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 Tools>Options>Chart, 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
0
KirstyW (3)
3/6/2007 12:22:13 PM
excel.charting 18370 articles. 0 followers. Follow

8 Replies
1010 Views

Similar Articles

[PageSpeed] 33

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" <KirstyW@discussions.microsoft.com> wrote in message 
news:BAB34DAD-3981-43AE-A17D-D3B74859C412@microsoft.com...
>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 Tools>Options>Chart, 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 


0
David
3/6/2007 12:51:56 PM
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" <KirstyW@discussions.microsoft.com> wrote in message 
> news:BAB34DAD-3981-43AE-A17D-D3B74859C412@microsoft.com...
> >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 Tools>Options>Chart, 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 
> 
> 
> 
0
KirstyW (3)
3/6/2007 1:34:33 PM
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" <KirstyW@discussions.microsoft.com> wrote in message 
news:B96CB862-EEDC-4B2E-A8D4-E0379D345429@microsoft.com...
> 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" <KirstyW@discussions.microsoft.com> wrote in message
>> news:BAB34DAD-3981-43AE-A17D-D3B74859C412@microsoft.com...
>> >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 Tools>Options>Chart, 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
>>
>>
>> 


0
jonxlmvpNO (4558)
3/6/2007 3:25:17 PM
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" <KirstyW@discussions.microsoft.com> wrote in message 
> news:BAB34DAD-3981-43AE-A17D-D3B74859C412@microsoft.com...
> >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 Tools>Options>Chart, 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 
> 
> 
> 
0
KirstyW (3)
3/6/2007 4:28:02 PM
Formula is: =ISNA(A1)
-- 
David Biddulph

"Kirsty W" <KirstyW@discussions.microsoft.com> wrote in message 
news:DFC6316C-56BB-4276-95E5-3FE37CE7F0EB@microsoft.com...
> 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.]
.... 


0
David
3/6/2007 4:51:48 PM
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message 
news:45ed98fc$1_1@glkas0286.greenlnk.net...
> 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" <KirstyW@discussions.microsoft.com> wrote in message 
> news:DFC6316C-56BB-4276-95E5-3FE37CE7F0EB@microsoft.com...
>> 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.]
> ...
> 


0
David
3/6/2007 5:27:45 PM
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 
> news:45ed98fc$1_1@glkas0286.greenlnk.net...
> > 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" <KirstyW@discussions.microsoft.com> wrote in message 
> > news:DFC6316C-56BB-4276-95E5-3FE37CE7F0EB@microsoft.com...
> >> 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.]
> > ...
> > 
> 
> 
> 
0
Linda (109)
8/17/2007 12:43:46 AM
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" <linda@discussions.microsoft.com> wrote in message 
news:05125BEA-6FC3-409D-B8C6-034B426FEBE9@microsoft.com...
> and soon if the data is available,it cannot be seen;-)

> "David Biddulph" wrote:
>
>> "David Biddulph" <groups [at] biddulph.org.uk> wrote in message
>> news:45ed98fc$1_1@glkas0286.greenlnk.net...
>> > 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" <KirstyW@discussions.microsoft.com> wrote in message
>> > news:DFC6316C-56BB-4276-95E5-3FE37CE7F0EB@microsoft.com...
>> >> 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.]
>> > ...


0
David
8/17/2007 9:40:55 AM
Reply:

Similar Artilces:

Delete blank rows Macro
Using MS Excel 2000. Help creating a macro please. I need to search the range A1:A10000 and if the cell is blank I want to delete the entire row. Thanks in advance. -- Richard "Richard" <Richard@discussions.microsoft.com> wrote in message news:DD28F95E-58CF-4961-A3AD-E37FC68AE0F4@microsoft.com... > Using MS Excel 2000. > Help creating a macro please. > > I need to search the range A1:A10000 and if the cell is blank I want to > delete the entire row. > > Thanks in advance. > -- > Richard Something like Sub DeleteRowsIfBlank() Dim lrow as Long...

how to get the address of the ceected cell
Hi Everybody How can I get the address of a cell whenever I click on it thanks Hi yosi, Look at the top left of your screen, to the left of the formula bar. It's called the name box. HTH Martin Here's how in code............ Sub SelectedCellAddress() MsgBox ActiveCell.address End Sub Vaya con Dios, Chuck, CABGx3 "yosi_lb" <yosi_lb@bezeqint.net> wrote in message news:45bb36fe$1@news.bezeqint.net... > Hi Everybody > > How can I get the address of a cell whenever I click on it > > thanks > > 'This is worksheet event code, which m...

Extracting Text from a Cell
I have a column in excell that has 1-4 names in each cell. I would like to extract the names into 4 seperate cells (column). The names are seperated by a comma and a soft return. I'd appreciate any help. Look at Data>Text To Columns -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tony" <anonymous@discussions.microsoft.com> wrote in message news:2031101c4588f$bfe1f240$a001280a@phx.gbl... > I have a column in excell that has 1-4 names in each cell. > I would like to extra...

Report = Blank?
Hello, I have a report that I got from this forum, and when I use it, everything is fine. The problem I am currently having though is if say I try and generate the report using RegisterID & Date then I get a blank report...if I try one or the other, then I am fine...I pretty much tried every angle with RegisterID and with Date, and nothing...any advise would be great, as I have seen this happen with other reports as well, where the final outcome is a blank report. Thank You, Vince "When I run into problems, we more often than not fix them ourselves. Can't put a price on ...

Merging Cells with Information
I have a list of foods in column B and their corresponding measured amount in column A. I would like to have this information available in one cell so I can use a dropdown list. Is there a way to do so? EXAMPLE: A B 12 fl. Oz BEER 4 fl. oz. White Wine I would like the above to read: A Beer 12 fl. Oz. White Wine 4 fl. oz Thanks! Hi Mark, In C1 enter this formula: =B1&" "&A1 Copy down as needed. Then you can delete columns A and B. Biff >-----Original Message----- >I have a list of foods in column B and their >...

Deleting Blank Characters in a Cell
I have a list of names with email addresses of attendees of a seminar series that I am trying to compile into a list that can be copied and pasted into an email (using BCC as the methodology for emailing). The challenge that I have is that when these files were exported from the database to excel (not sure how), all of the cells contain exactly 100 characters even though the email address may only contain 13-20 characters. Is there a way to "trim" the blank characters after the last character that is used by a letter instead of blank character field? Thanks in advance! PZan --...

Blank .msg attachments
Here's an odd one, When an email has been forwarded to, or replyed to and is using the 'attach as attachment' option (as opposed to embed text), the message is delivered with attachment, but the attachment appears blank. It is only .msg attachments that have this problem, everything else gets thru fine (.doc, ..xls, .exe etc.) The attachment is 64bytes long, and has nothing in it. Exchange 2003, Windows 2003 and Outlook 2003 being used. Any ideas anyone? -- The man with no shoes. MCSE (W2K3, W2K), MCSA (W2K3, W2K), MCSE+I (NT4), CCNA + others.... All replies to group pleas...

Blank Cells #4
Hello, I am trying to do the following: 1 - In Cell A4 display the SUM of A1 + A2 + A3 if at least one of these cells is not empty. 2 - In Cell A6 display A5-A4 if both cells are not empty. How can I do this? Thank You, Miguel = Sum(A1:A3) ...and... =IF(AND(LEN(A5),LEN(A4)),A5-A4,"") -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) "Shapper" <mdmoura@gmail.com> wrote in message news:25110584.2678.1325119728434.JavaMail.geo-discussion-forums@yqnd19... > Hello, > > I am trying to do ...

conditional formatting:highlight row based on blank or non-blank c
Does anyone know if, and how, is possible to use Conditional Formatting feature to automatically highlight the whole row if a specific cell in that row is non-blank (or blank)? Thank you! Hi, Yes, first select your row then in your conditional formatting select "formula is" from the drop down menu and type: =ISBLANK($A1)=FALSE or =ISBLANK($A1)=TRUE depending on weather you want the condition to apply when your cell is blank or non blank. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum....

Counting With Blank Rows
Hi, If I have a column of numbers like this: 4 5 6 3 7 8 9 3 2 7 4 1 4 How do I count the number of items between the blanks so it will look like I have it in the example. The number to the right and bottom is the count. So, for the numbers 4,5 6 the count is three. For 7,8 9 the count is three and for the numbers 2,7,4,1 the count is four. Is there a way to do this withoug using a macro? Thanks. Hi, You need to use the "count" function. If the rows in your example are 1-12 and the column is A, for the first group you'd type in (in column B) "=count(a1:a...

Counting cells depending upon a cell value.
Hi there, I've a table with the cell range a1:AE28. For the odd no. rows of the table, starting with the first row the possible values are "DG", "YL", "DR" and "DB". For the even no. rows of the table, starting with the second row the possible values are "G", "R" and "B". I need a formulae to calculate the count of cells having a value as "DG", for all the cells having values as "G". Thanks for your help.... Rashi p.s. here is a sample of the table, to give an idea First row--- DB DG DG DG DG YL D...

Charting blanks as spaces
I know you can chart blank cells as spaces by clicking it in the options...this is a little different. The cell it is charting as an if function in it to display a blank cell. example: =if(D3="","",D5-D3) So when D3 is blank it should display in cell D6 a blank. But when it charts it, it charts it as a zero. I want it to chart it as a gap. I tried =if(D3="",NA(),D5-D3), This way i get a "#N/A" error in my cell, which i can hide through conditional formating. but when i click display>data labels>values in chart options it displays "#...

disable/lock a cell based on the contents of another cell
using a formula is it possible to disable/lock another cell? In my worksheet cell A1 can only contain "H" or "E" If A1 = H, I do not want anybody to be able to populate B1. Hi, 1. You could set up Data, Validation on B1 such that =A1<>"H" would be your Custom formula. 2. The second alternative is to add a Worksheet_Change macro that would store the value of B1 and if that cell were changed when A1 = H the macro would return the original value to B1, replacing the users entry. Of course neither of these are fool proof since the user could change A1 to...

Lookup cell for documents in a folder
I am trying to create an overview of the time spent on projects. People hand in weekly time-charts in excel format where the projects are referenced by a standard number. So in effect I have a lot of separate excel files from which I would like to create a summary, which also updates when a new file is added to the folder. I have no idea if this is possible and if it is, how one should accomplish it. Can anyone help? There's a lot of information that would be needed for a complete solution, but I'll outline generally what would be needed. This would have to be done wi...

Blank, yet not blank
Hi, I copied some info from a database and pasted it to Excel where I wanted to manipulate the data. I notice that some of the cells, though appearing to be blank (and not showing 0 either), still responded to a simple formula like IF(OR(A1="",A1=0),1,0) giving the answer as 1. Can anyone tell me what's happening here, as it does mean I need to unneccessarily extend the formulas to exclude these cells. Rob "Rob" wrote: > Hi, > > I copied some info from a database and pasted it to Excel where I wanted to > manipulate the data. I notice that so...

Text not consistantly appearing in adjacent cells
I have a multi page spread sheet where there are a few inconsistancies: (1) My pages have a header that spans several columns once it pulls in an organization's name. On most of the pages the header appears fine even though it displays over several columns. On a few of the pages the header only appears partially -- displaying only the characters that will fit in the column width of the cell containing the header. As all of the adjacent column cells the header appears next to are blank, I just merged all the cells to eliminate this problem, however on two of my sheets I made v...

BLANK FORM / QUERY
My database is pretty basic with tables, forms, queries etc. I'd like to display a form that allows me to type in a figure in the first field and the rest of the fields will populate automatically. I'm not sure how to create a query where the field awaits input. Thanks in advance. -- TomMurray In the query's criteria box, use square brackets: [Enter a value] or from a form: [Forms]![FormName]![txBoxName] in the first case, you will be prompted. In the second, you will only be prompted if the form isn't open. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http:/...

Lock cells but allow data entry ONLY!
Hi - Can I protect my Excel spreadsheet to allow data entry and nothing else (i.e. formatting, changing column widths, changing formulas, etc. Thanks Melissa * Highlight all the cell(s) you want to be able to enter data into. * Right-click and choose *Format Cells.* * On the *Protection* tab take the tick out of the *Locked* box. * Highlight all the cells which contain the formulas you want to remai unchanged. * Right-click and choose *Format Cells.* * On the *Protection* tab make sure there is a tick in the *Locked box. * Go to * Tools > Protection > Protect Sheet* * Tick *Select ...

Blank Records in Catalog Merge
I am printing a directory with the following information: Name Child's Name Phone Number Address The records that do not have an entry under "Child's Name" are coming out like this: Name Phone Number Address (...with a blank space where the "Child's Name" field would be.) Even worse (and more confusing when reading the final document) is when the record has neither a "Child's Name" entry nor a "Phone Number" entry, and you get this: Name Address Basically, I want it to ignore the line if there is no data in the field. Accord...

Blank Document no longer blank
Every time Word opens, words and a picture (that were used at one time) appear as the blank document. I cannot find any place/button/option that will remove the words or picture from the default new document HELP please! See http://word.mvps.org/faqs/apperrors/blankdocnotblank.htm for tips on how to remove content from the Normal.dot(x) Hope this helps DeanH "AnaMarie" wrote: > Every time Word opens, words and a picture (that were used at one time) > appear as the blank document. I cannot find any place/button/option that will > remove the words or p...

Page shows up in print preview, but prints blank...
I'm trying to print a booklet with graphics on both the front and back covers. The back cover graphics and text appear fine in print preview, but when I send it to the printer, it prints blank. The front cover is fine. Help! Thanks in advance to anyone who can help me. How to troubleshoot problems that you may experience when you try to print to a local printer by using Office programs in Windows XP http://support.microsoft.com/kb/870622/en-us Have you checked the manufacturer's web page for your model printer? There maybe an updated driver. -- Mary Sauer MSFT MVP http://offi...

ADDING SUM TOTAL OF MORE THAN 30 CELLS IN A COLUMN TOGETHER
I am not able to add more than 30 cells in a column and need to add at least 70 - what do I do? will this work for you? =sum(a1:a100000) -- Don Guillett SalesAid Software donaldb@281.com "Robin" <Robin@discussions.microsoft.com> wrote in message news:8EBEA34A-10D3-4F58-AE0E-E9FF2EE0F73B@microsoft.com... > I am not able to add more than 30 cells in a column and need to add at least > 70 - what do I do? You can add much more than that, if indded you are using non adjacent cells like =SUM(A1,B3,C4 etc) then you can use 30, then another 30 like =SUM((1,3,5,...........

Blank Email
I have a customer with nagging and intermittent Exchange issue. Every so often when they send an email, the body of the email is blank, though there was text when they hit send. The Sent Items folder also shows the email with a blank body. There are no errors on the Exchange Server. It is happening on both the Terminal Server and on a PC, so it looks like it's Exchange Specific. Has anyone seen this? There in nothing on MS, and Google groups asks the question with no answer. Help! -Hal ...

sorting cells based on another column/row
Hi, Here's my problem - I hope you can understand it & help because I' getting rather desperate for an answer: Let's say I've got a column, A (or row 1, it doesn't matter) that contains cells, each with a single word/category that I'm interested i - we can assume that this is my list of category headers/titles. Plus I've also got various other columns, A-D (or rows) next to my column A titles that each contain a list of items, one per cell... Hmm, that sounds confusing. Perhaps it would be clearer if I drew it out (ignore the dashes, they're just ther...

Constructing graphs in excel
How do I insert a break in a line graph in excel? Is it even possible?? break in the Y axis. http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm -------------------------------------------------------------------------------- -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Twnooz" <Twnooz@discussions.microsoft.com> wrote in message news:16EECEAD-022B-404A-8436-EF66DF8D65F9@microsoft.com... > How do I insert a break in a ...