Thread: Do not show blank cells as 0 in chart

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?

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

koday@processtrends.com



"Sheila Innes" <SheilaInnes@discussions.microsoft.com> wrote in message
news:7AA1742B-F256-4FF1-B5C4-103DB453450B@microsoft.com...
>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?

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

"Kelly O'Day" wrote:

> 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
>
> koday@processtrends.com
>
>
>
> "Sheila Innes" <SheilaInnes@discussions.microsoft.com> wrote in message
> news:7AA1742B-F256-4FF1-B5C4-103DB453450B@microsoft.com...
> >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?
>
>
>

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" <SheilaInnes@discussions.microsoft.com> wrote in message
news:073951E4-3802-4D77-A331-0F9755818B47@microsoft.com...
> Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark
> it.
> Any ideas?
>
> "Kelly O'Day" wrote:
>
>> 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
>>
>> koday@processtrends.com
>>
>>
>>
>> "Sheila Innes" <SheilaInnes@discussions.microsoft.com> wrote in message
>> news:7AA1742B-F256-4FF1-B5C4-103DB453450B@microsoft.com...
>> >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?
>>
>>
>>

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" wrote:

> 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" <SheilaInnes@discussions.microsoft.com> wrote in message
> news:073951E4-3802-4D77-A331-0F9755818B47@microsoft.com...
> > Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark
> > it.
> > Any ideas?
> >
> > "Kelly O'Day" wrote:
> >
> >> 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
> >>
> >> koday@processtrends.com
> >>
> >>
> >>
> >> "Sheila Innes" <SheilaInnes@discussions.microsoft.com> wrote in message
> >> news:7AA1742B-F256-4FF1-B5C4-103DB453450B@microsoft.com...
> >> >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?
> >>
> >>
> >>
>
>
>

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" <SheilaInnes@discussions.microsoft.com> wrote in message
news:7BA5C670-2F1A-465B-834B-A8D7ABD736E3@microsoft.com...
> 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" wrote:
>
>> 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" <SheilaInnes@discussions.microsoft.com> wrote in message
>> news:073951E4-3802-4D77-A331-0F9755818B47@microsoft.com...
>> > Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark
>> > it.
>> > Any ideas?
>> >
>> > "Kelly O'Day" wrote:
>> >
>> >> 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
>> >>
>> >> koday@processtrends.com
>> >>
>> >>
>> >>
>> >> "Sheila Innes" <SheilaInnes@discussions.microsoft.com> wrote in
>> >> message
>> >> news:7AA1742B-F256-4FF1-B5C4-103DB453450B@microsoft.com...
>> >> >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?
>> >>
>> >>
>> >>
>>
>>
>>

Jon

No, there are no formulas in these cells.

Sheila

"Jon Peltier" wrote:

> 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" <SheilaInnes@discussions.microsoft.com> wrote in message
> news:7BA5C670-2F1A-465B-834B-A8D7ABD736E3@microsoft.com...
> > 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" wrote:
> >
> >> 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" <SheilaInnes@discussions.microsoft.com> wrote in message
> >> news:073951E4-3802-4D77-A331-0F9755818B47@microsoft.com...
> >> > Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark
> >> > it.
> >> > Any ideas?
> >> >
> >> > "Kelly O'Day" wrote:
> >> >
> >> >> 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
> >> >>
> >> >> koday@processtrends.com
> >> >>
> >> >>
> >> >>
> >> >> "Sheila Innes" <SheilaInnes@discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:7AA1742B-F256-4FF1-B5C4-103DB453450B@microsoft.com...
> >> >> >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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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
_______

"Sheila Innes" <SheilaInnes@discussions.microsoft.com> wrote in message
news:5F05277B-77BB-4267-8B4B-B0AACF94AA84@microsoft.com...
> Jon
>
> No, there are no formulas in these cells.
>
> Sheila
>
> "Jon Peltier" wrote:
>
>> 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" <SheilaInnes@discussions.microsoft.com> wrote in message
>> news:7BA5C670-2F1A-465B-834B-A8D7ABD736E3@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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" <SheilaInnes@discussions.microsoft.com> wrote in
>> >> message
>> >> news:073951E4-3802-4D77-A331-0F9755818B47@microsoft.com...
>> >> > Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't
>> >> > mark
>> >> > it.
>> >> > Any ideas?
>> >> >
>> >> > "Kelly O'Day" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >> koday@processtrends.com
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Sheila Innes" <SheilaInnes@discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:7AA1742B-F256-4FF1-B5C4-103DB453450B@microsoft.com...
>> >> >> >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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>