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-10-24T22:46:15+00:00

    You are welcome.  If my reply helped, please mark it as Answer.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-10-24T19:56:09+00:00

    Thank you Ashish! Tht's the only thing that worked :)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-08-22T15:38:11+00:00

    You saved my day! Had a last minute fix to my pivot and (blank) was out of the questions for a report to be  shown to upper management. THANK YOU !

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-07-28T11:13:09+00:00

    Use an old Excel trick: tripple semicolon in format type.

    1. On a Pivot table: select your PivotTable column.
    2. Choose [Conditional Formatting], [New Rule] pick "format only cells that contain" and choose "Specific Text"; "containing" and type "(blank)" (without quotation marks but with brackets).
    3. Then click on “Format” and choose the “Number” tab.
    4. Under Category pick "Custom" and in "Type" enter three semicolons ";;;" (without quotation marks). [OK] twice and you're done.

    You may have to resize the conditional formatting range if you refresh your PivotTable with new values.

    Was this answer helpful?

    9 people found this answer helpful.
    0 comments