Excel Graph - Don't Show Zeroes

[Hakala]

Hi! I have an Excel graph and the line plummets to the bottom of the graph wherever there is a zero. Is there a way to get Excel to ignore the zeroes in the data without changing the data itself?

Thanks!

Michelle Hakala

http://www.winebird.com/

 

[SkipVought]

Hi,

Zero IS a significant value.

Why not change the MIN SCALE axis value to a value that will show the variation in the chart, that you feel is significant?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Hi, Michelle.

How do you arrive at those zeros, through a formula or are they entered manually?

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[Hakala]

Manually. These are audit scores, and management wants a zero if there was no audit - zero as a real score is massively unlikely as all items are weighted. Perfect is 100, the lowest I've ever seen was 82.50.



Michelle Hakala

http://www.winebird.com/

 

[mintjulep]

Tools | Options on the chart tab, check "Plot visible cells only".

Hide the rows with zeros.

 

[xlhelp]

Hi, Michelle.

Your best bet is to leave the calls blank. As skip pointed out, Zero is indeed a value. Leaving the cell blank makes the chart jump to the next point to be plotted, giving you a hint that the blank spot on the chart is a zero. Any other way of plotting your data would produce misleading results.

BTW, I think your winebird site is amazingly unique. A refreshing pause from some of the mundane stuff out there.


Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[Hakala]

Thanks, xlhelp!

I'm looking at both the suggestions received, but I appreciate your input into why we should keep zeroes. And the site comment!


Michelle Hakala

http://www.winebird.com/

 

[lionelhill]

Can I make another suggestion?

Do you often have multiple missing audits?

If not, then you can very easily create a column that replaces zero values by the average of the value before and after (and retains the correct value if it is not zero)

Then create a third column that contains zero except when the original value is zero. If the original value is zero, it should contain some conveniently large value such as 10.

Now plot your graph using the interpolated data, and add error-bars using the zero-or-ten column.

This gives you a plot without misleading spikes, but marks each missing audit with a short vertical bar.

It is harder to adapt to multiple interpolations where several audits were missed in a row.

Incidentally, I'd recommend using a score such as -1 as a marker of a missing audit, rather than zero, just on the off-chance that one day someone is so awful that they really deserve zero. Markers should be values that cannot arise in any other way.