Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Blank cells in graph
I have a a line graph that displays customer behaviour in a number of
categories. The data source for the graph is a table that refreshes every time a different 'customer' is selected from a macro drop-down list above the table, drawing data from another table. All the blank cells from the table are plotted as zero on the line graph, and I can't seem to change it so that the blank cells are not plotted on the line graph at all. I've tried going to ToolsOptionsChart, but the 'Plot empty cells as' options are all greyed out except for the 'Zero' option- which I don't want! Can anyone help...... Thanks |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Blank cells in graph
Whatever formula you're using to generate your cell contents, if it's
currently setting the result to "", get it to set to NA() instead. [If you then also want to use conditional formatting to make the N/A# cells look empty, you can do so.] -- David Biddulph "Kirsty W" wrote in message ... I have a a line graph that displays customer behaviour in a number of categories. The data source for the graph is a table that refreshes every time a different 'customer' is selected from a macro drop-down list above the table, drawing data from another table. All the blank cells from the table are plotted as zero on the line graph, and I can't seem to change it so that the blank cells are not plotted on the line graph at all. I've tried going to ToolsOptionsChart, but the 'Plot empty cells as' options are all greyed out except for the 'Zero' option- which I don't want! Can anyone help...... Thanks |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Blank cells in graph
Hiya
Vlookup is getting the data into the table that feeds the line graph. =VLOOKUP($B$2,ebookings!$A$4:H31,'Overview - by Cust'!H$99,FALSE) Even if I put NA() in the cells that the Vlookup is using to get data, which means NA() is appearing in the table from which the graph feeds, the options to 'Plot Empty Cells' are still greyed out. "David Biddulph" wrote: Whatever formula you're using to generate your cell contents, if it's currently setting the result to "", get it to set to NA() instead. [If you then also want to use conditional formatting to make the N/A# cells look empty, you can do so.] -- David Biddulph "Kirsty W" wrote in message ... I have a a line graph that displays customer behaviour in a number of categories. The data source for the graph is a table that refreshes every time a different 'customer' is selected from a macro drop-down list above the table, drawing data from another table. All the blank cells from the table are plotted as zero on the line graph, and I can't seem to change it so that the blank cells are not plotted on the line graph at all. I've tried going to ToolsOptionsChart, but the 'Plot empty cells as' options are all greyed out except for the 'Zero' option- which I don't want! Can anyone help...... Thanks |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Blank cells in graph
If the cell contains a formula, it's not an empty cell. There's no way for a
formula to make Excel think it's an empty cell. The NA() is a trick that works for line and XY charts, because it suppresses plotting of a point. It is the equivalent of the Interpolate option of the Plot Empty Cells setting, so a line connecting points passes across the gap. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Kirsty W" wrote in message ... Hiya Vlookup is getting the data into the table that feeds the line graph. =VLOOKUP($B$2,ebookings!$A$4:H31,'Overview - by Cust'!H$99,FALSE) Even if I put NA() in the cells that the Vlookup is using to get data, which means NA() is appearing in the table from which the graph feeds, the options to 'Plot Empty Cells' are still greyed out. "David Biddulph" wrote: Whatever formula you're using to generate your cell contents, if it's currently setting the result to "", get it to set to NA() instead. [If you then also want to use conditional formatting to make the N/A# cells look empty, you can do so.] -- David Biddulph "Kirsty W" wrote in message ... I have a a line graph that displays customer behaviour in a number of categories. The data source for the graph is a table that refreshes every time a different 'customer' is selected from a macro drop-down list above the table, drawing data from another table. All the blank cells from the table are plotted as zero on the line graph, and I can't seem to change it so that the blank cells are not plotted on the line graph at all. I've tried going to ToolsOptionsChart, but the 'Plot empty cells as' options are all greyed out except for the 'Zero' option- which I don't want! Can anyone help...... Thanks |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Blank cells in graph
Thanks David and Jon,
I've now got it to work! How should I use the conditional formatting to make the #N/A cells look empty? Thanks, "David Biddulph" wrote: Whatever formula you're using to generate your cell contents, if it's currently setting the result to "", get it to set to NA() instead. [If you then also want to use conditional formatting to make the N/A# cells look empty, you can do so.] -- David Biddulph "Kirsty W" wrote in message ... I have a a line graph that displays customer behaviour in a number of categories. The data source for the graph is a table that refreshes every time a different 'customer' is selected from a macro drop-down list above the table, drawing data from another table. All the blank cells from the table are plotted as zero on the line graph, and I can't seem to change it so that the blank cells are not plotted on the line graph at all. I've tried going to ToolsOptionsChart, but the 'Plot empty cells as' options are all greyed out except for the 'Zero' option- which I don't want! Can anyone help...... Thanks |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Blank cells in graph
Formula is: =ISNA(A1)
-- David Biddulph "Kirsty W" wrote in message ... Thanks David and Jon, I've now got it to work! How should I use the conditional formatting to make the #N/A cells look empty? "David Biddulph" wrote: Whatever formula you're using to generate your cell contents, if it's currently setting the result to "", get it to set to NA() instead. [If you then also want to use conditional formatting to make the N/A# cells look empty, you can do so.] .... |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Blank cells in graph
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
... Formula is: =ISNA(A1) .... and at this point I should have added: make the font colour white (or whatever your cell background is). -- David Biddulph "Kirsty W" wrote in message ... Thanks David and Jon, I've now got it to work! How should I use the conditional formatting to make the #N/A cells look empty? "David Biddulph" wrote: Whatever formula you're using to generate your cell contents, if it's currently setting the result to "", get it to set to NA() instead. [If you then also want to use conditional formatting to make the N/A# cells look empty, you can do so.] ... |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Blank cells in graph
and soon if the data is available,it cannot be seen;-)
-- Regards, Linda "David Biddulph" wrote: "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Formula is: =ISNA(A1) .... and at this point I should have added: make the font colour white (or whatever your cell background is). -- David Biddulph "Kirsty W" wrote in message ... Thanks David and Jon, I've now got it to work! How should I use the conditional formatting to make the #N/A cells look empty? "David Biddulph" wrote: Whatever formula you're using to generate your cell contents, if it's currently setting the result to "", get it to set to NA() instead. [If you then also want to use conditional formatting to make the N/A# cells look empty, you can do so.] ... |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Blank cells in graph
Yes, perhaps (for the benefit of those not familiar with this use of CF) I
should have made it clear that the font colour white advice was meant to go with the CF ISNA() condition, and not to be applied as the basic format for the cell. :-) -- David Biddulph "linda" wrote in message ... and soon if the data is available,it cannot be seen;-) "David Biddulph" wrote: "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Formula is: =ISNA(A1) .... and at this point I should have added: make the font colour white (or whatever your cell background is). "Kirsty W" wrote in message ... Thanks David and Jon, I've now got it to work! How should I use the conditional formatting to make the #N/A cells look empty? "David Biddulph" wrote: Whatever formula you're using to generate your cell contents, if it's currently setting the result to "", get it to set to NA() instead. [If you then also want to use conditional formatting to make the N/A# cells look empty, you can do so.] ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I leave out blank cells in a line graph? | Charts and Charting in Excel | |||
Do not want chart to graph cells with formulas that are "blank" | Charts and Charting in Excel | |||
Blank cells in named range- how to ignore them when making my graph? Help plz! | Excel Discussion (Misc queries) | |||
How can I make the graph omit blank cells in the data set? | Charts and Charting in Excel | |||
REPOST: How can I make the graph omit blank cells in the data set? | Charts and Charting in Excel |