# Thread: need line graph to omit plotting zeros

1. ## need line graph to omit plotting zeros

Hi..using Excel 2010.
I can not figure out how to have excel not plot a zero in my line graphs. I have a data range that goes to the year 2020, and the values for 2012-2020 are blank, but the lines drop to the zero value at 2012.
In the "hidden and empty cells" dialog withing the "select data" box, I checked "gaps".
I also have unchecked on the "show a zero in cells that have zero value".

the referenced cells that the chart is plotting has a simple addition formula. not sure if that would affect anything.

I just want the graph to stop at 2011, the last data point not a zero.

Thanks!
Rick

2. A small number, though not equal to zero will plot near zero (excel only ignores real zeroes). Blanks are not plott, but most Errors and all text (even null strings!) will plot as zeroes. If the cell has a formula, it is not blank. If you make the results of the formula a #NA error [=na()], this will prevent the value from plotting.Steve

3. Hi Steve,
I tried doing all that, understanding that formulas always return a number. I read several solutions and the one that seemed the simplest is to do a two column solution. one column has the formula and the second column simply equals the first column. but that didn't solve my problem. not sure if i'm doing the na correct.

here is the formula in the cells is =SUM('sheet1'!F22,Sheet2!F22)
the cell is formatted Accounting, so it looks like this: \$ 5000.00 if there is something to return and it shows: \$ - if there is no data to add.

I tried to add this when i attempted the two column method. on the second column (which is the range for the chart data), i did this: so if column A1 had the above formula, I had B1: =IF(A1="","#N/A",A1).

how should i do any of this different to get the chart to simply stop charting if the cell is zero?

On a side note...why would excel make this so difficult or unintuitive? it seems like a simple click should do it. It seems that's what the "hidden and empty cell" button was to do, but it doesn't work.

Thanks for your patience with me.
Rick

4. I tried to add this when i attempted the two column method. on the second column (which is the range for the chart data), i did this: so if column A1 had the above formula, I had B1: =IF(A1="","#N/A",A1)
Your formula puts the literal text string "#N/A" into a cell. This is text that looks like the error not the error. You want a formula like:

=IF(A1="",NA(),A1)

The function NA results in a #N/A error.

It seems that's what the "hidden and empty cell" button was to do, but it doesn't work.
Yes it DOES work. As I mentioned in my original response, if a cell has something in it, it is NOT empty. Even the null string ("") in a cell makes the cell NOT empty. So unless you HIDE those cells (by hiding the rows) you can check the "hidden the empty" but since the cells you worry about are not hidden nor are they empty, they are charted (and text is plotted as a zero).

The way around this is to use the #NA error which does not plot.

Steve

5. A SUM formula will never return "", so you want:
=IF(A1=0,NA(),A1)

6. I figured i had that wrong on the "N/A#" part, but i tried that after i had already done what you suggested ( i saw it in another thread) I redid the IF statement the way you described but it didn't work.

Here's what I have:
REMINDER NOTE: I have two columns, the first has the formulas which pulls data from different sheets and adds them. The adjacent column has the IF(A1="",NA(),A1) formula. I'll call the first column the HOST column and the 2nd column the CHART DATA column, since the Chart's data source is the 2nd column.

I set up the data columns to go to the year 2030. However I only put the SUM formula through the year 2020. Years 2021-2030 are blank. This is how it looks:
cell range from year 2005-2011- Has data since it could add up the filled cells and plots correctly on graph

cell range from year 2012-2020- returns the Accounting version of zero "\$ -", on both the HOST column and the CHART DATA column and not the expected "#N/A". This range plots as zero on the x axis...not what I want.

Cell range from year 2021-2030-are blanks in the HOST column (since there are no formulas). The CHART DATA column returns the expected "#N/A". This range is not plotted on the chart (which is what I want, but for years 2012-2020 too)

So I have been able to make the cell range without a formula show "#N/A" which gives me the desired result, but how can i make a cell that has a formula do that?
Rick

7. If you want the formula to ignore zeroes as well you can use:

=IF(or(A1="",A1=0),NA(),A1)

If I am still missing what you are after, could you post a sample file and elaborate on the issue?

Steve

8. Hi Steve,
I attached a version of what I'm trying to do for you to look at and help.

based on what i have explained so far, the data should be self explanatory, but here is the overview:
Column b contains the formula summing data found in sheet 2 & Sheet 3. Column C contains data from Sheet 1.
Column D is equal to column B, but contains the IF clause in order to produce the desired #N/A to get the graph to not plot.
column E does the same thing except for column C

You can see in the results and on the graph that through 2020 (which are the years I entered the formula) the graph is plotting zeros. from 2021-2030 I have no formula entered in B & C and i get the right result:"#N/A" and it does not plot anything from 2021-2030.

So how can i tweak D & E's formula to produce the #N/A.
thanks,
Rick

9. I would change the formula in cell [D26] to:
=IF(B26<1,NA(),B26)
..and change the formula in cell [E26] to:
=IF(C26<1,NA(),C26)

..and then copy these formulas down.

zeddy

10. ## The Following 2 Users Say Thank You to zeddy For This Useful Post:

Potts13 (2013-10-06),r9thomas (2012-01-11)

11. Thanks!!!! that did it!
Rick

12. I have the same problem statement that Rick had. I seem to be having problem implementing the solution that he used.

The potentially unique part of my application.... My chart uses 2 axes. The first axis plots a stacked bar (to show plan values) and the second axis plots a stacked line (to show actual values). I'd like the "actual" lines to not plot for future time periods where there is no data.

I have made sure my formula uses =NA() so that the result of the cell is "empty" and does not plot. I have also adjusted the Hidden/Empty cell option to "Gaps". (I also tried Zero for grins and giggles.) Both axes use the same number format (integers with no decimal places).

Any reason why 2 axes or mixing columns/lines should prevent me from using this solution? anything I am missing?

Thanks!

13. Hi

Can you post a sample file?

zeddy

14. Here's a generic version of the file.

Sample.xlsx

15. Hi

See attached file.
I used dynamic range names for the data series you wanted to plot.
Essentially, this defines the data source range based on current date etc.
See if you can work it out from the attached file.
If not, post back here for more guidance.

zeddy

16. ## The Following 2 Users Say Thank You to zeddy For This Useful Post:

psymann (2014-12-04),thelontx (2013-03-04)

17. ## Omit graph plots

I have prepared a line grah but it shows zero in the last as there is no date for that particular week.

I do not need the zero line as the graph line should be continued without any breaks.

For ex: From June 2012 to 21 nov 2012 i have date then the next data will be on December and the next will be in jan (in between we have weeks, we are calculating weeks) so there is no data after jan to till date.

But my line graph shows zero for that week from jan to march

Pls help us in this regard

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•