The success of any business is backed by a lot of data collection, research, and analysis. Today, every business operation produces a lot of data on the go, and in different forms. In such a scenario, picturing a unified view of the data accumulated from multiple sources can be difficult.
For example, let’s say you are doing online business through multiple sales points, i.e. online marketplaces and your own e-commerce store. In such cases, you need to accumulate sales data generated by all these sales points to evaluate your business performance. Manually putting data from one Excel sheet to another and using the formulas to combine these datasets can be the worst option here.
The solution is to consolidate data in Excel. In this article, we cover everything about data consolidation in Excel, along with practical examples.
Consolidating data in Excel is a great way to combine information from multiple sources into one master worksheet. The consolidate feature allows you to quickly aggregate data from multiple sheets or workbooks without having to spend time manually copying and pasting.
In this comprehensive guide I’ll walk you through step-by-step how to use consolidate in Excel to merge data from multiple sources. Whether you need to consolidate data from multiple sheets in one workbook or across multiple workbooks, this guide has you covered.
What is Consolidate in Excel?
The consolidate feature in Excel allows you to combine data from multiple worksheets or workbooks into one summary worksheet. It works by matching common rows and columns in each source and aggregating the data using a calculation like sum, count average etc.
Some key things to know about consolidate in Excel:
- Works across sheets in same workbook or multiple workbooks
- Matches data based on row/column labels
- Common calculations are sum, count, average but others can be used
- Creates links to source data so consolidated data updates when source changes
When to Use Consolidate in Excel
Consolidating data is useful any time you need to combine information located across multiple sheets or workbooks into one centralized worksheet.
Here are some common examples of when you’d want to use consolidate:
- Combining monthly sales data from each store location into one summary worksheet
- Aggregating quarterly financial data from each department into one company roll-up
- Merging survey data collected on separate worksheets by each team member into one sheet
- Bringing together pricing lists for each product line into one master price list
The key is you have data in multiple places that needs to be merged into one centralized worksheet for reporting and analysis. Consolidate lets you do this quickly and easily.
Step-by-Step Guide to Consolidating Data in Excel
Follow these steps to consolidate data in Excel:
1. Open the Workbooks You Want to Consolidate
To start open all the workbooks containing the data you want to consolidate. This can be multiple sheets within the same workbook or entirely separate workbooks.
Make sure the data is formatted consistently in each source worksheet. The row and column labels should match across each source since consolidate uses those labels to match the data.
2. Create a New Summary Worksheet
Open a new blank workbook. This is where the consolidated data will be placed.
You can also insert a new blank worksheet into an existing workbook if you prefer to consolidate everything into the same file.
3. Click the Consolidate Button
Go to the Data tab and click the Consolidate button in the Data Tools group (in Excel 2010 or earlier it is under the Data tab).
![Image showing Consolidate button][]
This opens the Consolidate dialog box.
4. Select the Function
In the Consolidate dialog box, use the Function drop-down to select the calculation you want to perform on the data such as Sum, Count, Average, etc.
Sum is the most common to total the values across the sources.![Image showing Select Function][]
5. Select the First Data Range to Consolidate
In the Reference box, click the worksheet containing the first data range you want to consolidate. Then highlight the cell range.
The reference will be added below. Click Add to add it to the list.
Repeat this step for each additional worksheet you want to consolidate.![Image showing Add References][]
6. Identify Row and Column Labels
Check the boxes for Top row and/or Left column to identify rows/columns containing labels.
Consolidate will match data based on labels you select not cell positions.
7. Link to Source Data (Optional)
Check the box for “Create links to source data” if you want the consolidated data to update when the source data changes.
Formulas will be created linking the consolidated data to the original sources.
8. Click OK to Consolidate
Once all the sources and options are selected, click OK to perform the consolidation.
The aggregated data will be placed into the target worksheet you specified in step 2. Any links to the original sources will also be created if you selected that option.
And that’s it! By following these 8 simple steps you can easily consolidate data from multiple Excel sources using the consolidate feature.
Tips for Consolidating Excel Data Successfully
Here are some useful tips to help ensure your Excel data consolidation goes smoothly:
- Consistency is key – The source data should have the same layout and labels for consolidate to work correctly. Take time to reformat sources to match before consolidating.
- Put consolidated data in its own worksheet – Don’t attempt to consolidate back into one of the original source worksheets. Use a separate sheet to avoid data overlaps.
- Check for calculation differences – If consolidating values from sources with different calcuations, consolidate may not yield the expected results. Use sum/count instead of averages for example.
- Save consolidated file separately – After consolidating into a summary worksheet, save it as a separate Excel file for safe keeping and distribution.
- Refresh consolidated data – If you linked consolidation sources, remember to refresh if any source data changes to update the consolidated values.
Consolidating Data from Multiple Excel Files
The examples above consolidated data from multiple worksheets within the same workbook. But you can also use consolidate to aggregate data across completely separate Excel files:
- Open all workbooks – Open each file containing source data you want to consolidate. Keep them all open.
- Use “Browse” to select files – In the consolidate dialog, use the Browse button to navigate to each file to select data ranges rather than selecting sheets.
- Follow remaining steps – The process is the same as consolidating from sheets in one workbook. Just select file references instead of sheet references and proceed as normal.
Consolidating across workbooks works the same way. The key is having all files open so you can easily navigate to each one to select the data range to add.
When Not to Use Consolidate in Excel
While the consolidate feature is incredibly useful for aggregating data, it’s not the right tool for every job.
Here are a few cases when you may want to use another method instead of consolidate:
- Data has no common rows/columns for labels
- Sources are formatted completely differently
- You need to further analyze consolidated data
- You want to consolidate within a pivot table
For these situations, it may be better to use copy/paste to manually merge data, use VLOOKUP/HLOOKUP to merge, or create a pivot table for analysis.
Consolidate Can Save You Time Combining Data
As you can see, the consolidate feature in Excel provides a simple way to quickly combine data from multiple sources into one centralized location for reporting and analysis.
While you could manually copy and paste from each source sheet, consolidate automates this process and ensures your data is aggregated correctly.
Next time you need to combine data located across multiple sheets or workbooks, give the consolidate feature a try to save yourself time and effort. Just remember to make sure your sources are consistently formatted for optimal results.
Example #2 – How to consolidate data in Excel from multiple ranges
You can also use consolidate in Excel to combine data from multiple ranges.
For example, let’s say you have order sales data for January & February months and want to combine both of them to find the total quantity of products sold in both months by SKU.
In this case, you need to select multiple ranges and use both the ‘top row’ and ‘left column’ as labels. Let’s see how to consolidate data in Excel from multiple ranges.
- Select the cell where you want to consolidate data from multiple ranges.
- Go to Data > Data Tools > Consolidate, and select the appropriate function (which is ‘sum’ here).
- Now, select and add all the reference ranges (remember to select ranges including the header rows), tick both ‘Top row’ & ‘Left column’ to use their labels, and click on OK to continue.
- The data will be consolidated into the target cell.
Why do you need to consolidate data in Excel?
Different systems may produce different datasets in multiple formats. Bigger organizations may end up with hundreds (if not thousands) of CSV files and worksheets containing data. Combining these datasets into a single one, in a particular format, is essential for an overall analysis.
Data consolidation in Excel is a helpful functionality to combine similar datasets from multiple rows, columns, worksheets, or workbooks.
How to use the consolidate function in Excel
How do I use Excel consolidate?
For the Excel Consolidate feature to work correctly, make sure that: Each range (data set) you want to consolidate resides on a separate worksheet. Don’t put any data on the sheet where you plan to output the consolidated data. Each sheet has the same layout, and each column has a header and contains similar data.
What is consolidated function in Excel?
(With Steps) Consolidate function in Excel consolidates the data from multiple worksheets and provides desirable results. It is an inbuilt function in Excel, and we can insert a consolidated table by clicking on Data → Consolidate under the Data Tools group.
When should I consolidate data by position?
When the data in the source sheets or workbooks have the same order and utilize the same labels, consolidate the data by position. When the data in the source sheets or workbooks have a different order but utilize the same labels, consolidate the data by category.
