PivotTable From Multiple Ranges In #Excel

(Note: Video version above, text version below)

Text version:

We have 3 ranges of data that we want to consolidate into one PivotTable. They could be on the same sheet as they are here, or on different sheets:

PT1

We need to access the PivotTable Wizard, but there is no ribbon button for this, so you need to use the keyboard shortcut Alt D P. That will bring up the wizard:

PT2

We’ll select Multiple consolidation ranges and click Next:

PT3

Then we’ll select “I will create the page fields”:

PT4

Now we can highlight each range so it appears in the Range box and click Add so it now shows in the “All Ranges” field:

PT5

Now we will click on each range in the All Ranges box and then type the field name we want to appear in Field One box below:

PT6

After we click Next, we now have the choice if we want the PivotTable to appear on the same sheet or a separate sheet:

PT7

Now we click Finish and the consolidated PivotTable is created:

PT8

Now we can filter the data, move the fields around using the PivotTable Fields List, and do all the other functions that we can with PivotTables:

PT9

Although there were some salespeople that appeared in multiple states, Excel consolidated those values into one line item. What a great way to manage large ranges of similar data into one great analytical tool – the PivotTable!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

No comments yet.

Leave a Reply