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.

Download Practice Workbook

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

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.

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 from multiple worksheets in Excel 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.

• 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.

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.

• 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.

• 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.

• 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.

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.

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.

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.

• Next, in the new sheet, you will see the total sales quantity summary of each salesman for those items by months. 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.

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

You can merge the cells containing the Salesmen’s names 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.

Practice Section

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

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 or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.

The post How to Create Summary Table from Multiple Worksheets in Excel appeared first on ExcelDemy.