Summarize/Calculate Data From Multiple Worksheets in Excel!!
Tags: kutoolsexceltutorial, summarizecalculateda, kutools
Published: 2020-11-16 07:31:45

---
title: Summarize/Calculate Data From Multiple Worksheets in Excel!!
published: true
date: 2020-11-16 06:41:41 UTC
tags: KutoolsExcelTutorial,SummarizeCalculateDa,Kutools
canonical_url: https://geekexcel.com/summarize-calculate-data-from-multiple-worksheets-in-excel/
---

In this article, we are going to see how to **Summarize/Calculate Data From Multiple Worksheets into One Worksheet in Excel Office 365** using **Kutools.** Let’s step into this article!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

**Note:** [kutools for Excel](https://geekexcel.com/get-kutools-for-ms-excel-2019-2016-2013-2010-2007-office-365/) has more than 180 features which are used to complete the difficult task with several clicks. If you want to use Kutools, you need to install it from Excel’s official website.
## Combine and Sum Data from Multiple Worksheets:

If you want to combine and sum data from multiple worksheets, do as follows.

- Go to the **Kutools Plus Tab, select** the **Combine** option.

![Kutools Plus Tab](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/1-18.png)<figcaption id="caption-attachment-17260">Kutools Plus Tab</figcaption>

- It will display a **prompt box,** you need to **click** the **OK** button.

![Prompt box](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Prompt-box-2.png)<figcaption id="caption-attachment-17261">Prompt box</figcaption>

- Now, it will open the **Combine Worksheets – Step 1 of 3** dialog box.
- **Combine Operations –** In this section, you need to **select** the **Consolidate and calculate values across multiple workbooks into one worksheet** option.
- Then, **click** the **Next** button.

![Click the Next option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Click-the-Next-option.png)<figcaption id="caption-attachment-17262">Click the Next option</figcaption>

- It will open the **Step 2 wizard** , where you need to **select** the **workbooks** and **worksheets** that you want to combine and summarize data.

![Step 2 wizard](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Step-2-wizard.png)<figcaption id="caption-attachment-17263">Step 2 wizard</figcaption>

### Configure the settings as below:

### #1) **Add workbooks to merge**

- In the **Add drop-down** list, you can **click** the **Files(s)** button to add one workbook or multiple workbooks to merge.
- **Folder –** It will add all workbooks of the folder into the workbook list to merge.
- **OneDrive File(s) or OneDrive Folder –** This will add one or multiple OneDrive files from one folder or different folders as you need.

![Add option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Add-option.png)<figcaption id="caption-attachment-17264">Add option</figcaption>

### #2) Sort all workbooks or worksheets in the workbook

- **Sort drop-down list –** It is used to sort all workbooks or worksheets in the list box.

!["<yoastmark](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Sort-Drop-down-list.png)

### #3) Remove workbooks from the workbook list box

- **Workbook list –** You can delete a single workbook from the list by using the **X** button.

![Workbook list](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/2-18.png)<figcaption id="caption-attachment-17266">Workbook list</figcaption>

- **Remove all –** It will remove all the workbooks from the workbook list.

![Remove all button](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Remove-all-button.png)<figcaption id="caption-attachment-17267">Remove all button</figcaption>

### #4) Open workbooks which have been encrypted with the password

- **Password –** This option is used to combine the workbooks which have been encrypted with passwords.

!["<yoastmark](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Password-option.png)

- **Password Manager Dialog box – Click** the **Add** button, **enter** the workbook **password** into the Password text box. Then, **fill** the **Label** and **click** the **OK** button.
- **Repeat the above step** until the workbooks passwords are added to the manager, then **click** the **OK** button to close the window.

### #5) Save/Access one scenario of your operation

- You can save the settings of **Step 2** for future use. You need to **click** the **Save** option under the **Scenario** drop-down list.
- Then, you need to give the **name** for the scenario in the popping-out dialog box.

![Scenario option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/3-17.png)<figcaption id="caption-attachment-17269">Scenario option</figcaption>

### #6) Same range option

- Using this option, you can select the specified range in one worksheet and select the same range from all other sheets in the workbook automatically.

![Same range option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/4-14.png)<figcaption id="caption-attachment-17270">Same range option</figcaption>

### #7) Same Sheet option

- With this option, you can select the specific sheet across multiple workbooks that contain a specific sheet name.

![Same Sheet option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/5-11.png)<figcaption id="caption-attachment-17271">Same Sheet option</figcaption>

- After configuring **Step 2** , kindly **click** the **Next** button.

![Click the Next option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/6-11.png)<figcaption id="caption-attachment-17272">Click the Next option</figcaption>

- It will go to the **Step 3** wizard.
- In this dialog box, do as below steps.
- **Function list –** You need to select a function to calculate data from multiple worksheets in different workbooks. The functions are Sum, Count, Average, Max, Min, Product, Count Numbers, stdDev, stdDevp, Var, and Varp.
- **Use labels in –** You can select the **Top row** option to calculate the date based on column titles or select the **Left column** option to calculate date based on row titles.
- After that, **click** the **Finish** option.

![Step 3 wizard](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Step-3-wizard.png)<figcaption id="caption-attachment-17273">Step 3 wizard</figcaption>

- Now, it will open the **Specify the file name and location for the combined workbook dialog box** , where you need to **select a folder to save** the combined workbook.

![Click Save button](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Click-Save-button.png)<figcaption id="caption-attachment-17274">Click Save button</figcaption>

- **Fine name –** You need to type the name of the file as you need.
- Finally, **click** the **Save** button.
- At last, a new workbook is created and the status of the selected workbooks is listed as shown in the below image.
- **Output file link –** You can click this link to view the combined workbook directly.

![Result](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/7-9.png)<figcaption id="caption-attachment-17275">Result</figcaption>

- Then, all worksheets of workbooks are combined and summarized into one worksheet according to your settings.

![Output](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/8-9.png)<figcaption id="caption-attachment-17276">Combined Result</figcaption>

## Combine & Average Multiple Worksheets across Different Workbooks:

To summarize or calculate average data in multiple worksheets across different workbooks, follow the below steps.

- On the **Kutools Plus Tab, select** the **Combine** option.

![Kutools Plus Tab](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/1-18.png)<figcaption id="caption-attachment-17260">Kutools Plus Tab</figcaption>

- Now, it will open the **Combine Worksheets – Step 1 of 3** dialog box.
- **Combine Operations –** In this section, you need to **select** the **Consolidate and calculate values across multiple workbooks into one worksheet** option and **click** the **Next** button.

![Click the Next option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/Click-the-Next-option.png)<figcaption id="caption-attachment-17262">Click the Next option</figcaption>

- **Step 2 wizard –** You need to specify the workbooks and worksheets that you want to combine and summarize data into one worksheet.

![Step 2 wizard](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/9-7.png)<figcaption id="caption-attachment-17277">Step 2 wizard</figcaption>

- To know more about **Step 2** and **Step 3** , refer to the above section.
- **Step 3 wizard –** You need to **select** the **Average** function.

![Step 3 wizard](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/10-7.png)<figcaption id="caption-attachment-17278">Step 3 wizard</figcaption>

- After completing all the configurations, you will see the result as shown in the following image.

![Result](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/11/dddd.png)<figcaption id="caption-attachment-17279">Result</figcaption>

## A Brief Summary:

In this article, you can clearly understand the steps to **Summarize/Calculate Data From Multiple Worksheets into One Worksheet in Excel Office 365** using **Kutools.** Drop your **queries/feedback** in the comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!

### Keep Reading:

- [How to Copy Multiple Selections in Excel Office 365?](https://geekexcel.com/how-to-copy-multiple-selections-in-excel-office-365/)
- [How to insert a Monthly or a Yearly Calendar in Excel Office 365?](https://geekexcel.com/how-to-insert-a-monthly-or-a-yearly-calendar-in-excel-office-365/)