Excel Pivot Table Missing a Column from Source Data?

  • Article

I have a large spreadsheet I’ve gotten from a colleague.  To help categorize it for a pivot table, I added a column which is “group”.  I then added a table in a different sheet that mapped people to groups – and used the VLOOKUP function in the “Group” column to lookup the group from the mapping table sheet. 

I then went to pivot the data by clicking “Summarize with Pivot Table” in the “Table Tools” ribbon section, but the pivot table field list doesn’t contain my group column.

At first I thought that maybe for some reason calculated fields wouldn’t be included in the pivot table – but this made no sense and there are are other calculated fields in my source data.

After poking around a bit on the web, I found this post which describes the problem and how to fix it.  Basically, Excel has a “pivot table” cache which needs to get refreshed.  Since other pivot tables had been created in the workbook based on my source data by the person who gave it to me, Excel “knew” what the source data looked like – and in its view, it didn’t have a “Group” column.  Simply going to the pivot table sheet, selecting the “Pivot Table Options” tab and clicking “Refresh” made the “Group” column appear in my pivot table field list.

Comments

  • Anonymous
    January 22, 2013
    The comment has been removed

  • Anonymous
    February 27, 2013
    This is great information about Refresh in the Pivot Table ... and I know your original posting was a few years ago ... but I have found another similar problem I am wondering how to solve.  I was able to fix a worksheet on which the Pivot Table was refreshed to include all the data.  However, I used a DSUM formula to sum items from a table (not a Pivot Table) with dates set for which I wanted another column summed (similar to the Pivot Table).  The DSUM gave me the same result that I had gotten originally with the Pivot Table (before I refreshed it).  Is there something wrong with my Table of data?  There does not seem to be anything I can do to "refresh" the DSUM.

  • Anonymous
    April 03, 2014
    Works for Excel 2013.  Thanks for the post!

  • Anonymous
    April 23, 2014
    So glad you posted this--it was the easiest fix to a problem that I have been trying to figure out on my own for over 30 minutes!

  • Anonymous
    April 25, 2014
    So glad I found this.....I've been trying to fix a problem like this since yesterday!

  • Anonymous
    July 08, 2014
    The comment has been removed

  • Anonymous
    November 25, 2014
    You are a genius.  This problem was really perplexing me.

  • Anonymous
    November 26, 2014
    Excellent help - Thank you.  I've spent hours trying to figure this one out. I couldn't even figure how to begin searching for online help for it.  

  • Anonymous
    February 02, 2015
    Thanks for the post. I almost wanted to revert to office 2010 because of the frustration around this..:)

  • Anonymous
    February 03, 2015
    You are a Genius Man, great help. I use the refresh button when I've added information to the source data and did not know I could use it to bring up unseen columns.

  • Anonymous
    August 26, 2015
    Good stuff, mate. It works on Excel 2016 too.

  • Anonymous
    September 29, 2015
    Thanks! Couldn't figure this one out!

  • Anonymous
    November 24, 2015
    Superb and easy solution thank you very much for this.  

  • Anonymous
    February 01, 2016
    I wish there was a setting to force an "auto refresh" so new data entries automatically showed up on the pivot charts.