Excel - Multiple Consolidation Range Pivot Table - Episode 2047

Microsoft Excel Tutorial - Multiple Consolidation Range Pivot Tables.

Welcome to episode 2047 of the MrExcel podcast, where we will be discussing the powerful tool of Multiple Consolidation Range Pivot Tables in Excel. If you missed yesterday's episode, make sure to check it out as we covered how to use Data Consolidate to combine multiple tables into one data range. But today, we will be taking it a step further with Pivot Tables.

In order to access the Multiple Consolidation Range feature, we will need to go back to the classic version of Pivot Tables from Excel 2003. Don't worry, it's easy to access. Simply press Alt D for Data, then P for Pivot, and voila! The classic Pivot Table option will appear. A special shout out to the Microsoft team for updating the art for this feature, even though it was removed from the ribbon. Your efforts are appreciated!

Once you have selected the Multiple Consolidation Range option, you will be prompted to choose the ranges you want to consolidate. This is where the magic happens. You can select multiple ranges from different tables and combine them into one Pivot Table. And don't worry about the number of steps, just keep clicking "Add" until you have all the ranges you need.

After selecting your ranges, you will be taken to the familiar Pivot Table layout. But here's where it gets interesting. You can choose to create page fields, which will allow you to filter the data by different groups or datasets. However, I personally do not use this feature often as it can be easily achieved with other tools. But it's good to know it's there in case you need it.

As always, there are some clean up steps that need to be done, such as changing the report layout and showing empty cells as 0. But overall, this is a great trick to have in your Excel arsenal. And if you want to learn more tricks like this, make sure to check out my book, which includes 70 tricks, bonus tips, keyboard shortcuts, and even some Excel cocktails. Just click the "i" in the top-right corner to purchase the book.

That's it for today's episode. Remember, if your goal is to create a Pivot Table, the Multiple Consolidation Range feature is a great tool to have. And if you can't remember the shortcut, you can always add the PivotTable Classic icon to your ribbon. Thanks for tuning in and we'll see you next time for another netcast from MrExcel!

This video answers these common search terms:
excel multiple consolidation pivot tables tutorial
how to create multiple consolidation pivot tables in excel
consolidating data with pivot tables in excel
excel pivot tables for data consolidation
step-by-step guide to multiple consolidation pivot tables
advanced excel data analysis with pivot tables
excel pivot table data consolidation techniques
using pivot tables for data summarization in excel
how to make multiple excel sheets into pivot table
how to pivot multiple excel sheets

#excel
#microsoft
#microsoftexcel
#exceltutorial
#excelhacks
#evergreen
#pivottable
#pivot_table
#excelpivot
#excelpivottablestutorial

Table of Contents:
(00:00) Using Alt D P to access Pivot table in Excel 2003
(00:43) Shout out to Microsoft for improving Pivot table art
(00:53) Choosing Multiple Consolidation Range in Step 1
(01:05) Specifying ranges in Step 2a and 2b
(01:29) Creating a new worksheet and finishing the Pivot table
(01:41) Clean up steps for the Pivot table
(02:04) Using page fields for grouping data sets
(02:14) Alternative method for reporting data sets
(02:24) Consolidating data using Pivot table
(02:34) Buy the book
(02:48) Clicking Like really helps the algorithm

No one bothered to use Multiple Consolidation Range pivots from Excel 97-2003, so they were locked in a cave starting in Excel 2007. But you can still pull them out when you need them! If your goal is to create a pivot table, you can consolidate multiple ranges into the table.
Use Alt+D P to start the pivot table
Choose Multiple Consolidation Ranges
Page fields are optional

[pmsocho]

Thanks for the video. Good to know it but that mechanism is limited to numbers. That is why I used it maybe once or twice. Thanks to MS for giving us Power Query - that is definitely a gamechanger for every consolidation problem! :)

[lazalazarevic]

great video, just when I thought I know everything about PivotTables ;)