Hide zero values in Excel charts
In Excel, you can easily hide zero values for charts. As a result, your diagram does not rustle towards 0, but simply skips the empty value. In this practical tip, we will show you how.
Excel: Hide zero values in diagrams
If a cell is empty, Excel interprets its value as zero - and displays it with this value in the diagram. To prevent this, provide Excel with an unrepresentable error value instead of the zero.
- For this, the field to be displayed in the diagram must contain the value "#NV". You can either install it manually or embed it in a formula.
- In the example of a simple IF formula, you then write:
- = IF ((Your • Formula) = 0; # N; your • Formula)
- If your intermediate result is zero, the function returns the error value "#NV", otherwise the normal result.
- The disturbing data points then disappear from the diagram at zero values.
Excel diagram: Hide zero values afterwards
If you have already entered a lot of data, there is also a solution.
- To be able to change the values later, you have to insert an auxiliary column.
- There you simply regulate that zero values should be displayed as #NV, all other values normally.
- This works with this formula = IF (original field = 0; #NV; original field). You can see an example in the picture on the right.
- To make your table look nicer, you can simply color the text in your auxiliary column white. Then the values are no longer displayed.
