Ways to summarize data from multiple tables in an Excel table

ways to summarize data from multiple tables in an Excel table

1. Open the workbook you want to work with, in this workbook, the November purchase table and December purchase tables worksheet contains the data you want to merge, as shown in Figure 1. The totals worksheet is used to place the data after the consolidation, and its structure is shown in Figure 2.

Figure 1 Two worksheets that need to be merged

Figure 2 The structure of the "Totals" worksheet

2. In the totals worksheet, click the first cell in the range of cells where you want to place the merged results, and then click the Consolidate button in the Data Tools group on the Ribbon Data tab, as shown in Figure 3.

Figure 3 Click the Merge Calculation button

3, open the Merge Calculation dialog box, select the Sum option in the Function Drop-down list box to set how you want to calculate the merged data, and then click the Reference button to the right of the reference location text box, as shown in Figure 4. Open the November Purchase table worksheet, drag the mouse over the worksheet to select the reference cell, and then click the Reference button again, as shown in Figure 5.

Figure 4 Click the Reference button

Figure 5 Selecting a reference location