Quickly summarize / calculate data from multiple worksheets into one worksheet
Kutools for Excel
Boosts Excel With 300+
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:
Kutools for Excel’s Combine Worksheets utility can help you calculate data across multiple worksheets into one final worksheet quickly.
Click Kutools Plus >> Combine. See screenshots:
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.
3. In the Step 2 wizard, please specify the workbooks and worksheets that you want to combine and summarize data into one worksheet.
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.
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.
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.
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.
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.
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:
There are only 2 workbooks (Workbook E and Workbook C) has the same name worksheet (Sheet4), so you will have the results as follows:
4. After configuring in the Step 3, please click Next button to get into the Step 3 wizard. See screenshot:
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:
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.
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 button to share it to your friends.
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.
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.