Show columns in pivot table, even if they are empty - Excel 2007

[davejago]

I want to be able to have a standard set of lookup formulas in a worksheet that reference specific columns in a pivot table. However, when I refresh the pivot table with updated data, some of the columns go out of view because they have no data in them. I'd like those columns to show in my pivot table, even if they contain no data so that my lookup formulas continue to work.

In my pivot table options, I have the following already checked:
1. Display item labels when no fields are in the value area.
2. For empty cells show ...

I noticed though, that the 'Show items with no data on rows', and 'Show items with no data on columns' are greyed out - I cannot check these boxes. These may resolve this issue so if they do...how do I un-grey them?!

 

[xenou]

Try right-clicking on a field or column label, instead of options for the whole pivot table. There is (I think) a box there to be checked in the bottom left corner - show items with no data.

 

[sparlaman]

Try right-clicking on a field or column label, instead of options for the whole pivot table. There is (I think) a box there to be checked in the bottom left corner - show items with no data.

I'd like to re-open this topic as I am running into this exact issue and the solution posted here doesn't seem to be applicable. I have looked all over to find a solution and am unsuccessful. There are some VBA scripts out there for this as well but I don't understand them to be able to implement them and/or edit them to function. I keep reading that OLAP data won't allow this functionality but I don't know what OLAP is and whether or not that's my problem.

PLEASE HELP! All help is greatly appreciated.

 

[oldfisher]

Try right-clicking on a field or column label, instead of options for the whole pivot table. There is (I think) a box there to be checked in the bottom left corner - show items with no data.

Great help! It works wonderfully and the result is what I need.

Thanks!!

 

[sparlaman]

This applies to Excel 2007
More detailed instructions because there were a few steps left out...
Right Click one of the Values in the Field or Column
Click Field Settings
Click Layout & Print Tab
Check "Show items with no data"

 

[sziyad2011]

So I have the same problem as above but when I follow those directions I get all the items from all the columns instead of just the one I chose to show all for... is there a way to only show the items with no data for one column.
Also, when I look into the field settings for the other columns I didn't want all the data for the box is not selected...

 

[Krebs]

This applies to Excel 2007
More detailed instructions because there were a few steps left out...
Right Click one of the Values in the Field or Column
Click Field Settings
Click Layout & Print Tab
Check "Show items with no data"

Used this in Excel 2010. It saved me!!!! Thanks a lot!

 

[Jacqui van Graan]

Hi thank you for the help on this... I have the same issue as this thread. I completed checking the said boxes for all the fields so it shows as checked. The option is still greyed out in the table options so not sure whether this is the issue ... but the columns with no data are still not showing in the pivot table even after refreshing?? Could someone please assist me as I have quite a few tables with formulae and this will save so much time.
Thank you in advance.

 

[lineetta]

This applies to Excel 2007
More detailed instructions because there were a few steps left out...
Right Click one of the Values in the Field or Column
Click Field Settings
Click Layout & Print Tab
Check "Show items with no data"

Thank you for this! In Excel 2010 the solution was very slightly different--I right-clicked on the header of the column (or the name of the column field), then followed the steps above. Right-clicking on the values in the table itself brought up different options.