Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can blank cells be omitted from graph?
I have created a graph in Excel 2007 that has 4 data series. However, in
many instances I will only have data entered for 1 or 2 of those series. Currently, the blank series are being plotted as zero values. Is there a way to define the data series so that if they are blank they will be completely omitted from the graph? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can blank cells be omitted from graph?
I use a helper column and use that as the source of the graph.
The helper column contains a formula like: =if(a2="",na(),a2) (and drag down) (I'd hide the column if it's irritating.) DoubleZ wrote: I have created a graph in Excel 2007 that has 4 data series. However, in many instances I will only have data entered for 1 or 2 of those series. Currently, the blank series are being plotted as zero values. Is there a way to define the data series so that if they are blank they will be completely omitted from the graph? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can blank cells be omitted from graph?
Blanks are treated as zeros. Just the way it is. To get around it you need to
have the zeros turned into #N/A. =if(B2 = 0, #N/A, B2) -- HTH... Jim Thomlinson "DoubleZ" wrote: I have created a graph in Excel 2007 that has 4 data series. However, in many instances I will only have data entered for 1 or 2 of those series. Currently, the blank series are being plotted as zero values. Is there a way to define the data series so that if they are blank they will be completely omitted from the graph? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can blank cells be omitted from graph?
If I am not mistaken there is a way to accomplish your request in Excel 2007:
http://office.microsoft.com/he-il/he...485551033.aspx In former versions: After activating (selecting) the chart you can declare NOT to present empty cells as zeros (Tools options chart empty cells with WITH INTERPULATION) Micky "DoubleZ" wrote: I have created a graph in Excel 2007 that has 4 data series. However, in many instances I will only have data entered for 1 or 2 of those series. Currently, the blank series are being plotted as zero values. Is there a way to define the data series so that if they are blank they will be completely omitted from the graph? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can blank cells be omitted from graph?
If you hide the column be sure to uncheck
Tools - Options - Charts | Plot Visible Cells Only (The option to leave blanks for gaps in the data only works for constants. If your series is based on formulas that return blanks then your zeros will be plotted.) If you don't want to hide the column and just format the #N/A to not show you can do that with conditional fomatting with a formula similar to. =isna(B2) -- HTH... Jim Thomlinson "Dave Peterson" wrote: I use a helper column and use that as the source of the graph. The helper column contains a formula like: =if(a2="",na(),a2) (and drag down) (I'd hide the column if it's irritating.) DoubleZ wrote: I have created a graph in Excel 2007 that has 4 data series. However, in many instances I will only have data entered for 1 or 2 of those series. Currently, the blank series are being plotted as zero values. Is there a way to define the data series so that if they are blank they will be completely omitted from the graph? -- Dave Peterson . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can blank cells be omitted from graph?
That does not work if the data series value is the result of a formula that
returns a blank. It does work for constants by selecting Leave Gaps (which is the default selection). Unless you have some trick to make it work? ... I hope... -- HTH... Jim Thomlinson "מיכאל (מיקי) אבידן" wrote: If I am not mistaken there is a way to accomplish your request in Excel 2007: http://office.microsoft.com/he-il/he...485551033.aspx In former versions: After activating (selecting) the chart you can declare NOT to present empty cells as zeros (Tools options chart empty cells with WITH INTERPULATION) Micky "DoubleZ" wrote: I have created a graph in Excel 2007 that has 4 data series. However, in many instances I will only have data entered for 1 or 2 of those series. Currently, the blank series are being plotted as zero values. Is there a way to define the data series so that if they are blank they will be completely omitted from the graph? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can blank cells be omitted from graph?
May I quote a part of the question:
"... I will only have data entered for 1 or 2 of those series. Currently, the blank series are being plotted as zero values." No one - including DoubleZ mentioned anything about values that are a result of formulas. I'm pretty aware of Excels limitations as well of its advantages as far as charts are concerned. *** Some of my replies do not match the exact question mainly because English is not my mothers tongue - but I try my best... Micky "Jim Thomlinson" wrote: That does not work if the data series value is the result of a formula that returns a blank. It does work for constants by selecting Leave Gaps (which is the default selection). Unless you have some trick to make it work? ... I hope... -- HTH... Jim Thomlinson "מיכאל (מיקי) אבידן" wrote: If I am not mistaken there is a way to accomplish your request in Excel 2007: http://office.microsoft.com/he-il/he...485551033.aspx In former versions: After activating (selecting) the chart you can declare NOT to present empty cells as zeros (Tools options chart empty cells with WITH INTERPULATION) Micky "DoubleZ" wrote: I have created a graph in Excel 2007 that has 4 data series. However, in many instances I will only have data entered for 1 or 2 of those series. Currently, the blank series are being plotted as zero values. Is there a way to define the data series so that if they are blank they will be completely omitted from the graph? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can blank cells be omitted from graph?
Thanks Jim, Dave, and Micky.
I should have specified that all of the blank cells do have formulas in them. So, if I return #N/A it fixes the issue of graphing the data, but it still shows the data series title and marker type of the series with blank cells. Is there a way to have this removed automatically if the series is blank? Thank you again for your help. "Jim Thomlinson" wrote: Blanks are treated as zeros. Just the way it is. To get around it you need to have the zeros turned into #N/A. =if(B2 = 0, #N/A, B2) -- HTH... Jim Thomlinson "DoubleZ" wrote: I have created a graph in Excel 2007 that has 4 data series. However, in many instances I will only have data entered for 1 or 2 of those series. Currently, the blank series are being plotted as zero values. Is there a way to define the data series so that if they are blank they will be completely omitted from the graph? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
exclude blank cells in a line graph | Charts and Charting in Excel | |||
Blank Graph After Hiding Cells | Excel Discussion (Misc queries) | |||
Blank cells in graph | Charts and Charting in Excel | |||
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 |