How to summarize data from worksheets/workbooks into one worksheet?

Imagine you have a workbook with multiple worksheets, all formatted identically. Now, you need to consolidate these worksheets and compute the final results into a master worksheet. How do you effectively summarize data from various worksheets or even different workbooks into one central location? This guide will introduce several efficient techniques to help you achieve this seamlessly.

Summarize data from multiple worksheets/workbooks into one worksheet with the Consolidate function

Summarize data from multiple worksheets/workbooks into one worksheet with Kutools for Excel

Summarize data from multiple worksheets/workbooks into one worksheet with the Consolidate function

This Consolidate feature combines values and does some calculations from multiple worksheets or workbooks into one worksheet. In the following example, I want to summarize the total score of four terms for each student. See screenshots:

The score of the first term:

The score of the second term:

The score of the third term:

The score of the fourth term:

Now I can use the Consolidate function to summarize the data from multiple worksheets into a single worksheet, please do as follows:

1. Create a new workbook in which you want to put the consolidated data, then click Data > Consolidate, see screenshot:

2. In the Consolidate dialog box, select Sum (as we want to show the sum of values for consolidation ) from the Function option, then click the   button from Reference to select the range that you want to summarize, and then click the Add button to add the range reference to the All references list box. See screenshot:

3. Use the same way to add other ranges to the All references list box. If your data contains labels for the top row and left column, please check the Top row and Left column under the Use labels in section. You can also enable the links to source data option, for linking the source data(this option is applied to the new workbook ), see screenshot:

4. Click OK to finish this operation. All the data from the worksheets has been added to a new worksheet. At the same time, it creates links for the consolidated data, which updates automatically when the source data changes.

Summarize data from multiple worksheets/workbooks into one worksheet with Kutools for Excel

If you are not familiar with the Consolidate function in Excel, here I will recommend an easy and multi-functional tool-Kutools for Excel. With its Combine feature, you can quickly summarize the data from multiple worksheets or workbooks into one master worksheet.

After installing Kutools for Excel, please click Kutools Plus > Combine.

1. In the Combine Worksheets wizard, select Consolidate and calculate values across multiple workbooks into one worksheet. See screenshot:

2. On step 2 of the wizard, all the opened workbooks and worksheets are listed in the list boxes, you can click the Add button to add other files or folders that you want to consolidate. Click the  button to select the range you want to summarize one by one. (By default, the used range is selected for each worksheet) See screenshot:

3. After selecting the ranges, then continue to click the Next button, on step3 of the wizard, and set the following settings:

• (1.) Select the calculation type from the Function, in this example, I choose Sum.
• (2.) Check the Top row, Left column if your source data contains the corresponding labels.
• (3.) Check the Create links to source data option if you want the consolidation to be updated automatically when the source data are changed. See screenshot:

4. And then click the Finish button, and you will be asked if you want to save this scenario. If you want to save this scenario, please click Yes, and enter the name of the scenario, (see the following screenshots), if you don’t want to save this scenario, please click No. When you want to use this scenario, you just only need to click the Scenario button to choose the name of the scenario you need on the step2 wizard.

And then all the data from the worksheets has been summarized into one worksheet of a new workbook. And the consolidated data is added to the links as well. See screenshot:

Notes:

1. With the above two methods, you can also do other calculations, such as Average, Count, Max, Min, and so on.

2. Both the two features also can be applied to multiple workbooks for consolidation.

If you want to know more about this feature, please click Consolidate and calculate values across multiple workbooks into one worksheet.

Related articles:

How to combine multiple workbooks to one workbook in Excel?

How to merge worksheets / workbooks into one worksheet?

How to combine worksheets of same name into one worksheet?