Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
Worth $20, Absolutely Free
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.
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.
Now, to analyze this data, you need to make a single pivot table report from these multiple sheets.
Some of the common methods are:
But the thing is, these methods require coding, copy-pasting, or, are 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.
Here we have simple steps which you can follow and before that, please download this file from here to follow along.
Select * from Year2005
Select * from Year2006
Select * from Year2007
Select * from Year2008