If you’ve got multiple spreadsheets representing numerical data with the same column and row headings, you may want to consolidate the data into one worksheet.
As an example, consider a set of spreadsheets representing product sales for different sales reps. You may want to get the total sales for all products in each quarter or the averages for all reps per product in each quarter.
Consolidate in Excel is the easiest way to do this and doesn’t require any macros or VBA code; it’s built into Excel itself.
If this is what you need, to consolidate Excel files, you can follow the steps in the Consolidate in Excel section below (you can download the sample files here).
Consolidate in Excel
- Create a new ‘master’ spreadsheet which will contain the final consolidated data. It’s probably easiest to copy one of the existing spreadsheets and delete then data values then ‘Save as…’ to get this master spreadsheet
- Open all the Excel files which you would like to consolidate data from
- In the master spreadsheet select the first cell which will contain data:
- In the Data tab of the Excel Ribbon, click the Consolidate button:
- The Consolidate dialog will be shown:
- Click on the reference selection button to select the first set of data to use in the consolidation:
- Select the first Excel file or worksheet which contains data you would like to include in the consolidation
- Select the data to consolidate (e.g. select the first cell containing the data then hold the Ctrl button down and click the down arrow then the right arrow):
- The reference selection dialog should then show the file and cells selected:
- Then click the button to end reference selection:
- Back in the Consolidate dialog, click on the ‘Add’ button to add this reference to the list of data to be consolidated (All references):
- Repeat steps 6 to 12 for each file/worksheet containing data to be consolidated (the following example includes 2 sets of references in 2 separate files):
- Once all the Excel files/worksheets to be consolidated have been referenced, click the ‘OK’ button and the master spreadsheet will contain the consolidated data:
If you’d like to try Excel consolidation out, you can download some sample data here: SampleDataForExcelConsolidate.zip