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

Auto-Generated 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 three
  • 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 you've got to update
  • this art after excel 2003 then LLL never
  • got to see it again because they took it
  • off the ribbon thanks for improving that
  • choose multiple consolidation range and
  • step one in step 2 a of three look they
  • have no clue how many steps there's
  • going to be it i'll say i will create
  • the page fields in step to be of three
  • specify the first range and click add
  • specify the second range and click add
  • specify the third 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 and they don't even know what
  • these are called this is called a 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 is that third
  • dataset and so on but if you just want
  • to report the first date
  • said well then let's face it you would
  • have just used that so I'm not a fan of
  • the page feels 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 nail good trick to do 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 eye
  • on the top right hand corner right up
  • here that one that I right up there you
  • can buy the book all right 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
  • DP there's also a pivot table classic
  • ikon you could add up here if you can't
  • remember all TP 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 net cast from mr.
  • excel

Download File

Download the sample file here: Podcast2047.xlsm

Title Photo: wsanter / Pixabay

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.