Excel: zero values in charts hide
If a cell is empty, Excel interprets your value as a Null and represents this value in the chart. To prevent this, provide you Excel instead of the Zero of a non-displayable error value.
- For this purpose, the in the diagram to display field must contain the value "#n / a". These you can either install manually, or in a formula embed.
- In the example of a simple IF-formula, then write:
- =IF((your formula)=0;#NV;your•formula)
- If your intermediate result is Zero, the function returns the error value "#n / a", otherwise the normal result.
- In the diagram, the disturbing data disappear, then points with zero values.
Excel: zero values in chart
Excel chart null values to subsequently hide
If you have already entered a lot of data, there is a solution for you.
- The values subsequently change, you need to insert a helper column.
- There rules are simple, the zero values as #n / a will be displayed, all other values normal.
- The works with this formula =IF(original box=0;#NV;original box). An example you see in the picture to the right.
- So your table looks beautiful, you can color the Text in your helper column is simply white. Then the values will not be displayed.
Zero values hide later
In the next practical tip we show you how to an Excel chart as a template save can.
