How to consolidate Excel files

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).

Excel Document StackNot exactly what you need?
Have a look at The Ultimate Guide on how to Merge Excel files for some other ways to merge Excel files.

Consolidate in Excel

  1. 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
  2. Open all the Excel files which you would like to consolidate data from
  3. In the master spreadsheet select the first cell which will contain data:
    Consolidate Excel Master Data
  4. In the Data tab of the Excel Ribbon, click the Consolidate button:
    Excel Data Consolidate
  5. The Consolidate dialog will be shown:
    Excel Consolidate Dialog
  6. Click on the reference selection button to select the first set of data to use in the consolidation:
    Excel Consolidate Reference Button
  7. Select the first Excel file or worksheet which contains data you would like to include in the consolidation
  8. 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):Consolidate Excel Bobs Data Reference
  9. The reference selection dialog should then show the file and cells selected:
    Excel Consolidate Reference Dialog With Ref
  10. Then click the button to end reference selection:
    Excel Consolidate Reference Dialog End Reference
  11. Back in the Consolidate dialog, click on the ‘Add’ button to add this reference to the list of data to be consolidated (All references):
    Excel Consolidate Dialog Reference Added
  12. 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):
    Excel Consolidate Dialog Reference 2 Added
  13. 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:Consolidate Excel Master Populated

If you’d like to try Excel consolidation out, you can download some sample data here: SampleDataForExcelConsolidate.zip