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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

I have two datasets, Mid-Term Result-2021 & Annual Result-2021. I will use these two datasets to show you how to consolidate information 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


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


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.


Download Practice Workbook

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


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.


<< Go Back To Consolidation in Excel | Merge Sheets in Excel | Merge in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo