Excel - Consolidate

If you have several data sources but the data structure is more or less same. Then you can use "Consolidate" tool to merge multiple data sources into one.

Now imagine you own 3 stores, which sells more or less same items, and you have the quarterly sales data for the last year.

Data of Store 1:

Data of Store 2:

Data of Store 3:

Notice that the column headers are same, however, the row headers are not same across the 3 stores.

1. Open all the 3 workbooks containing the sales data of 3 stores.

2. Open a new workbook. Then Click Data > Consolidate
Note: If data of the 3 stores are in separate worksheets of the same workbook, then you can just work in this Workbook. No need to create a new workbook.

3. Select "Sum" since we want to add up the sales amount

4. In the "Reference" field, go to the workbook and select the data range, then click "Add". Do it for store 1, 2 and 3. Check "Top row" and "Left column". This makes sure that data of all the 3 stores are matched by the row and column headers. If they are not checked, the data will be matched by position only, which will cause error in this example, since the column headers are not identical across all 3 stores.

Note: Check "Create links to source data" will create links to the data source so that when the source data changes, the consolidated data will also update. It also creates an outline.

4. The consolidated data is shown below.

5. If you click the "1" or "2", or "+" or "-", the data table will expand or collapse.