Donna has attended:
Excel Advanced course
Consolidating data from many spreadsheets
Hi,
I have multiple spread sheets (~30 at the moment but this will increase with every new project) with matching data fields that I want to analyse.
What would be the best way to consolidate this data?
My thoughts were to copy the required data onto separate tabs in one workbook and create a pivot table on the front page. However, this will be time consuming initially.
Any better ways I can do this?
Thanks,
Donna
RE: Consolidating data from many spreadsheets
Hello Donna,
Thank you for your question. An easy tool to use is Consolidate, which is found in the Data tab. With Consolidate, you can add as many ranges from different sheets as you like. Your ranges need to have similar labels but the labels don't have to be in the same order. Also, the ranges don't need to have the same number of records.
You would go to a new sheet, then simply add the ranges to the Consolidate tool one by one by selecting a range then clicking the Add button. Once you've added all the range references, make sure you tick the boxes at the bottom of the Consolidate tool, including Create Links to Source Data. Now click OK. You should have a table which reflects the consolidated totals from all the sheets.
Afterwards, you can simply add new ranges to create a new consolidation. This table can be used to create a Pivot Table.
I hope this helps.
Kind regards
Marius Barnard
Best STL
