A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Charts and Charting in Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Do not show blank cells as 0 in chart



 
 
Thread Tools Display Modes
  #1  
Old March 23rd 06, 04:26 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default 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?
Ads
  #2  
Old March 23rd 06, 04:49 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Do not show blank cells as 0 in chart

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





"Sheila Innes" > wrote in message
...
>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?



  #3  
Old March 23rd 06, 05:01 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Do not show blank cells as 0 in chart

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
>
>
>
>
>
> "Sheila Innes" > wrote in message
> ...
> >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?

>
>
>

  #4  
Old March 23rd 06, 05:29 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Do not show blank cells as 0 in chart

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" > wrote in message
...
> 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
>>
>>
>>
>>
>>
>> "Sheila Innes" > wrote in message
>> ...
>> >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?

>>
>>
>>



  #5  
Old March 24th 06, 09:30 AM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Do not show blank cells as 0 in chart

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" > wrote in message
> ...
> > 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
> >>
> >>
> >>
> >>
> >>
> >> "Sheila Innes" > wrote in message
> >> ...
> >> >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?
> >>
> >>
> >>

>
>
>

  #6  
Old March 24th 06, 06:32 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Do not show blank cells as 0 in chart

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" > wrote in message
...
> 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" > wrote in message
>> ...
>> > 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
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "Sheila Innes" > wrote in
>> >> message
>> >> ...
>> >> >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?
>> >>
>> >>
>> >>

>>
>>
>>



  #7  
Old March 27th 06, 09:11 AM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Do not show blank cells as 0 in chart

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" > wrote in message
> ...
> > 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" > wrote in message
> >> ...
> >> > 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
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Sheila Innes" > wrote in
> >> >> message
> >> >> ...
> >> >> >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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

  #8  
Old March 27th 06, 01:51 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Do not show blank cells as 0 in chart

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" > wrote in message
...
> 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" > wrote in message
>> ...
>> > 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" > wrote in
>> >> message
>> >> ...
>> >> > 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
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Sheila Innes" > wrote in
>> >> >> message
>> >> >> ...
>> >> >> >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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>

>>
>>
>>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 04:57 PM
Chart to display only bars for values that are > 3 & blank cells Neil Goldwasser Excel Worksheet Functions 1 August 4th 05 09:37 PM
Activating a Chart object Hari Prasadh Charts and Charting in Excel 6 August 2nd 05 07:22 PM
Condensing a list/range with blank cells to a new list/range without blanks KR Excel Worksheet Functions 4 July 5th 05 04:23 PM
Blank Cells in Pivot Tables Greg Excel Discussion (Misc queries) 1 March 16th 05 09:23 PM


All times are GMT +1. The time now is 09:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.