You might call it perfectionism, but I hate when my reports don’t look their best. One of the most irritating elements is the “(blank)” text you may have on your Excel pivot tables.
Jeremy_Cottino_Blank_values_figure_1

I’ve tried some pivot table options to eliminate that word, “blank,” but nothing seems to work properly.

Jeremy_Cottino_Blank_values_figure_2

So I’ve come up with another way to get rid of those blank values in my tables. It requires playing with conditional formatting.

 

  1. Select the cells you want to remove that show (blank) text. It could be a single cell, a column, a row, a full sheet or a pivot table.
  1. On the Home tab, go on Conditional Formatting, and click on New rule…

Jeremy_Cottino_Blank_values_figure_3

  1. Select Format only cells that contain.
  1. Set that rule type to Cell Value | equal to | and input (blank) text.

Jeremy_Cottino_Blank_values_figure_4

  1. Click on Format…
  1. On the Number tab, select Custom category, then type three semi-colons: ;;;

Jeremy_Cottino_Blank_values_figure_5

I must admit, it’s a kind of magic.

Jeremy_Cottino_Blank_values_figure_6

A version of this article first appeared on Jeremy Cottino’s blog.

Image Source

 


Related Content

Webinars (watch for free now!):
Project and Excel Integration – the application dream team!
Get rid of your last separate Excel sheets: Use your MS project schedule for project financials

Articles:
Microsoft® Excel Keyboard Shortcuts
A Free Project Pipeline Tracker for Excel