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

D

davejago

Board Regular
Joined
Apr 29, 2005
Messages
133
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?!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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.
 
Upvote 0
xenou said:
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.
Click to expand...
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.
 
Upvote 0
xenou said:
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.
Click to expand...


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

Thanks!!
 
Upvote 0
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"
 
Upvote 0
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...
 
Upvote 0
sparlaman said:
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"
Click to expand...


Used this in Excel 2010. It saved me!!!! Thanks a lot!
 
Upvote 0
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.
 
Upvote 0
sparlaman said:
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"
Click to expand...


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Timeline on Pivot Table not working
Replies
1
Views
328
Sergius
S
C
  • Question Question
How to show a list view of columns based off another sheet / alternative to Pivot table?
Replies
7
Views
126
Domenic
D
M
  • Question Question
Pivot Table Calculated Item Crashing Workbook
Replies
1
Views
399
cooleomter
C
T
  • Question Question
Retaining structure of Pivot table after refresh
Replies
1
Views
279
Alex Blakenburg
A
F
  • Question Question
Showing details of Pivot table from Grand total, including Row filtering
Replies
0
Views
234
Francois2France
F

Forum statistics

Threads
1,224,592
Messages
6,179,787
Members
452,942
Latest member
VijayNewtoExcel

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top