Excel Pivot Table Missing a Column from Source Data?


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 (14)

  1. Dave says:

    Have been scratching my head over this problem for an hour. Found your post and this worked for me. Thanks!

    For anyone else that finds this – I'm running Excel 2007 and right-clicking anywhere in the pivot table and selecting 'Refresh' made the missing column appear.

  2. Suzette says:

    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.

  3. Chris says:

    Works for Excel 2013.  Thanks for the post!

  4. MP says:

    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!

  5. Bev says:

    So glad I found this…..I've been trying to fix a problem like this since yesterday!

  6. Ken says:

    It worked!  (I found a lot of other "solutions" on line that didn't.)

  7. Rob says:

    You are a genius.  This problem was really perplexing me.

  8. Ron says:

    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.  

  9. Kigotho says:

    Thanks for the post. I almost wanted to revert to office 2010 because of the frustration around this..:)

  10. MD, South Africa says:

    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.

  11. William says:

    Good stuff, mate. It works on Excel 2016 too.

  12. Mark says:

    Thanks! Couldn't figure this one out!

  13. John says:

    Superb and easy solution thank you very much for this.  

  14. JohnL says:

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