Here’s part 2 of our series on consolidating multiple Excel ranges into a single Pivot Table. Lesson 18 added the Pivot Table Wizard to the Quick Access Toolbar and prepared the data to consolidate. This video goes through the Pivot Table Wizard to actually create the Pivot Table. One of the tricks to consolidating data is to not include any totals in the ranges you add in the wizard. Watch carefully and you’ll see I accidentally leave out the top row with the months in two of the three ranges. When I don’t get the result I expect, I include in the video how to go back and correct the ranges.
Next time we’ll go through and clean up several issues in the consolidated Pivot Table and I’ll show you how the page fields we set up in the wizard can be used in the new Pivot Table. Cleaning up the Pivot Table will show you a few tricks you can use in all Pivot Tables, not just tables that consolidate multiple Excel ranges.
Finally, I put all three tables I wanted to consolidate on different tabs on the same spreadsheet, just to make things easier in the demonstration. You can also consolidate ranges from the same spreadsheet tab or from completely different spreadsheets, depending on where your data is.
I hope you find these videos helpful.