Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 26th 07, 05:06 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,510
Default Lines in chart when no data is present

I'm not sure if I am on the right track with the problem but if I am, it
appears to me that you only have to select the chart, select Tools, Options,
Chart Tab and set the Plot Empty Cells option.

"Jon Peltier" wrote:

This is vaguely familiar (everything before yesterday is only vaguely
familiar).

I just tested Excel 2003 SP2, and the line can be formatted as no line
without a problem. I recall that the issue was that instead of making the
line segment vanish, what Excel did was draw the line from the previous
point to the subsequent point, so the point in the middle was still there
but not connected to either adjacent point. I wonder if that was how Excel
2003 came out, and they fixed it (i.e., changed it back) in an SP.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
Hi Jon,

I just retested versions of XL from 2000 to 2007: created a Line chart and
then tried to format a single point to 'no line'. As of today, in 2002
(SP2?) it still shows the line. I am reasonably sure that was the same
behavior with 2003 when it first came out but today the line disappeared
for
the single point. So, it was probably fixed in some 2003 SP.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar -

In which version of Excel did Microsoft make the change?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
That's the way MS designed the charting capability. If you are
plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

It used to work with Line charts until MS made a change that messed up
how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have found the trick to prevent 0's from showing in a chart when the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to
the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)"
is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.








  #12   Report Post  
Old January 26th 07, 06:55 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default Lines in chart when no data is present

Hi,

Well I test 2000 and it worked, but 2002 SP3 did not.

--
Thanks,
Shane Devenshire


"OssieMac" wrote:

I'm not sure if I am on the right track with the problem but if I am, it
appears to me that you only have to select the chart, select Tools, Options,
Chart Tab and set the Plot Empty Cells option.

"Jon Peltier" wrote:

This is vaguely familiar (everything before yesterday is only vaguely
familiar).

I just tested Excel 2003 SP2, and the line can be formatted as no line
without a problem. I recall that the issue was that instead of making the
line segment vanish, what Excel did was draw the line from the previous
point to the subsequent point, so the point in the middle was still there
but not connected to either adjacent point. I wonder if that was how Excel
2003 came out, and they fixed it (i.e., changed it back) in an SP.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
Hi Jon,

I just retested versions of XL from 2000 to 2007: created a Line chart and
then tried to format a single point to 'no line'. As of today, in 2002
(SP2?) it still shows the line. I am reasonably sure that was the same
behavior with 2003 when it first came out but today the line disappeared
for
the single point. So, it was probably fixed in some 2003 SP.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar -

In which version of Excel did Microsoft make the change?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
That's the way MS designed the charting capability. If you are
plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

It used to work with Line charts until MS made a change that messed up
how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have found the trick to prevent 0's from showing in a chart when the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point to
the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)"
is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.







  #13   Report Post  
Old January 26th 07, 02:32 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Lines in chart when no data is present

That only works with real blank cells, not cells in which a formula returns
"" or any other value.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"OssieMac" wrote in message
...
I'm not sure if I am on the right track with the problem but if I am, it
appears to me that you only have to select the chart, select Tools,
Options,
Chart Tab and set the Plot Empty Cells option.

"Jon Peltier" wrote:

This is vaguely familiar (everything before yesterday is only vaguely
familiar).

I just tested Excel 2003 SP2, and the line can be formatted as no line
without a problem. I recall that the issue was that instead of making the
line segment vanish, what Excel did was draw the line from the previous
point to the subsequent point, so the point in the middle was still there
but not connected to either adjacent point. I wonder if that was how
Excel
2003 came out, and they fixed it (i.e., changed it back) in an SP.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
Hi Jon,

I just retested versions of XL from 2000 to 2007: created a Line chart
and
then tried to format a single point to 'no line'. As of today, in 2002
(SP2?) it still shows the line. I am reasonably sure that was the same
behavior with 2003 when it first came out but today the line
disappeared
for
the single point. So, it was probably fixed in some 2003 SP.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar -

In which version of Excel did Microsoft make the change?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in
message
om...
That's the way MS designed the charting capability. If you are
plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

It used to work with Line charts until MS made a change that messed
up
how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have found the trick to prevent 0's from showing in a chart when
the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point
to
the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave
gaps)"
is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.









  #14   Report Post  
Old January 26th 07, 06:21 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default Lines in chart when no data is present

I should clarify, I test the idea of selecting the line segment that is
interpolated across the missing point and changed its color to None and it
worked in my copy of 2000 but not in 2002. These cells contained =NA(), not
a blank.

An alternate way to do this is to set the line color to match the background
color rather than to set it to None. With this approach there are issues,
but they can all be solved.

--
Thanks,
Shane Devenshire


"Jon Peltier" wrote:

That only works with real blank cells, not cells in which a formula returns
"" or any other value.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"OssieMac" wrote in message
...
I'm not sure if I am on the right track with the problem but if I am, it
appears to me that you only have to select the chart, select Tools,
Options,
Chart Tab and set the Plot Empty Cells option.

"Jon Peltier" wrote:

This is vaguely familiar (everything before yesterday is only vaguely
familiar).

I just tested Excel 2003 SP2, and the line can be formatted as no line
without a problem. I recall that the issue was that instead of making the
line segment vanish, what Excel did was draw the line from the previous
point to the subsequent point, so the point in the middle was still there
but not connected to either adjacent point. I wonder if that was how
Excel
2003 came out, and they fixed it (i.e., changed it back) in an SP.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
om...
Hi Jon,

I just retested versions of XL from 2000 to 2007: created a Line chart
and
then tried to format a single point to 'no line'. As of today, in 2002
(SP2?) it still shows the line. I am reasonably sure that was the same
behavior with 2003 when it first came out but today the line
disappeared
for
the single point. So, it was probably fixed in some 2003 SP.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar -

In which version of Excel did Microsoft make the change?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in
message
om...
That's the way MS designed the charting capability. If you are
plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

It used to work with Line charts until MS made a change that messed
up
how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have found the trick to prevent 0's from showing in a chart when
the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data point
to
the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave
gaps)"
is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.










  #15   Report Post  
Old January 27th 07, 02:58 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Lines in chart when no data is present

I know at some point this functionality was broken. However, it's fixed
again by Excel 2003 SP2; you can change a line segment to None, whether it
connects two regular points, spans a blank, or spans NA().

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"ShaneDevenshire" wrote in
message ...
I should clarify, I test the idea of selecting the line segment that is
interpolated across the missing point and changed its color to None and it
worked in my copy of 2000 but not in 2002. These cells contained =NA(),
not
a blank.

An alternate way to do this is to set the line color to match the
background
color rather than to set it to None. With this approach there are issues,
but they can all be solved.

--
Thanks,
Shane Devenshire


"Jon Peltier" wrote:

That only works with real blank cells, not cells in which a formula
returns
"" or any other value.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"OssieMac" wrote in message
...
I'm not sure if I am on the right track with the problem but if I am,
it
appears to me that you only have to select the chart, select Tools,
Options,
Chart Tab and set the Plot Empty Cells option.

"Jon Peltier" wrote:

This is vaguely familiar (everything before yesterday is only vaguely
familiar).

I just tested Excel 2003 SP2, and the line can be formatted as no line
without a problem. I recall that the issue was that instead of making
the
line segment vanish, what Excel did was draw the line from the
previous
point to the subsequent point, so the point in the middle was still
there
but not connected to either adjacent point. I wonder if that was how
Excel
2003 came out, and they fixed it (i.e., changed it back) in an SP.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in
message
om...
Hi Jon,

I just retested versions of XL from 2000 to 2007: created a Line
chart
and
then tried to format a single point to 'no line'. As of today, in
2002
(SP2?) it still shows the line. I am reasonably sure that was the
same
behavior with 2003 when it first came out but today the line
disappeared
for
the single point. So, it was probably fixed in some 2003 SP.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar -

In which version of Excel did Microsoft make the change?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in
message
om...
That's the way MS designed the charting capability. If you are
plotting a
XY Scatter chart, check out the add-in at
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

It used to work with Line charts until MS made a change that
messed
up
how
Line charts deal with breaks in the line.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have found the trick to prevent 0's from showing in a chart
when
the
data
is blank:

If(H2="",NA(),H2/F2)

However, the chart line extrapolates from the previous data
point
to
the
next datapoint instead of showing a gap. I've looked at
Tools-Options-Chart
and the Active Chart - Plot Empty Cells as "Not Plotted (leave
gaps)"
is
selected.

The data is being charted on a secondary Y axis if this makes a
difference.
Please help. I need to have gaps.














Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create single chart with data from seperate worksheets MATT Charts and Charting in Excel 1 May 10th 06 04:09 AM
Can't get data from a web page to chart in excel. Any solutions? goody444 Charts and Charting in Excel 1 March 1st 06 02:23 PM
Can't get data from a web page to chart in excel. Any solutions? goody444 Excel Discussion (Misc queries) 1 March 1st 06 07:09 AM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
Help making a chart that doesn't graph cells without data? Filtration Guy Excel Discussion (Misc queries) 0 March 22nd 05 10:21 PM


All times are GMT +1. The time now is 10:47 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017