Log in
x
or
x
x
Register
x

or
0
0
0
s2sdefault

Quickly summarize / calculate data from multiple worksheets into one worksheet

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 Enterprise >> Combine. See screenshots:

shot combine same name sheets 1
summarize sheets 2

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. Please apply this utility by clicking Enterprise > Combine > Consolidate and calculate values across multiple workbooks into one worksheet > Click Next.

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

shot summarize data 03

Tips:

A. Add workbooks to merge

Click File… 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.

B. Save or access one scenario of your operation:

If you want to save the settings of Step 2 in Combine Worksheets for future operations, you can save a screnario of the settings by clicking Scenario > Save… button. 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.

C. If you want to delete a workbook from the Workbook list, please click on the workbook and then click Remove button, it will remove the workbook from the list immediately.

D. What’s Same range button? It will automatically specify each worksheet of all checked workbooks in the Workbook list of having the same range as the worksheet you selected. For example, The Sheet1 of Workbook A has a range $A$1:$C$6 as shown in the below screenshot, if you first click on Sheet1 and then click Same range button, you will see the Sheet2 of Workbook A will has the same range as the Sheet1, and each worksheet of checked workbooks in the Workbook list will also have the same range. See screenshots:



E. What’s the Same sheet button? At the button 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:

There are only 2 workbooks (Book5 and Book3) contains the same name worksheet (Sheet4), so you will have the results as follows:

3. After configuring in the Step 2, please click Next button. You will go into the Step 3. See screenshot:

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 to start combining and summarize data, then all worksheets of workbooks will be 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 Enterprise > Combine > Consolidate and calculate values across multiple workbooks into one worksheet > Click Next.

2. In the Step 2, 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 data 014

3. After clicking Finish button. 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


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 200 Advanced Functions for Excel 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

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

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

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.