MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Consolidating to a Pivot Table


October 12, 2017 - by Bill Jelen

Consolidating to a Pivot Table

Consolidate several ranges in Excel to a single pivot table. This episode will show you how.

The other ancient tool is the Multiple Consolidation Range pivot table. Follow these steps:

  1. Press Alt + D, P to invoke the Excel 2003 Pivot Table Wizard.
  2. Choose Multiple Consolidation Ranges in step 1. Click Next.

    Multiple Consolidation Ranges
    Multiple Consolidation Ranges
  3. Choose I Will Create the Page Fields in step 2a. Click Next.
  4. In Step 2b, use the RefEdit to point to each table. Click Add after each.

    Add Ranges to be Consolidated
    Add Ranges to be Consolidated
  5. Click Finish to create this pivot table.

    Result Pivot Table
    Result Pivot Table

Thanks to CTroy for suggesting this feature.

Watch Video

  • 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

Video Transcript

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!

Download File

Download the sample file here: Podcast2047.xlsm

Title Photo: wsanter / Pixabay


Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.