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: Newest
Most helpful Newest Oldest
  1. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2013-03-22T22:38:14+00:00

    Hi,

    A ' in a cell is effectively an empty cell.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-03-22T13:39:58+00:00

    Dear Ashish,

    In February 2012 on the Microsoft Community site, you posted a fix to hide any

    ”(blank)”s in a pivot table report by using Find & Replace with an apostrophe as the Replace character.

    Does that effectively format a cell as a text cell with an empty string so that Excel is tricked into thinking the cell is not empty or does the apostrophe serve a different function?

    In other words, why an apostrophe and how does it work?

    Thank you in advance for any reply you are willing to send me.

    Kind regards,

    Peter Cloutier, Geneva, Switzerland

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2013-03-20T02:21:00+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.

    Is there a way to do this against a PowerPivot Table?  When I tried the method above, the  ' did replace the (blank) but displayed as is rather than with a blank field.

    Thanks,

    Amy

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-03-01T10:54:03+00:00

    ok, got it... 

    In the Table all blanks were replaced by an apostrophe.

    (Now i'm not sure how did you make them invisible ?)

    Nevertheless this a workaround more than a solution in my opinion.

    Now after a lot of time spent/wasted to find for a solution i can say there is none.

    Thanks anywhere.

    Was this answer helpful?

    0 comments