Mrinmoy Roy
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.
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.
Read More: How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)
Similar Readings
- How to Consolidate Two Sheets into One in Excel (3 Useful Methods)
- How to Automate Consolidation in Excel (with Easy Steps)
- Consolidate Rows and Sum Data in Excel (4 Effective Methods)
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.
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
- Consolidate Two Sheets into One in Excel (3 Useful Methods)
- How to Create a Linked Consolidation in Excel (2 Useful Methods)
- Build a Static Consolidation in Excel (2 Suitable Examples)
- How to Use Grouping and Consolidation Tools in Excel (5 Easy Examples)
- Consolidate Function for Text Data in Excel (with 3 Examples)
- How to Remove Consolidation in Excel (2 Handy Methods)
- [Fixed]: Consolidation Reference Is Not Valid in Excel (with Quick Fix)
