Skip to main content

Quickly summarize / calculate data from multiple worksheets into one worksheet

Kutools for Excel

Boosts Excel With 300+
Powerful Features

It is easy for us to do some calculations such as Sum and Average in a worksheet, but if you want to sum or calculate data from multiple worksheets into one worksheet. How could you sum data from multiple worksheets into one worksheet quickly?
For example, I want to sum the quantity of the products from four worksheets into one worksheet. See following screenshots:

shot summarize data 001

Kutools for Excel’s Combine Worksheets utility can help you calculate data across multiple worksheets into one final worksheet quickly.

Combine and sum data from multiple worksheets into one worksheet

Combine and average multiple worksheets across different workbooks into one worksheet


Click Kutools Plus >> Combine. See screenshots:

shot summarize 1

Combine and sum data from multiple worksheets into one worksheet

If you want to combine and summarize multiple worksheets across different workbooks into one worksheet in Excel, you can get it done as follows:

1. Click Kutools Plus > Combine to enable the feature. Then click OK in the following Kutools for Excel dialog box.

2. In the Combine Worksheets -Step 1 of 3 window, select the Consolidate and calculate values across multiple workbooks into one worksheet option, and then click the Next button.
shot summarize 2

3. In the Step 2 wizard, please specify the workbooks and worksheets that you want to combine and summarize data into one worksheet.
shot summarize 3

Tips:

A. Add workbooks to merge

Click File(s) under Add button, you can add one workbook or multiple workbooks into Workbook list to merge.
If you click Folder… and specify a folder, it will automatically add all workbooks of the folder into Workbook list to merge.
If you click OneDrive File(s) or OneDrive Folder..., it will automatically add one or multiple OneDrive files from one folder or different folders as you need.
shot summarize 4

B. Sort all workbooks or worksheets in the Workbook or worksheet list

Specify a sorting condition in the Sort drop-down list to sort all workbooks or worksheets in the list box. For example, select Name in the Sort drop-down list, all workbooks or worksheets in the list box are sorted by name automatically.
shot summarize 13

C. Remove workbooks from the Workbook list box

If you want to delete a workbook from the Workbook list box, please select this workbook and click the button.

If you want to remove all workbooks from the Workbook list box, just click the Remove all button.
shot summarize 5

D. Open workbooks which has been encrypted with password

If you want to combine the workbooks which has been encrypted with passwords, please click the Password button.
shot summarize 6

In the Password Manager window, click the Add button, enter the workbook password into the Password textbox, fill in the label and click the OK button. Repeat the operation until all workbooks’ passwords are added to the manager, and then click the OK button to close the window. See screenshot:

E. Save or access one scenario of your operation:

If you want to save the settings of Step 2 for future operations, please click Scenario > Save… button, and then name the scenario in the next popping up dialog. By doing so, you don’t need to add workbooks or specify the worksheets again and again in the future, you just need to open the scenario item you have saved, then all the workbooks will be listed in the Workbook list.
Besides, you can click Scenario > Manage to get into the Manage Scenario window to manage the saved scenarios as you need.
shot summarize 7

F. What’s the Same range button? It will automatically specify each worksheet of all checked workbooks in the Workbook list to have the same range as the worksheet you selected. For example, you have specified range $A$1:$C$6 in Sheet1 of Workbook A as shown in the below screenshot, if you first click on Sheet1 and then click Same range button, you will see the ranges in other worksheets of Workbook A are all changed to $A$1:$C$6 immediately. At the same time, ranges in each worksheet of all checked workbooks in the Workbook list will also change to the same range. See screenshots:



G. What’s the Same sheet button? At the bottom of the Worksheet list, there is a Same sheet button. With this button, you can easily select all worksheets with same worksheet name across all the checked workbooks in the Workbook list. For example, there are 5 workbooks that have been checked in the Workbook list, if you click on the Sheet4 as shown in the below screenshot, it will immediately select all worksheets with same worksheet name across the 5 checked workbooks after clicking Same sheet button. See screenshots:

shot summarize 8

There are only 2 workbooks (Workbook E and Workbook C) has the same name worksheet (Sheet4), so you will have the results as follows:

shot summarize 9
shot summarize 10

4. After configuring in the Step 3, please click Next button to get into the Step 3 wizard. See screenshot:

shot summarize 11

Tips 2:

A. Function list: you can choose a function to calculate data from multiple worksheets in different workbooks. The function includes Sum, Count, Average, Max, Min, Product, Count Numbers, stdDev, stdDevp, Var and Varp.

B. Use labels in Top row or Left column?

Top row: it will calculate / consolidate date based on column titles.

Left column: it will calculate / consolidate date based on row titles.

For example, if you have data as shown in the following screenshots and you have checked Top row option and it will sum all data from worksheets, you will see the results as shown in the following screenshots.

4. Click Finish button in the Step 3  wizard, in the Specify the file name and location for the combined workbook window, select a folder to save the combined workbook, name it as you need, and then click the Save button. See screenshot:

Note: If you haven't saved the workbook and worksheet settings as scenario, a Kutools for Excel window will pop up to remind you to save the scenario. If you decide to save it, click the Yes button and name it in the next Save Scenario dialog. Otherwise, click the No button. See screenshot:

A new workbook is created and status of the selected workbooks are listed inside. You can click on the Output file link to open the combined workbook directly.

Then all worksheets or specified worksheets of workbooks are combined and summarized into one worksheet according to your settings. See screenshot:


Combine and average multiple worksheets across different workbooks into one worksheet

If you want to combine and average multiple worksheets across different workbooks into one worksheet, you can quickly get it done as follows:

1. Please apply this utility by clicking Kutools Plus > Combine > Consolidate and calculate values across multiple workbooks into one worksheet > Next.

2. In the Step 2 wizard, please specify the workbooks and worksheets that you want to combine and summarize data into one worksheet.

For more information about the settings of Step 2 and Step 3, please refer to Combine and sum data from multiple worksheets into one worksheet. The difference is to select Average function in the Step 3 as shown in the following screenshot.
shot summarize 12

3. After finish all settings, you will see the results as shown in the following screenshots.

You can also combine and apply the following functions (Sum, Count, Average, Max, Min, Product, Count Numbers, stdDev, stdDevp, Var and Varp) to multiple worksheets across different workbooks in Excel.

For more information about:
A: Quickly combine multiple worksheets / ranges from workbooks into one worksheet
B: Quickly merge / combine worksheets or workbooks into one workbook in Excel
C: Quickly merge / combine all worksheets of same name across workbooks into one worksheet

Note: If you think the Combine feature is useful, you can click shot share button button to share it to your friends.
shot share


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 30 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have tried to work through this page but I keep getting lost. I do not want to do any calculations [necessarily, at this point]. I want to "Combine .. data from multiple worksheets into one worksheet". I.E. I do a daily report broken down by time periods. BUT the first ROW is the headers and the SECOND ROW is the summary line for the day. Row 2, columns B thru H. Rows 3ff are time periods, sometimes an hour, sometimes 2 or 3 hours, etc. I put the summary in row 2 to make it consistent across sheets. All I want to do is to have the daily "summary line" be copied into the next available row in a "Summary sheet".. So the Summary sheet would be a list of days, with the column 1 the dates and the rows B thru H showing how many visits, calls, eConsults, etc I did that day. So I am not tracking the time periods, just the total day.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations