How to Consolidate Data from Multiple Ranges in Excel (2 Easy Ways)

Data consolidation means merging data with one another. To make a final report, we need to consolidate data from multiple ranges in Excel. Fortunately, Excel has some built-in features that be used to consolidate data. In this article, I will show you 2 ways to consolidate data from multiple ranges in Excel. So, without having any further discussion, let’s get started.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


2 Ways to Consolidate Data from Multiple Ranges in Excel

I have two datasets, Mid Term Result-2021 & Annual Result-2021. I will use these two datasets to show you how to consolidate data from multiple ranges in Excel.


1. Using Consolidate Feature to Consolidate Data from Multiple Ranges

First, I will use the Consolidate command to show you how to consolidate data from multiple ranges in Excel. The Consolidate command is a built-in command in the main ribbon. It is used to merge data with one another.

Now follow the steps below to consolidate data.

  • First, select a cell. I have selected cell G6 as a destination cell address.
  • After that, go to Data Data Tools Consolidate.

Using Consolidate Feature to Consolidate Data from Multiple Ranges in Excel

The Consolidate dialog box will open.

  • Select Sum from the Function drop-down menu.
  • Then select the first range in the Reference I have selected the range of the Mid Term Result-2021 dataset.
  • Then click on Add.

Using Consolidate Feature to Consolidate Data from Multiple Ranges in Excel

The first range will appear in the All reference box.

  • Now click on the upper-arrow button beside the Browse.

Using Consolidate Feature to Consolidate Data from Multiple Ranges in Excel

  • Insert the second range in the Reference I have inserted the range of the Annual Result-2021 dataset.
  • Click on the Add button again.

Using Consolidate Feature to Consolidate Data from Multiple Ranges in Excel

The second range will also appear in the All references box.

  • After that, select the Top row and Left column beneath the ‘Use labels in’ These will add the data with the correct labels (assuming that the labels are at the first row and column in the table).
  • Then click OK.

The two ranges will be consolidated at your desired destination. In the consolidated results, you will see the marks have been summed up from the two ranges.

Using Consolidate Feature to Consolidate Data from Multiple Ranges in Excel

Read More: How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)


Similar Readings


2. Consolidate Data from Multiple Ranges Using Pivot Table

Another way to consolidate data from multiple ranges is to use the Pivot Table. The Pivot Table is a handy tool in Excel. It is used to organize data in a meaningful manner.

However, follow the steps below to consolidate data using the Pivot Table.

  • Select a cell first.
  • Then press the following keys alternatively: ALT + D + P.

Consolidate Data from Multiple Ranges Using Pivot Table in Excel

The PivotTable and PivotChart Wizard dialog box will open.

  • Select Multiple Consolidation Ranges.
  • Then select PivotTable.
  • After that, click on Next.

Consolidate Data from Multiple Ranges Using Pivot Table in Excel

  • Select ‘Create a single page field for me’.
  • Then click on the Next button again.

  • Insert the first range in the Range I have inserted the range of the Mid Term Result-2021 dataset.
  • Then click on Add.

Consolidate Data from Multiple Ranges Using Pivot Table in Excel

  • Now select the second range in the Range I have inserted the range of the Annual Result-2021 dataset.
  • Click on Add.
  • After that, click on Next.

  • Finally, it’s time to select the destination of the Pivot Table.

You can select either the New worksheet option or the Existing worksheet option.

  • I selected the Existing worksheet Then I inserted a destination cell address.
  • After that, click on Finish.

Consolidate Data from Multiple Ranges Using Pivot Table in Excel

  • Select the Row and Value column in the PivotTable Fields dialog box.
  • Then click on the drop-down menu beside the Count of Value
  • Select Value Field Settings.

Consolidate Data from Multiple Ranges Using Pivot Table in Excel

The Value Field Settings dialog box appears.

  • Select Sum in the ‘Summarize value field by’ Here, the data suits the Sum operation, that’s why selected that, depending on the circumstances and data type select the operation.
  • Then click OK.

After that, you will see the consolidation result. In the Row Labels column, you will see the student names. In the Sum of Value, you will see the consolidated marks of each student.

Consolidate Data from Multiple Ranges Using Pivot Table in Excel

Read More: How to Consolidate Multiple Worksheets into One PivotTable (2 Methods)


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.


Conclusion

To summarize, we have discussed 2 ways to consolidate data from multiple ranges in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo