none
Hide Blank Columns in a Pivot

    Question

  • I posted this on the Public.Excel forum and a Community Star Engineer suggested that I post my question here. Do any of you have any suggestions?

    --------------------------------------------------------------------------

    I am having problems hiding blank columns in a pivot. When I go to pivot options, the 'Show Items with No Data in Columns' is whitened out and won't allow me to check the box. I am using version 2007.

     

    Is there a way that I can get this feature to work? If not, do I have alternatives

    Tuesday, August 28, 2012 7:27 PM

Answers

  • Hi,

    Based on my research, the “'Show Items with No Data in Columns” option applies for OLAP based pivot tables only.

    And I think as this issue, we can use Filter to hide items with no data.

    For example, if the item you want to hide is a calculated data, then use the following steps:

    Select the Row Labels in the Pivot Table, right click -> Filter ->Value Filters... -> select the items listed in the dropdown list -> select “does not equal” -> type “0” (not include the brackets) in the last textbox.

    For the Row Labels, do the same as the above steps, but choose Label Filters… instead of Value Filters… and leave the last textbox blank.


    Jaynet Zhang

    TechNet Community Support

    Wednesday, August 29, 2012 8:53 AM

All replies

  • Hi,

    Based on my research, the “'Show Items with No Data in Columns” option applies for OLAP based pivot tables only.

    And I think as this issue, we can use Filter to hide items with no data.

    For example, if the item you want to hide is a calculated data, then use the following steps:

    Select the Row Labels in the Pivot Table, right click -> Filter ->Value Filters... -> select the items listed in the dropdown list -> select “does not equal” -> type “0” (not include the brackets) in the last textbox.

    For the Row Labels, do the same as the above steps, but choose Label Filters… instead of Value Filters… and leave the last textbox blank.


    Jaynet Zhang

    TechNet Community Support

    Wednesday, August 29, 2012 8:53 AM
  • That worked. Thanks so much for your help/
    Wednesday, August 29, 2012 7:38 PM