How to Create a Pivot Table from Multiple Worksheets

With a pivot table, you summarize your data within a few seconds. You can create reports, analyze data, and easily share with others.

That's the beauty of pivot tables.

But here is a twist:

Normally when you create a pivot table you can select source data from a single table in a worksheet as Excel doesn't allow you to refer to different worksheets.

But sometimes, it happens that we need to use the source data from multiple worksheets to create a pivot table.

So today, I'd like to share with you simple steps to use multiple worksheets in a pivot table.

The Problem!

Assume that you want to analyze the sales data of your company and you pull out the yearly data for the last 4 years.

This is how you get the data dump in Excel.

Please note, the data in all the 4 sheets is different but the structure is exactly the same i.e. same number of columns and heading.

Some of the common methods are:

1. Manually copy and paste data from each sheet and make one single data set on a new sheet.
2. Use a VBA code to automatically consolidate data from multiple sheets.
3. Or you can, consolidate multiple worksheets using into a single worksheet by using Excel's consolidate option.

But the thing is, these methods require coding, copy-pasting, or, are repetitive.

The Solution

Here I am going to discuss a new method using Microsoft query which is dynamic, robust and simple.

Trust me you’ll love it!

By using Microsoft query you can create a pivot table from multiple worksheets.

Download this data file to follow along.

Steps To Create a Pivot Table from Multiple Worksheets

• First of all, select all the data on each sheet and name them.
  • 2005 data named as – Year2005
  • 2006 data named as – Year2006
  • 2007 data named as – Year2007
  • 2007 data named as – Year2007

• After that, apply data tables to all the data in fours worksheets.
  • Select any cell in the data range.
  • Use the Ctrl + T to convert the data on each sheet it into a table.
  • Make sure “My table has headers” is checked each time.
  • Repeat this for all 4 years (sheets).

• Let’s start consolidating this data on a new sheet (shortcut to add a new sheet : Shift + F11).
• In the Data Tab, click on ‘From Other Sources’ -> Choose ‘From Microsoft Query’.

• In the Query Wizard:
  • Drag each named range (using the arrow button) into the ‘Columns in your query area’.
  • Click on Next – Forget about the error.
  • And click on OK

• Now, we have all the data in our query editor the only thing remaining is to combine the data from all the named ranges. To do that click on the little SQL button.

• In the SQL box, delete all the text and write a new query and click OK.

Select * from Year2005
Union all
Select * from Year2006
Union all
Select * from Year2007
Union all
Select * from Year2008

• Now, the table that appears on the screen has the data from all the 4 sheets.
• All we need to do is go to File Tab and import that table into Excel.
• In the end, import the data back to excel as a pivot table.
  • From the File Menu -> click on Return Data to Microsoft Excel.
  • You can see that in total (from all 4 sheets) we have 592 records.
  • Also if you add more data to any of the 4 sheets, the pivot table will update as soon as you refresh it.

NOTE: Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.