Consolidating to a Pivot Table

Learn Excel from MrExcel podcast, episode 2047 - Multiple Consolidation Range Pivot Tables!

I am podcasting all of my tips from this book, click the “i” in the top-right hand corner to get to the playlist!

Yesterday in 2046, I showed how to use Data Consolidate to take 3 tables, different number of columns, but still the same basic shape, names down the left, months across the right, and put it together into a data range. You can do this with a Pivot table, but not the new Pivot table, you have to go back to Excel 2003, and the way to get back to Excel 2003 is to do Alt D for Data, P for Pivot, and BAM, there it is. A shout out to the person at Microsoft who got to update this art after Excel 2003, never got to see it again because they took it off the ribbon, thanks for improving that!

Choose Multiple consolidation range in Step 1. In Step 2a of 3 look, they have no clue how many steps there's going to be, I'll say I will create the page fields. In Step 2b of 3 specify the 1st range and click Add, specify the 2nd range and click Add, specify the 3rd range and click Add, alright, so now I have three different ranges I'm going to put together into a Pivot table. I'll click Next, say Go to a new worksheet, click Finish, and there. It's taken all of the data from all three datasets, all the months from all three months(?), and put it together into a single Pivot table.

Now of course, we still have the same clean up that we would normally have to do. Report Layout, Show in Tabular Form, right-click, Pivot Table Options, For empty cells show 0, click OK, but it is an interesting way to go. See, they don't even know what these are called, this is called Row and Column, interesting way to go. If you had created page fields, you have a report filter up here that you could say maybe Group 1, Group 2, Group 3, you know, the first data set, second data set, third data set and so on. But if you just want to report the first data set, well then let's face it, you would have just used that, so I'm not a fan of the page fields. I do this once in a while, it's a good way to consolidate data, like really, just to create the Pivot table. I know, this was an intermediate step on uninhibited data before Power Query came along, but every once in a while it's a good trick to know.

This trick, and a whole bunch of other tricks, 70 tricks plus some bonus tricks, plus some keyboard shortcuts, plus some excel cocktails, all in this book. Click the “i” on the top-right hand corner, right up here, that one, that “i” right up there, you can buy the book. Alright, short episode today: If your goal is to create a Pivot table, you can consolidate multiple ranges, you have to get to that using Alt D P. There's also a PivotTable Classic icon you could add up here, if you can't remember Alt D P. Use multiple consolidation ranges, page fields are optional, I usually do not use them.

Thanks for stopping, by we'll see you next time for another netcast from MrExcel!