If Excel data is on different sheets, you can create a pivot table from multiple sheets, by using multiple consolidation ranges. My video, further down this page, shows you the steps.
Of course, it’s better if the data is all on one sheet. But, if you don’t have that option, the multiple consolidation ranges will pull all the data into one pivot table.
How to Get Started
Long ago, in Excel 2003, you could open the PivotTable and PivotChart wizard by going to the Data menu. Then, you could click the PivotTable and PivotChart Report command.
Unfortunately, there’s no equivalent command on the Ribbon in later versions of Excel.
However, you can press the keyboard shortcut:
- Alt+D, then type P
That will open the PivotTable and PivotChart wizard.
Pivot Table Limitations
The pivot table from multiple sheets isn’t as flexible as a regular pivot table:
- All the data fields use the same summary function
- There’s only one row field
- The first column in the data is used as the row field
However, while you’re setting up the pivot table you can create one or more page fields, and create labels for the data ranges.
Read the Instructions
There are written instructions, and details on the pivot table limitations, on my Contextures website:
Excel Pivot Table From Multiple Consolidation Ranges
Watch the Video
The short video below shows you the steps to create the pivot table from multiple sheets, and set up a page field for each set of data.
Video Timeline
- 0:00 Data on 2 Sheets
- 0:24 Open PivotTable Wizard
- 0:50 Select Sheet Ranges
- 1:08 Page Field Settings
- 1:29 Adjust the Pivot Table
- 2:04 Show Sum
- 2:15 Page Field
____________
Excel Pivot Table from Multiple Sheets
Very Useful and well explained.
Thanks a ton!!
Good information, but my Excel 2007 does have a pivot table choice on the ribbon and I am unable to do what was shown in the video, i.e., consolidate data from multiple sheets. alt+D, P don’t bring up anything.
Never mind, I’m a clutz. I got it. Cheers.
Tried all your suggestions but I’m still having problems to get to PivotChart Wizard in Excel 2007. There is no Pivot Table Wizard in Options. Keyboard shortcut — Alt + D, P don’t bring up anything.
Could anyone help me?
Took me some time to discover that you can press Alt+D, then P for the English version. You must type A (not P) in the Portuguese version
Thanks a lot Marco!!!
excellent piece to knowledge…:)
It will help me to play with huge data where I have more than 25 lacs rows in three different sheet…..Great.
The modification I’m looking for would use a pivot column filter. I have 6+ pivots all with the same values in each pivot tables column filter. There are no other column based filters. (have a couple of Row filters)
What I had hoped was being able to replace pivotitems with pivotcolumns but it doesn’t appear that function exists.
Is there another approach?