Tip 10: Hiding zero values
One of the things I use conditional formatting to the most is hiding values that are zero.
This can be done in several ways. One of the common ways is to change the Excel settings so all zero values are hidden.
This “solution” however creates new challenges:
- If you send the file to someone else who hasn’t changed their Excel settings, they will see the zero values.
- You can’t decide if you only want to hide the zeros in certain parts of your workbook or sheets. It’s all or nothing.
So let’s do it in a smarter way!
With conditional formatting you can hide “zero values”, send it to someone else and they won’t be able to see the zero values either.
Additionally, you can select specific ranges of your data to be hidden if the values are zero and other places you can choose to see the zero values.
It’s fairly easy:
Select the area where you want to hide the zero values.
I usually choose an entire worksheet, so I just select the entire sheet by clicking a cell and pressing CTRL + A (after that, I press the shortcut once again, since it usually just selects my data first, and not the entire sheet).
Click the ‘Conditional Formatting’ button on the ‘Home’ tab and hover your mouse over ‘Highlight Cells Rules’.
Click ‘Equal To…’.
Now you see a new box with 2 fields.
In the field to the left, you enter 0.
In the field to the right, you click the arrow next to the field and choose ‘Custom Format…’
(Above process is shown in the animation below)
In this box, you go to the ‘Font’ pane and change the font color from automatic/black to white (or whatever your cell color is).
Now all zeros disappear from the sheet, because the font color of all zeros are changed to match the cell color.