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:


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:


We’ll select Multiple consolidation ranges and click Next:


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


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:


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:


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


Now we click Finish and the consolidated PivotTable is created:


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:


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!

Free Download!

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

No comments yet.

Leave a Reply