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.

Table of Contents hide

Download Practice Workbook

2 Ways to Consolidate Data from Multiple Ranges in Excel

1. Using Consolidate Feature to Consolidate Data from Multiple Ranges

2. Consolidate Data from Multiple Ranges Using Pivot Table

Practice Section

Conclusion

Download Practice Workbook

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

Consolidate Data from Multiple Ranges.xlsx

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.

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.

The first range will appear in the All reference box.

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

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

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.

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.

The PivotTable and PivotChart Wizard dialog box will open.

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

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.

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.

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.

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.

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.