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.