Consolidate Data in Excel

Consolidation in Excel is a powerful way to do some simple mathematical operations from two or more worksheets from either a workbook or separate workbooks. To show how to consolidate data in Excel, let’s calculate the sum from three individual Excel files by following the steps below.

1. First of all, we need to have three Excel files/workbooks (e.g., Expentirure-1, Expenditure-2, and Expenditure-3). Notice that worksheets have different column names.

2. Make sure that all workbooks are open at the same time.

3. Open another blank workbook and click “Consolidate” on the Data tab.

4. Select the Sum function from the dialog box.

5. Click the reference box, select the range B2:F6 in the Expenditure-1 workbook and then click Add.

6. Now repeat step 5 twice for Expenditure-2 and Expenditure-3 workbooks.

7. Make sure the Top row, Left column, and Create links to source data are checked.

8. Click OK.

Result:

6 of 9 finished! Recommending more on Worksheet: Next Example >>

<< Previous Example | Skip to Next Chapter 05: Formatting Cells