Surpressing plot of blank cells

I'm creating a line chart plot using Excel 2007.  The data range for plot 
lines  contains some blank cells. These blank cells get plotted as zero 
points.  

How do I change this behavior to plot only points that have values and to 
leave the blank cells unplotted?

I appreciate your help, -John
0
John1063 (943)
10/6/2009 1:13:04 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
715 Views

Similar Articles

[PageSpeed] 40

Hi,

If they are truly blank then check the plot empty cells setting via.
Select Data > Hidden and Empty cells.

If the Cells in fact contain formula then use NA() instead of "". This will 
suppress the data marker but will not break the line.

Alternatively use Autofilter to hide unwanted rows.

Cheers
Andy

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"John" <John@discussions.microsoft.com> wrote in message 
news:49F3936C-DB45-497F-A77D-4F9F779F1CC8@microsoft.com...
> I'm creating a line chart plot using Excel 2007.  The data range for plot
> lines  contains some blank cells. These blank cells get plotted as zero
> points.
>
> How do I change this behavior to plot only points that have values and to
> leave the blank cells unplotted?
>
> I appreciate your help, -John 

0
andy9699 (3616)
10/6/2009 3:02:15 PM
The cells I am plotting are non-empty, they contain the formula 
   =IF('Monthly Input'!I32<>"",'Monthly Input'!I32,"")
that picks up none empty cells from another sheet.  Checking the "Plot empty 
cells" option does not surpress the plotting of these cells as zero points.  

How do I change my formula to cause zero points to not plot?

I appreciate your help, -John

"Andy Pope" wrote:

> Hi,
> 
> If they are truly blank then check the plot empty cells setting via.
> Select Data > Hidden and Empty cells.
> 
> If the Cells in fact contain formula then use NA() instead of "". This will 
> suppress the data marker but will not break the line.
> 
> Alternatively use Autofilter to hide unwanted rows.
> 
> Cheers
> Andy
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> "John" <John@discussions.microsoft.com> wrote in message 
> news:49F3936C-DB45-497F-A77D-4F9F779F1CC8@microsoft.com...
> > I'm creating a line chart plot using Excel 2007.  The data range for plot
> > lines  contains some blank cells. These blank cells get plotted as zero
> > points.
> >
> > How do I change this behavior to plot only points that have values and to
> > leave the blank cells unplotted?
> >
> > I appreciate your help, -John 
> 
> 
0
John1063 (943)
10/7/2009 8:43:02 PM
=IF('Monthly Input'!I32<>"",'Monthly Input'!I32,NA())
--
David Biddulph

"John" <John@discussions.microsoft.com> wrote in message 
news:EB3D147D-B608-4C9A-9F1A-9687C96A03FA@microsoft.com...
> The cells I am plotting are non-empty, they contain the formula
>   =IF('Monthly Input'!I32<>"",'Monthly Input'!I32,"")
> that picks up none empty cells from another sheet.  Checking the "Plot 
> empty
> cells" option does not surpress the plotting of these cells as zero 
> points.
>
> How do I change my formula to cause zero points to not plot?
>
> I appreciate your help, -John
>
> "Andy Pope" wrote:
>
>> Hi,
>>
>> If they are truly blank then check the plot empty cells setting via.
>> Select Data > Hidden and Empty cells.
>>
>> If the Cells in fact contain formula then use NA() instead of "". This 
>> will
>> suppress the data marker but will not break the line.
>>
>> Alternatively use Autofilter to hide unwanted rows.
>>
>> Cheers
>> Andy
>>
>> -- 
>>
>> Andy Pope, Microsoft MVP - Excel
>> http://www.andypope.info
>> "John" <John@discussions.microsoft.com> wrote in message
>> news:49F3936C-DB45-497F-A77D-4F9F779F1CC8@microsoft.com...
>> > I'm creating a line chart plot using Excel 2007.  The data range for 
>> > plot
>> > lines  contains some blank cells. These blank cells get plotted as zero
>> > points.
>> >
>> > How do I change this behavior to plot only points that have values and 
>> > to
>> > leave the blank cells unplotted?
>> >
>> > I appreciate your help, -John
>>
>> 


0
David
10/7/2009 10:15:07 PM
Funny thing.  I can get this to work if I'm using a straight IF() 
function...but using a check on the value returned by VLOOKUP, while the cell 
still shows "#N/A" as a result of the source cell being blank, the chart 
seems to recognize it differently.  (I have two spreadsheets going with the 
same data, one of which only needs a single value updated to slide the date 
forward, whereas the other requires all formulae in all columns to be 
updated...naturally, I want to use the VLOOKUP version)

So...using if(source="",NA(),source) shows "#N/A" and plots no point.
Using IF(VLOOKUP(source)=0,NA(),VLOOKUP(source)) shows "#N/A" and plots 0:00.



"David Biddulph" wrote:

> =IF('Monthly Input'!I32<>"",'Monthly Input'!I32,NA())
> --
> David Biddulph
> 
> "John" <John@discussions.microsoft.com> wrote in message 
> news:EB3D147D-B608-4C9A-9F1A-9687C96A03FA@microsoft.com...
> > The cells I am plotting are non-empty, they contain the formula
> >   =IF('Monthly Input'!I32<>"",'Monthly Input'!I32,"")
> > that picks up none empty cells from another sheet.  Checking the "Plot 
> > empty
> > cells" option does not surpress the plotting of these cells as zero 
> > points.
> >
> > How do I change my formula to cause zero points to not plot?
> >
> > I appreciate your help, -John
> >
> > "Andy Pope" wrote:
> >
> >> Hi,
> >>
> >> If they are truly blank then check the plot empty cells setting via.
> >> Select Data > Hidden and Empty cells.
> >>
> >> If the Cells in fact contain formula then use NA() instead of "". This 
> >> will
> >> suppress the data marker but will not break the line.
> >>
> >> Alternatively use Autofilter to hide unwanted rows.
> >>
> >> Cheers
> >> Andy
> >>
> >> -- 
> >>
> >> Andy Pope, Microsoft MVP - Excel
> >> http://www.andypope.info
> >> "John" <John@discussions.microsoft.com> wrote in message
> >> news:49F3936C-DB45-497F-A77D-4F9F779F1CC8@microsoft.com...
> >> > I'm creating a line chart plot using Excel 2007.  The data range for 
> >> > plot
> >> > lines  contains some blank cells. These blank cells get plotted as zero
> >> > points.
> >> >
> >> > How do I change this behavior to plot only points that have values and 
> >> > to
> >> > leave the blank cells unplotted?
> >> >
> >> > I appreciate your help, -John
> >>
> >> 
> 
> 
> 
0
ymgagnon (2)
1/25/2010 5:36:01 PM
Reply:

Similar Artilces:

Non Numbers Cells
Hello, I would like to know if I can create a Macro that will do a search in column A (from A2 to A65536) and every row that has anything else than a number, delete the entire row (not just the cell). Thanks!!! Hi try the following macro: Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If not isnumeric(Cells(row_index, 1).Value) then Rows(row_index).delete End If Next Application.ScreenUpdating = True End Sub >-----Origi...

link to cell, including hyperlink
searching the forums and i can't seem to find any post that solve m problem. basically i have a long colomn of links starting in cell A1, that i a hyperlink to a picture, and the word displayed is "screenshot". I' consentaly inserting new cells and shifting the current ones down. Th hyperlinks shift ok and everything. my problem is, i have another sheet that i've made that i will b saving in HTML format for easy viewing online. BUt i only want it t show the most recent 10 screenshots. When I'm on the other sheet an try ='sheet1'!$A$1 instead of copying...

Making a MsgBox return cell data when search finds different data in an adjacent cell
Thank you for helping me with this in advance. I'm grateful for all the help I've found in these groups. I'm running a macro that is searching a sheet for data. When it finds that data, I need a msgbox to pop up with the data from that column's top cell. I have it searching the sheet and finding the data listed in the search. Because of the possibility of multiple winners, the msgbox will pop up each time someone scores. This macro works fine as it is except I would like to add the winners names to the message boxes. thanks again, JasonK With Worksheets(1).Range(&quo...

Help Adding up multiple calculated cells
I have 8 columns that MAY contain NOTHING or may contain text chosen from a lookup name from a data validation pick list. Lic3 won't contain a value UNLESS Lic1 AND Lic2 are populated. I have 8 columns range named Lic1, Lic2, etc to hold a chosen license name. I have 8 other columns used for number counts relating to the lookups I have range named Lic1Cnt, Lic2Cnt, etc which hold a number such as 12. I need to add up the value calculated for each Lic based on looking up the text in a table, getting the price and then multiplying by the Lic? Cnt number. Some records may have only a singl...

Count of cells since first purchase
Hi, Looking for a bit of help with the below. I want to calculate how many weeks have passed since a customer first purchased from me. So for example see below. I want to calculate in cell E2 how many weeks have passed since Account 1 first purchased from me. In cell E3 i want to calculate how many weeks it is since Account 2 first purchased from me. Week 3 April Week 4 April Week 1 May 10 Account 1 2 3 Account 2 1 Account 3 ...

VBA code for copying on a range were cells are blank
Hi everyone, me again!! So, you were all so helpful with my earlier prob, thought i'd keep you going with another prob i'm having. OK, so what I want to do (in VBA if poss), is to search the range B2:E2 and see if they are blank. If yes, then I want to copy the cells above and then carry on searching that range. I can't do it upwards as its likely that there are going to be more than one set of blanks, e,g. A B C D E F 1 Joe Bloggs | 309873 | Smith, John | 08:00 | 12:00 | Back Office 2 | | ...

Formula to strip figures from cells text strings
I'm trying to write a cell formula that strips figures from cells text strings & totals them into one cell. The figures will always have a decimal. For example: Cell A5 has a text string "924 Social 9.78" Cell B5 has a text string "984 163.94 981 7.84" Cell C5 has a text string "920 Kitchen 7.86 Cell D5 has a text string :988 Youth 42.46 Need to arrive a the total amount of 231.88 in cell E5 (9.78+163.94+7.84+7.86+42.46)-do not include the non-decimal figures 924, 984, etc. Any ideas? Thanks for all your help. mikeburg -- mikeburg --------------...

Copy contents of a cell into a blank cell
How do I copy the contents of cell B2 into cell D2 only if D2 is empty Hi It cannot be done with a formula - only with VBA. The following code should achieve what you described Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$D$2" Then If Range("D2") = "" Then Range("D2") = Range("B2").Value End If End Sub To Use Copy Code above Right click on sheet tab>View Code Paste code into white pane that appears Alt+F11 to return to Excel -- Regards Roger Govier "fire9...

Specifying Cells to Count?
Hi everyone, I am having trouble adding in Excel... I have a list of IDs in A:A, and a list of numbers in B:B. For example: 1 0.1 1 0.5 3 0.1 3 0.2 3 0.3 6 0.1 6 0.5 8 0.3 8 0.9 8 0.8 8 0.4 Now, I need to calculate the number of instances of 0.1 corresponding to the value of 1 in A:A, the number of instances of 0.1 corresponding to 3 in A:A, and so on. I've tried FREQUENCY(), COUNTIF(),and a few others... I know the number of instances that 1,2 and any other number shows up in A:A, and I think I can use COUNTIF() -- however, I can't find a way to dynamically specify the number of...

How to keep a cell empty at the same time formula embedded?
Can we have an empty cell but formula embedded in that cell. A cell cannot be both "empty" and contain something...........you can however, put a formula there that will display a blank cell according to the conditions of it's calculations........... =(IF(A1>5,A1,"")......will display a blank cell if A1 is 5 or less Vaya con Dios, Chuck, CABGx3 "vic_dic" <vic_dic@discussions.microsoft.com> wrote in message news:A590D8A6-7E86-49B7-AA9A-12DE213E670C@microsoft.com... > Can we have an empty cell but formula embedded in that cell. What are you...

Help with copying dynamic column selected based on remote cell value and dynamic formula fill
Before posting my nightmare I would like to direct my thanks to all of you who posted answers before. Browsing trough the response I complete maybe 80% of my task in a very neat way. The remaining 20% of the task it seems that will require more than 80% of the time! There are 3 worksheets named S1, S2, and S3. I use a macro to first fill the no value NA() a range sayA1:C700 in S3! And second to open a text file and format data in it (get rid of empty rows via sorting since data in first column is ascending, etc). At the end of this macro there is another worksheet named P1.txt which has 12 co...

Storing VBA Code In a Cell to Use real Time
I want to store multiple values in Column A and VBA Conditional Code in Column B. The Conditional Code is just text and is just stored on the worksheet. When a macro is run and a variable equals the value in column A then the code stored in Column B executes inside the macro. For example, A1 = "ALLEX" and B1 = "If Not Left(route,1) = "K" . So, when the macro runs and checks to see if "ALLEX" is in Column A. If so ,then it will use the associated Condition: If Not Left(route,1) = "K". directly in the Macro. So, if both condition...

When printing a page have a blank space at the bottom
Hi, When I print an A4 page out it has a blank space at the bottom. In print preview it shows as full so can anyone help me get rid of this space please. I am using Publisher 2002. Thanks Could be your printer's margin limitation: Tutorial: Finding your Maximum Print Area http://ed.mvps.org/Static.aspx?=Publisher/horidiag -- Mary Sauer http://msauer.mvps.org/ "malenbrook" <malenbrook@discussions.microsoft.com> wrote in message news:88BA7341-CF85-4E0D-B93D-50AC5DD93D60@microsoft.com... > Hi, > When I print an A4 page out it has a blank space at the bottom. In p...

Formula for selecting a varying cell reference
I'm trying to tie 2 worksheets together. The first is a data entry sheet, set up by month. The second is a sheet that I want to pick up monthly data -- i.e., the most recent month of information entered on the data entry sheet. Is there a formula I can write in the monthly sheet that will look at the data entry sheet and go to the last monthly column that has data and retrieve that? I guess I'm thinking like a flowcharting yes/no process starting in December -- i.e.,: Is December blank? Yes -- go to November; No -- use this data -- etc., etc., etc. Any ideas? ...

How to plot a Pixel in MFC
Hi, Can't find it in the MSDN Thanks Jack Jack wrote: > Hi, > Can't find it in the MSDN > Thanks > Jack Try Using CDC::SetPixel COLORREF SetPixel( int x, int y, COLORREF crColor ); COLORREF SetPixel( POINT point, COLORREF crColor ); try following link http://msdn2.microsoft.com/zh-tw/library/021s1051.aspx ...

Clearing Check Boxes and Cells
Ok, this is either going to kill me or make me stronger.......... I have successfully combine my "clear" functions into one button now (clearing text/numbers from cells and clearing checks from boxes)................. NOW....... NOW.......... my problem is that when I protect the sheet........ the "clear" button that worked perfectly gives a "Run-time error'1004', Unable to set the Value property of the CheckBoxes class". If I turn protection back off, the button works perfectly. I have to be able to protect the sheet so users can only check boxes ...

NAICS Code
Hello, I have a list of NAICS codes formatted as numbers that I would like to format as hierarchical text. Current List 10 12 101 112 Desired List 10 101 112 12 Does anyone know how to change the cell format so that the "Current List" becomes the "Desired List"? Thank you in advance for your help. It looks like you could preformat that column as text and enter your values as text. Then you could sort the data as text. Some versions of excel will ask if you want to sort values that look like numbers as numbers. You should say no to that prompt. And just changing t...

Selecting Cells
Please help, When starting excel, I click on a cell and get the cross pointer but when I move the mouse to put a formula or data into that cell all cells are selected in the direction of mouse movement. I am unable to de-select the cells and the only thing I can do is close excel from the task bar. I have come across this problem once before But cannot remember how to solve it. Thanks in advance for any help with this issue. Regards Paul Paul You sould only get the cross hair if you move the mouse over the box at the bottom right-hand of the cell. This is used to enter a fill of a s...

Custom patterns for cell shading
Hello, Is there a way to add 'custom patterns' to the cell shading option? I was hoping for a nice 'arrow pattern' for the conditional formatting. (I'm doing a presentation of traffic flow, and for flow north or south [ie if Northbound > Southbound, flow is northbound], I would like to graphically show the flow.) Any assistance or ideas would be greatly appreciated. I'm currently using windings arrows, but a form of pattern / conditional formatting would be awesome. Jools -- World of Warcraft *isn't* work? ...

Repeating Characters in Cells with info.
I am trying to repeat a period to connect every column in a list of marathon records. I want it to read name.............time..............date...............location. how do i write the formula to do this? You could give the cells a custom format of: General*.;-General*.;0*.;@*. Positive;negative;0;text The *. says to repeat the dot enough to fill the cell (to the right). You may not want it on the numbers, though. Stressed Student wrote: > > I am trying to repeat a period to connect every column in a list of marathon > records. I want it to read > name.............tim...

I want to know which cell(s) are the MAX
Hi everyone, I`m relatively new to Excel but I think I`ve mastered the basics enough not to get flamed here. My question is this, I have a bunch of values, that I want to find the biggest out of, that is easily done using the MAX function. Now, I also need excel to tell me the number of the cell in which is the biggest, for example, say we had 5 cash values $3, $7, $9, $10, $3, and those values were numbered 1, 2, 3, 4, 5 in a list, I not only want to know that $10 is the biggest, but also that it is at number 4. Is there any way to do this please? Hi one way: =MATCH(MAX(A1:A99),A1:A99,0) ...

Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...

Why does my mouse not release a range of cells when selected?
When I try to select a range of cells, the mouse will continue to highlight a range of cells and will not release. I end up having to force Excel to close and restart. Any help would be greatly appreciated. It does it with most versions of Excel. Hello Check if the F8 key was not pressed (you would have the "EXT" mention on the Excel Status bar), if yes press F8 again. Apart from that I think this could be a mouse driver problem so to make sure try and update your mouse driver (esp if you have a wheel mouse). Apart from that? HTH Cordially Pascal "Wolfer50" <Wolfer...

How do I lock a formula in a cell in an Excel worksheet?
I created a disbursement worksheet which is simple to use, just involves plugging in numbers and it figures everything out for us. However my coworker keeps erasing her formulas wehn entering info. Is there a way to lock a formula while still allowing numbers to be entered in the cell? Thanks for your help. Kathie Sure, ensure the formulas cells are locked and the other cells unlocked. When you password protect the sheet, make sure the section "Allow all users of this worksheet ti:" to not have Select locked cells checked. HTH ...

include string or text value in cell reference
Hi, does anyone know how/if i can include a cell value within a path in a function/formula so i can update it by simply changing cell value, e.g. SUM($A$1, '[E:\folder\stats June.xls]'!sheet1$A$1) so that the 'June' part can change according to a cell value, say F1. So if I change F1 from "June" to "July" then the SUM formula would change to show ....'\stats July' . many thanks in advance, Nelly Use the INDIRECT() function -- Gary''s Student "nelly" wrote: > Hi, > > does anyone know how/if i can include a ce...