How to Consolidate Data from Multiple Workbooks in Excel

Earlier we have learnt how to consolidate data from multiple worksheets in the same workbook. One method uses the window split, copy and autfill. The other process uses the standard consolidate feature with the named ranges. Now if you wish to consolidate data from sheets in multiple workbooks or files you first need to open all the files in one instance of Excel i. e.

You start the MS-Excel program
Open all the files with the relevant data
Create a new Excel file
Click on the data tab
Select consolidate from the ‘data tools’ group
In the new consolidate window that pops up you select the function according to which you wish to consolidate the data, for example, ‘sum’
Now you select the cell or cell-range from the relevant file that you wish to consolidate
Click on add
Next you select the other ranges from the relevant files and also add them one-by-one to the ‘all references’ area
You can also use the option ‘create links to the source data’ in case you wish to update your consolidated data if the data in the individual ranges changes
Finally click on OK

Watch the Excel training video below to see how we made the above process a bit simpler by also using named ranges and totaled data:

Path you can use to consolidate Excel data from different workbooks or files

Path you can use to consolidate Excel data from different workbooks or files

Leave a Reply

Your email address will not be published. Required fields are marked *