Lesson 35: How to merge worksheets to process data in different Excel files

You can use the Consolidate feature in Excel to merge your worksheets (located in one workbook or multiple workbooks) into one sheet.

How to merge sheets in Excel

As you can see in the 3 pictures below, the sheets are not the same. However, the good point of the feature Consolidate is that it can easily sum, count, average, etc. data using labels. This is a lot easier than creating a formula.

1. Open all three workbooks.

2. Open a blank workbook. On the Data tab, in the Data Tools group, select Consolidate.

3. Select the Sum function to sum the data.

4. Click Reference, select the area A1:E4 in District1 and click Add.

5. Repeat step 4 for District2 and District3.

6. Select Top row, Left column and Create links to source data.

Note: if you do not select Top row and Left column, Excel will sum all cells with the same position. For example, cell B2 (in district1.xlsx) + cell B2 (in district2.xlsx) + cell B2 (in district3.xlsx). Because my sheets are not the same, I want Excel to sum the cells with the same label. If you choose Create links to source data, Excel will create a link to your original data (your aggregated data will be updated if your original data changes).

7. Click OK.

Result.

In addition, you can also view many other excel articles here.