This post is brought to you by Chandeep from Goodly. He is a consultant and trainer. He helps people on Business Intelligence Dashboards, Business Plans, Financial Models and PowerPoint Presentations. Make sure to download his special resources from here.
And, today he is going to solve a save a problem for you. I am sure you all have faced this problem “Use Multiple Worksheets in a Pivot Table”.
So, here is Chandeep’s solution for you.
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 that:
- There are 4 Sheets (one for each year)
- The data & the number of rows of data in all the 4 sheets is different but the structure is exactly the same i.e. Same number of columns & same headers.
To analyze this data, you need to make a single pivot table report from these multiple sheets – How would you do that?
- Manually copy and paste data from each sheet and make one single dataset on a new sheet.
- Use a VBA code to automatically consolidate data from multiple sheets.
- Consolidate multiple worksheets with the consolidate option.
But the above methods are cumbersome (i.e. require coding), verbose (like copy-pasting) or repetitive.
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.
Check out this video tutorial to learn this awesome tip.
Steps To Create a Pivot Table from Multiple Pivot Table
- 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
- 2008 data named as – Year2008
- Convert the named ranges into Tables.
- Select any cell in the data range.
- Use the shortcut CTRL + T to convert the data on each sheet it into a table format.
- 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 Choose Data source box:
- Click on Excel Files and then press OK.
- Select the path of your excel file and then select your file click on OK.
- The named ranges will appear in the ‘Query Wizard- Choose Columns’ box.
- 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
Select * from Year2006
Select * from Year2007
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.
Pivot tables are one the most important things you can use for data analysis. And, by using the steps mentioned above you can achieve an extra mile in data analysis.
These steps are such a real-life problem solver. I have learned this tip from one of my friends Ashish Mathur. And, I hope you liked it.
So tell me one thing. Have you ever tried to create a pivot table from multiple worksheets? Share with me in the comment box.
Words By Puneet
A BIG Thank You to Chandeep for this awesome pivot table tip.
And, make sure to download his special resources for everyone here at ExcelChamps.