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. 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
  2. Anonymous
    2012-04-26T13:09:21+00:00

    Hi,

    I figured a workaround which you might be interested in. In essence it involves masking the string (blank) with white color fonts using conditional formating. Needs to be done one time only upon the whole sheet or PT or range of cells.

    1. select the whole sheet/ table/ range of cells
    2. select [Conditional Formatting] -> [New Formatting Rule]
    3. in the [New Formatting Rule] dialog window

    select [Format only cells that contain]

    under the [Format only cells with:] select [Cell Value] from the drop-down list - actually it is selected by default ; select [equal to] from the drop-down list next; type (blank) in the third box 

    1. then press the [Format] button to set the format
    2. it opens another dialog window about formatting
    3. select [Fonts] tab (it is the default one)
    4. select white color for fonts
    5. OK and OK again and you've done it :)

    In this way, (blank) will not visually annoy you and the same time you don't mess with cells contents.

    Hope to have been of help

    ******@abv.bg

    Was this answer helpful?

    9 people found this answer helpful.
    0 comments
  3. Anonymous
    2012-08-27T07:11:45+00:00

    Instead of replacing (blank) you can actually filtering (blank) using label filter - Does Not Contains (Blank) anytime you refresh the PT will be updated.

    tks,

    harry

    Was this answer helpful?

    8 people found this answer helpful.
    0 comments
  4. Anonymous
    2012-09-18T21:04:53+00:00

    Thank you thank you thank you!  Now that said:

    Is it just me or isn't it too bad that we users have to come up with workarounds because a standard pivot table feature (For empty cells show: ) DOESN'T WORK!

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments