Data Consolidation

Data consolidation provides an easy way to combine data from multiple worksheets in a single worksheet. You can consolidate data from different worksheets in the same workbook or from different workbooks, or even a combination of both. The process allows you to select the ranges you want to add to the consolidation from different sources and Excel will aggregate the data in another workbook.

To consolidate data, all the ranges to be included in the consolidation must be of the same shape and size. For example, let’s say we have sales data from 2017 to 2019 that we want to consolidate from three worksheets into one worksheet named Sales for 2017 – 2019 .

The three workbooks we will be consolidating the data from are:

• 2017Sales.xlsx
• 2018Sales.xlsx
• 2019Sales.xlsx

To consolidate cell ranges from the three workbooks:

1. First, you need to open the destination workbook, that is the workbook into which you want to consolidate your data. In our example it will be 2017_2019Sales.xlsx.
2. Open the source workbooks, that is the workbooks supplying the data you want to consolidate. For our example, the source workbooks are the three workbooks listed above.
3. Switch to the workbook into which you want to consolidate your data. Click on the Data tab and click the Consolidate button in the Data Tools group.
   The Consolidate dialog box will be displayed.
   
4. On the Reference field, click the Collapse Dialog button (at the right edge of the field) to collapse the dialog box. When you click this button, it will minimise the Consolidate dialog box.
5. Now you need to select the range from the first worksheet. On the View tab, in the Window group, click Switch Windows . This will display a list of all open workbooks.
   
6. Click the first workbook that contains data you want to include in your consolidation. This will make the workbook the active window.
   
7. Select the cells you want to consolidate, then click the Expand Dialog button to return the Consolidate dialog box to its full size.
8. Click Add to add the selected range to the All references list box.
   
9. Repeat the steps 4 to 8 above to add additional ranges to the consolidation. These ranges can come from different workbooks or from different worksheets in the same workbook. For our example, these would be from 2018Sales.xlsx and 2019Sales.xlsx .
   
10. The default function used to aggregate the data in the consolidated workbook is Sum . You can change this to another function like Count or Average, for example, by clicking the Function drop-down list and selecting a different function.
11. Click OK when you’ve added all the ranges to be consolidated.

Each cell in the consolidated data will now hold the sum for that cell from all the other worksheets.