Remove (blank) from pivot table

Anonymous
2011-02-25T12:16:56+00:00

Hi,

My source data has empty cells.  When I PT this data - the PT shows "(blank)" for the empty cells.  How can I keep the cells "empty" in the PT.  I do not want to manually filter and uncheck (blank) as this removes other data which is needed.

TIA,

PS

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2011-02-25T14:15:06+00:00

Excel 2007/2010 PivotTable

Show blank cell instead of (blank)

in RowField/ColumnField item labels.

http://c3017412.r12.cf0.rackcdn.com/01_23_11.xlsx

If you get *.zip, don't unzip, just rename *.xlsx

Was this answer helpful?

200+ people found this answer helpful.
0 comments

46 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. triptotokyo-5840 36,701 Reputation points Volunteer Moderator
    2011-02-25T12:49:53+00:00

    Try this:-

    Right click in the Pivot Table.

    PivotTable Options . . .

    Layout & Format tab.

    In the:-

    Format

     - section there is a box called:-

    For empty cells show:

    Make sure the above box is ticked (checked) but is blank.

    Click on:-

    OK

    If my comments have helped please Vote As Helpful.

    Thanks.

    Was this answer helpful?

    100+ people found this answer helpful.
    0 comments
  2. Anonymous
    2011-02-25T14:18:33+00:00

    Hi ttt,

    Thanks for your reply.  I tried this first of all before I asked the question but it had not effect (I did refresh PT after).  I've tried checking and unchecking the box but that had no effect.  I tried putting in "zzzzz" (ticked and unticked) but that had no effect either!

    Could there be a.n.other reason why the PT isn't acting as expected?

    Thanks,

    Pete

    Was this answer helpful?

    40+ people found this answer helpful.
    0 comments
  3. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2012-02-13T00:51:10+00:00

    Hi,

    If you do not wish to change the source data, then try this

    1. Select the pivot table (Ctrl+A while on any cell in the pivot table)
    2. Press Ctrl+H
    3. In the Find box, type (blank)
    4. In the Replace with box, type '
    5. Click on Replace All

    Hope this helps.

    Was this answer helpful?

    20+ people found this answer helpful.
    0 comments
  4. Anonymous
    2012-02-12T22:27:27+00:00

    And almost a year later, another amazed and happy PT user...what a great little workaround. (And where is it that you can set an option to ignore 'blanks'? I know I've seen it somewhere...) Any way, thanks

    Was this answer helpful?

    9 people found this answer helpful.
    0 comments