It’s common to have null values on your Tables or Pivots, like in the example below.
But what if you want these null values to instead appear as blank spaces or empty strings?
There are two ways to accomplish this.
- Conditional formatting rules
The first is to write a formula that converts your original column to Text and replaces null values with an empty string, like this one below:
Coalesce(Text(Round([Sum of Cost], 0)), "")
However, you’ll notice some shortcomings of this method. We’re missing some text formatting that was being applied to the column as a Number and we’re no longer able to apply certain conditional formatting rules.
Our second method will help us work around these limitations, as it allows us to keep the column as a Number.
For the second method, we’ll create a Conditional Formatting rule that displays Null values with text that is the same color as the background
You can access the Conditional Formatting rules for an element by clicking on the Element Format menu (the Paintbrush), then clicking on “Conditional formatting”
You can then …
- create a new rule by clicking “Add rule”,
- “Apply to” the appropriate column with a “Formatting rule” of “Is null”,
- and then finally set the Style to a Fill Color of None and a Text Color of white (or whatever the background color of your workbook is).
And you should be all set! Hopefully this has been helpful!
-Chandler Phelps, Sigma Support