How to Consolidate Date from Multiple Workbooks in Excel

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010

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 totalled data

consolidate data ms excel
Path you can use to consolidate Excel data from different workbooks or files
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010