How to Consolidate Multiple Excel Worksheets into One PivotTable

The article will show you how to consolidate multiple worksheets into one Pivot Table. When you have a similar type of data in multiple worksheets, it’s convenient for you to merge or consolidate them into one Pivot Table to analyze the whole data of those worksheets. A Pivot Table is a vital tool for making an effective analysis on summarizing the whole dataset. In this article, you will see how to use a Pivot Table to consolidate multiple worksheets.

In the dataset, we have sales information about some products and sellers who sold them in the months of March, April, May, and June.

It’s convenient to use the data as a table. To convert this data to a table,

• Select the dataset and then go to Insert >> Table.
• After that, a dialog box will show up. Make sure you select My table has headers and click OK.

Finally, your data is converted to a table. We will be using our data as tables in the following sections.

1. Using Excel Power Query Editor to Consolidate Multiple Worksheets into One PivotTable

The most effective way to consolidate multiple worksheets of an Excel Workbook is to use a Power Query Editor. Let’s go through the procedure below for a detailed description.

Steps:

• We will be using the following sheets for consolidation into one Pivot Table.

• 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, I named my query Overall_Report and hit ENTER.
• Later, type the following formula in the Power Query formula bar and hit ENTER.

The formula will return all the tables in the workbook.

• 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. As we are working with the first four tables of this workbook, we selected four.

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

• Next, you will see all the data of your sheets will consolidate together.

• The query may contain some unnecessary format like the date and time You will see there is a time after every date by default when the query opens. To remove this, we clicked right on the mouse while selecting the Date column and then chose Change Type >> Date.

• By the way, if your data contains any blanks, use the following command. 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. This command is not necessary all the but it’s a good practice while working with 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.

• You will see your Pivot Table in a new worksheet as we chose a New worksheet for it. After that, drag the Date range to the Rows Field, Sales Price to the Values Field, and Seller to the Columns Field to have a look at the summarized data of your worksheets.

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

• In addition, if you want to find more information about sales in a month, click on the Plus icon beside the month name. This will return you a date-based sales report.

Thus you can consolidate multiple worksheets into one Pivot Table by using a Power Query Editor.

2. Applying PivotTable and PivotChart Wizard to Consolidate Multiple Worksheets

You can also use the PivotTable and PivotChart Wizard to consolidate multiple worksheets of your Workbook. Although this method has some drawbacks for sales calculation, we can still use it for simple sales records. Let’s have a look at the procedure below.

Steps:

• Here, we will be working with the Sales So we will use the same worksheets but we will omit the Bill ID column. It may cause some redundant calculations in the Pivot Table.

• After that, press ALT, D, and P You will see the PivotTable and PivotChart Wizard on the screen.
• Next, select the field where you want to analyze your data. As our article is dedicated to the Pivot Table, we select Multiple consolidation ranges and PivotTable. In addition, if you want to do a detailed analysis, you may select the PivotChart report instead of just PivotTable.
• Later, click Next.

• Thereafter, you will have two options of how many page fields you require for analysis. You can either choose Excel to create a page field automatically for you or you can make your own. In this case, I select Create a single page field for me which means Excel will create it automatically for me.
• After that, click Next.

• Next, you will see a message box where you will add the ranges of your tables for the Pivot Table Just select the sheet where the table of a sales report is, then select the table and click Add. First I selected the table of June (2) sheet.

• Similarly, add the other tables too.
• After that, click Next.

• Select New worksheet or Existing worksheet based on your convenience.
• Click Finish after that.

• Previously, I mentioned that this method is not as effective as the first one. We need to understand the meaning of the ranges in the PivotTable Fields. Here, Row refers to the dates and Value refers to the sales values. So drag Row and Value fields to the Rows and Values Fields

• You can observe that the value field of the sales values is in Count of Value. We need the sales values and their total so we click on Count of Value field >> Value Field Settings.

• In the Value Field Settings window, we select Sum of Value and click OK.

• This operation will return a monthly sales report in the Pivot Table.

• If you want to expand your analysis on a daily basis, click the Plus icon beside the month name.

Thus you can consolidate multiple worksheets into one Pivot Table by using the PivotTable and PivotChart Wizard.

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 consolidate multiple worksheets into one Pivot Table. If you have any better methods or questions or feedback regarding this article, please share them in the comment box.