Do not show blank cells as 0 in chart

[Sheila Innes]

I have a spreadsheet with a two columns, one for expected enrolment numbers
and the first column of actual enrolments. For April we expect 90, so have a
blank cell to the right for actual. This is showing a line from the March
enrolment of 3 down to 0. How do I get the line to stop at 3 until I put a
figure in for April?

 

[Kelly O'Day]

Excel lets you choose how you can plot empty cells.

As zeros, not plotted or interpolated.

To make your choice, select your chart, go to Tools > Options > Charts and
select the Not Plotted option button for empty cells.

....Kelly

(e-mail address removed)

 

[Sheila Innes]

Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark it.
Any ideas?

 

[Jon Peltier]

1. Select the chart before going to Tools - Options - Charts.
2. What kind of chart is it?
3. If it's a line or XY chart, if you have a formula that returns "",
understand that this is not a blank cell, it's a cell with a formula, so
Kelly's solution won't change how the cell plots. Change the "" in the
formula to NA(), which gives you an ugly #N/A in the cell, but gives you the
Interpolate behavior in the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

 

[Sheila Innes]

Hi Jon

I checked the chart and the 'not plotted' option is already selected. The
equation for the chart is =Recruitment!$B$4:$B$9,Recruitment!$O$4:$P$9.
The B selection is the month, the O is the actual and the P is the target.
Any more help much appreciated.

Regards
Sheila

 

[Jon Peltier]

Sheila -

Do you have formulas in Recruitment!$B$4:$B$9 and Recruitment!$O$4:$P$9?
This is where you need to change "" to NA(), if it's a line or XY chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

 

[Sheila Innes]

Jon

No, there are no formulas in these cells.

Sheila

 

[Jon Peltier]

Then make sure the cells are truly blank.

Is it an area chart? An area chart doesn't treat blanks or NA() the same way
that line or XY charts do.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______