|
Lines in chart when no data is present
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. |
Lines in chart when no data is present
Hi RW,
I don't believe there is a straight forward way to do this. -- Cheers, Shane Devenshire "RW" wrote: 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. |
Lines in chart when no data is present
Nothing straightforward, but Andy Pope has a technique for this:
http://andypope.info/charts/brokenlines.htm Other alternatives include deleting the cells to form a gap (via macro which can recreate the formula. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "ShaneDevenshire" wrote in message ... Hi RW, I don't believe there is a straight forward way to do this. -- Cheers, Shane Devenshire "RW" wrote: 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. |
Lines in chart when no data is present
According to the following link,
http://office.microsoft.com/en-us/ex...346081033.aspx it states: "By default, Excel does not plot a data point for an empty cell, which causes a break in a data series. " and even shows an example. Why does it not work for my data? "ShaneDevenshire" wrote: Hi RW, I don't believe there is a straight forward way to do this. -- Cheers, Shane Devenshire "RW" wrote: 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. |
Lines in chart when no data is present
Because you don't have empty cells, you have formulas that return "", which
is a short piece of text. Unfortunately there is no function that simulates a blank cell. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "RW" wrote in message ... According to the following link, http://office.microsoft.com/en-us/ex...346081033.aspx it states: "By default, Excel does not plot a data point for an empty cell, which causes a break in a data series. " and even shows an example. Why does it not work for my data? "ShaneDevenshire" wrote: Hi RW, I don't believe there is a straight forward way to do this. -- Cheers, Shane Devenshire "RW" wrote: 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. |
Lines in chart when no data is present
By the way I also have a way to do it but its not pretty. There is also a
code solution which might be simplier but you would have to run it anytime the data changes. -- Thanks, Shane Devenshire "Jon Peltier" wrote: Because you don't have empty cells, you have formulas that return "", which is a short piece of text. Unfortunately there is no function that simulates a blank cell. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "RW" wrote in message ... According to the following link, http://office.microsoft.com/en-us/ex...346081033.aspx it states: "By default, Excel does not plot a data point for an empty cell, which causes a break in a data series. " and even shows an example. Why does it not work for my data? "ShaneDevenshire" wrote: Hi RW, I don't believe there is a straight forward way to do this. -- Cheers, Shane Devenshire "RW" wrote: 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. |
Lines in chart when no data is present
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. |
Lines in chart when no data is present
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. |
Lines in chart when no data is present
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. |
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. |
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. |
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. |
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. |
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. |
| All times are GMT +1. The time now is 11:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com