Using Table Reference Syntax with a Multiple Consolidated Range Pivot Table

Posted on 2011-10-25
Last Modified: 2012-05-12
Using the Pivot Table Wizard in Excel 2007  (Alt+D+P)

I would like to use Table reference syntax like "Tbl_Data1[Color]" as the range reference in a Multiple Consolidate Range Pivot Table.    When the columns of data are referenced with column references "Data1!$A:$B" all the data is returned to the Pivot.   When the columns of data are referenced with Table reference "Tbl_Data1[[Color]:[Type]]" the first row of each data set is not returned.   I believe it assumes the first data value is the column header (which it is not w/ table reference).

Is there a way use the Table reference and return all the data?   I am concerned about using column reference because the data sets can have columns add or deleted, making the reference invalid in the Pivot.

 Multiple-Consolidated-Ranges.xlsx Step1 Step2 Step 3a - Tablesstep3b.png
Question by:Jerry Paladino
    LVL 41

    Expert Comment

    I'm looking at this.  There are a couple solutions:

    1.  Create an MS Query Union of both tables, then run your Pivot Table against that
    2.  Insert a blank row in each table - not the perfect solution, but appears to work properly after that!
    3.  Recreate the tables - this time, DON'T check "my table has headers" = see tip here:

    This last one appears to be just as functional as #2 - creating the blank row.

    I'm for #2, where you can maintain the column reference to Color and Type as opposed to Column 1 and Column 2 which could be a different column set on data refresh, if I'm reading your question correctly.

    See attached.


    LVL 85

    Accepted Solution

    Your syntax should be:


    LVL 41

    Expert Comment

    Nice!  Very.
    LVL 85

    Expert Comment

    by:Rory Archibald
    Ta. Much.
    LVL 16

    Author Closing Comment

    by:Jerry Paladino
    Works perfect - Thanks Rory!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now