How to Create Summary Table from Multiple Worksheets in Excel

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to create a summary table in Excel from multiple worksheets. When you have a similar type of data in multiple worksheets, it’s convenient for you to create a summary of it to analyze the whole data of those worksheets. PivotTable, Power Query Editor, etc. are vital tools to make an effective analysis on summarizing the whole dataset. In this article, you will see how to use different features of Excel to create a summary table from multiple worksheets.


How to Create Summary Table from Multiple Worksheets in Excel: 3 Useful Ways

We have sales data for a few products and the vendors that sold them in the months of March, April, May, and June in the dataset.

create summary table in excel from multiple worksheets

Utilizing the data as a table is practical. To convert this data to a table,

  • After choosing the dataset, move to Insert >> Table.
  • Next, You’ll see a dialog box Make sure ‘My table has headers’ is checked, then click OK.

Your data is then transformed into a table. The following sections will make use of our data as tables.


1. Using Power Query Editor and PivotTable to Create Summary Table from Multiple Worksheets

The most effective way to create a summary table in Excel from multiple worksheets is to use the Power Query Editor and PivotTable. Let’s go through the procedure below for a detailed description.

Steps:

  • We will be using the following sheets to create the summary table from multiple worksheets.

Using Power Query Editor and PivotTable to Create Summary Table from Multiple Worksheets

  • Now, go to Data >> Get Data >> From Other Sources >> Blank Query.

  • After that, the Power Query Editor will open up.
  • Next, give your Query a name. In my case, the name of my query is Overall_Report and I pressed the ENTER button after that.
  • Later, in the Power Query formula bar, enter the following formula.

=Excel.CurrentWorkbook()

All of the workbook’s tables will be returned by the formula.

  • Next, as we don’t need duplicates, we removed the second column using the Remove Other Columns command from the context menu which we can get by right-clicking on the header of the first column.

create summary table in excel from multiple worksheets method 1

  • Later, we select Home >> Keep Rows >> Keep Top Rows. We don’t need all the tables in this workbook.

  • Thereafter, the Keep Top Rows window will appear. We chose four since we will be working with the first four tables in this workbook.

  • This operation will return the first four rows only. After that, click on the marked icon of the following image, uncheck Use original column name as prefix and click OK.

  • The data from all of your sheets will then combine together, as you will see next. This will help you to create the summary table.

  • The query might include some extraneous formatting, such as the date and time When the query opens, you will automatically see that there is a time following every date. To remove this, we clicked right on the mouse while selecting the Date column and then chose Change Type >> Date.

create summary table in excel from multiple worksheets method 1

  • By the way, use the next command if your data contains any blanks. Select the columns that contain blanks and then right-click >> Fill. Select Down or Up according to your convenience.

  • After that, press CTRL+A and then go to Transform >> Detect Data Type. Although it’s not always essential, using this command is a good habit to get into when using the Power Query Editor.

  • Thereafter, select Home >> Close & Load To…

  • Next, the Import Data dialog box will appear. Select PivotTable Report, the sheet where you want the Pivot Table to be opened, and click OK.

  • Your Pivot Table will be seen on a new worksheet as we selected a New Worksheet for it. Then, to view the worksheets’ compiled data, drag the Date range to the Rows Field, the Sales Price to the Values Field, and the Seller to the Columns Field.

create summary table in excel from multiple worksheets method 1

  • After that, you will see monthly and overall reports of the sales from March to June. You can also see seller-based sales records in your Pivot Table.

  • Additionally, click on the Plus button next to the month name if you want to learn more details about sales in that particular month. You’ll receive a date-based sales report as a result.

Thus you can create a summary table from multiple worksheets in Excel by using the Power Query Editor and  Pivot Table.

Read More: How to Summarize Text Data in Excel


2. Applying 3D Reference to Create Summary Table from Multiple Worksheets

If you don’t want complexity, just the data of total Sales, you can use the 3D Reference procedure. It’s actually pretty easy although it sounds a bit complex. Let’s have a glimpse at the description below.

Steps:

  • First, create a new sheet.
  • After that, choose a cell to store the total Sales and use the formula below.

=SUM(June:March!F5:F11)

Applying 3D Reference to Create Summary Table from Multiple Worksheets

The formula uses the SUM function and sheet references to return the total Sales over the period March to June.

Thus you can create a summary table from multiple worksheets in a very simple way.


3. Using Consolidate Command to Create Summary Table from Multiple Worksheets

There is another useful alternative to creating a summary table from multiple worksheets. We can use the Consolidate command from the Data tab to combine the sheets that we want to summarize. Let’s have a look at the dataset that we will use for this method. The data shows how many sales of a particular item have been done by the salesman over the months January to April. We present here 3 items: Item 1, Item 2, and Item 3.

Using Consolidate Command to Create Summary Table from Multiple Worksheets

Please go through the instructions below and you will have a clear idea.

Steps:

  • First, create a new sheet and select any cell where you want your combined data to start. Here, I created a sheet named summary.
  • Next, select Data >> Consolidate (From the Data Tools group). You will see the appearance of the Consolidate window.

  • After that, put the cursor in the Reference section and select the sheet where you put the data first (in my case it’s Item 1).
  • Next, select the range(B4:F11) which you will use to create the summary.
  • Thereafter, click on Add.

  • Similarly, add the other ranges from Item 2 and Item 3 Also, check the options in the ‘Use labels in’ section.
  • Later, click OK.

create summary table in excel from multiple worksheets method 3

  • Next, in the new sheet, you will see the total sales quantity summary of each salesman for those items by month. The command generates a new column after the salesmen column which we don’t need. So delete this column.

  • Let’s do some formatting to make the summary table look better.

  • Here, you can see some Plus icons at the left edge of the Excel sheet. The sales quantity by each item and month is hidden by these Plus Say, we want to know the sales summary done by Falcone. Just click the Plus Icon beside his name.

create summary table in excel from multiple worksheets

  • If you want to know the detailed information, click on button 2 at the left top corner of the sheet. Clicking on 1 will bring up the overall report again.

You can merge the cells containing the salesman’s name to remove the unnecessary borderlines. This won’t affect the procedure at all.

Thus you can create a summary table from multiple worksheets in Excel by using the Consolidate command. This command uses the SUM function automatically to return the total amount of the data based on columns and sheets.

Read More: How to Summarize a List of Names in Excel


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.


Download Practice Workbook


Conclusion

In the end, we can conclude that you can learn the basic ideas of how to create a summary table in Excel from multiple worksheets. If you have any better methods, questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.


Related Articles


<< Go Back to Summarize Data In Excel | Data Analysis with Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo