Plot empty cells as not plotted (leave gaps)

Under Tools/Options/Chart Menu I have set the "Plot empty 
cells as: Not plotted (leave gaps)as well as Plot visible 
cells only, however the chart still plots null values 
causing the automatic scaling to be impractical 
0
anonymous (74722)
5/18/2004 8:17:48 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
1186 Views

Similar Articles

[PageSpeed] 24

If you're using a formula that returns an empty string for missing 
values, change it to an NA()function. For example:

instead of   =IF(B9="","",B9)
use          =IF(B9="",NA(),B9)

To hide the resulting #N/A errors on the worksheet, you can use 
conditional formatting. There are instructions on my web site:
   http://www.contextures.com/xlCondFormat03.html#Errors


Harry v M wrote:
> Under Tools/Options/Chart Menu I have set the "Plot empty 
> cells as: Not plotted (leave gaps)as well as Plot visible 
> cells only, however the chart still plots null values 
> causing the automatic scaling to be impractical 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/18/2004 10:55:34 AM
NA() is the way to ignore cells in the sense of ignoring that a cell is 
there at all.  If the offending cells are surrounded by actual data and 
you want them to break the line that connects data points, then you want 
to recognize that the cell is both there and empty.  There is currently 
no way to do that in Excel except for the cell to actually be empty 
(i.e. delete the formula).

If this is charting dynamic data, you could write a worksheet change 
event that would delete or restore the formulas as needed when the data 
changes.  If this is for a one-time presentation, just set up the chart, 
manually delete the "empty" cells, and move on.

Jerry

Debra Dalgleish wrote:

> If you're using a formula that returns an empty string for missing 
> values, change it to an NA()function. For example:
> 
> instead of   =IF(B9="","",B9)
> use          =IF(B9="",NA(),B9)
> 
> To hide the resulting #N/A errors on the worksheet, you can use 
> conditional formatting. There are instructions on my web site:
>   http://www.contextures.com/xlCondFormat03.html#Errors
> 
> 
> Harry v M wrote:
> 
>> Under Tools/Options/Chart Menu I have set the "Plot empty cells as: 
>> Not plotted (leave gaps)as well as Plot visible cells only, however 
>> the chart still plots null values causing the automatic scaling to be 
>> impractical

0
post_a_reply (1395)
5/18/2004 11:07:51 AM
Reply:

Similar Artilces:

How to only print cells where there is data entered, as in a sched
Please does anyone know if it is possible to print selective cells automatically as in schedule employees for a certain day? I have an employee list of about 65 and would like an enlarged printout showing just the scheduled employees each day and their hours. Any ideas? I'm using Excel 2003. I'd rather not have to buy another or add-in program. Cel Have you tried to see what an Autofilter can do for you? Perhaps filter just for that particular day. You can print the results then remove the filter. Gord Dibben Excel MVP On Tue, 19 Apr 2005 13:43:02 -0700, Cel <Cel@discuss...

Log Plots
Hi all, Is there a way to change the labels on the axis of a logarithm scale plot so it says 10 to the nth-power (i.e. 10^1, 10^2... and so on) instead of the 10, 100, 1000... that Excel uses by default. Using Excel 2002 if that matters... Thanks, Javier Javier, Select the offending axis (click any label on it). Format - Selected axis - Scale - Number. -- Regards from Virginia Beach, EarlK ------------------------------------------------------------- "Javier Gomez" <javier_gomez@REMOVE.THIS.engineer.com> wrote in message news:e6cpCh4VDHA.1512@TK2MSFTNGP11.phx.gbl......

Selecting Certain Cells
I'm making an input sheet and I want the user to only be able to select certain cells. I've got the sheet protected so that a user can only enter data into certain cells, but I can still use the mouse and select other cells on the spreadsheet. Is there a way to do this? Thanks!!! Hi! In the Protection dialogue boxes there are many options that you can set including whether to allow users to select locked cells. Goto Tools>Protection>Protect sheet and a list of those options should appear. Also, you must first select which cells are to be locked. To do this go throu...

Pasting cell values doesn't remove the formula
WindowsXP Excel2003 I select all, copy, go to a new sheet, edit, paste special, values..its pastes different numbers and not the number that I previously had on the sheet. If you select the cell in the original range that looks different after you paste it, what do you see in that cell? What do you see in the formula bar for that cell? Any chance you're copying a range that has hidden rows/columns? Or even pasting to a range that has hidden rows/columns? HT wrote: > > WindowsXP > Excel2003 > > I select all, copy, go to a new sheet, edit, paste special, values..its ...

Allow Numeric Characters Only In A Cell
Hello! I am wanting to both limit a cell to numeric entry only and require 12-digits. Example of typical entry: 01-2005-12345. I have to ensure that the zero will show up at the start if that is what they need to enter and the dashes must fall in the string as shown. I do not want the user to have to key the dashes. I currently have been able to use "Format Cells" to ensure the zero shows up and the dashes automatically insert into the string by using custom format and 0#-####-##### , but I am not able to require 12-digits and limit to numeric entry only. Any help would be g...

Limit cells shown
Hello, I have a worksheet with a select number of cells I want shown. Unfortunately in Excel you can by default theoretically scroll for forever through the worksheet... I want to limit this. I can't find a way to reasonably select my "content" area and "hide" every other cell aside from those selected. Any ideas? Thanks! Maybe this will get you there. Run this in a macro to limit the scroll area. To limit the area: Worksheets(1).ScrollArea = "A1:F10" To remove the limit: Worksheets(1).ScrollArea = "" HTH Regards, Howard <caerdwyn@gmail....

Formula cell references
When you double click on a formula that references other cells you usually see the referenced cells outlined in a color with reference to that same color in the formula. What is this called and is this something you can turn on and off? Where? Thanks RLK It's called "Edit In Cell". You get exactly the same results when you click in the formula bar. If you turn this off, double clicking in a cell containing a formula will then make the focus move to the cell(s) referenced in the formula. <Tools> <Options> <Edit> tab, Check or uncheck "Edit Directly I...

Method for protecting cells which still allows quick editing
I’ve read a few posts on here and I think I’m pretty clear on determining which cells are locked for editing and which are unlocked for editing when a sheet is protected (tools-->protection-->protect sheet). I also know how to password protect a file for either opening or editing. What I am looking for is a method of edit protection for individual cells within a sheet (an entire column, really) which still allows for quick editing by approved individuals. Ex: a protected "signature" column that can be quickly edited by a few individuals. At this point, th...

sorting problems
i get the error message "this operation requires merged cells to be identically sized" i have checked several times and the merged cells are the same size. i have tried re-merging and un-merging and nothing seems to work. any ideas? Un-merge; don't re-merge. Merged cells cause countless problems, of which you've found one. Many others can be seen in the archives of thgese Excel newsgroups. -- David Biddulph "4markw" <4markw@discussions.microsoft.com> wrote in message news:972B5049-62CF-4AEE-A5DD-56E50254DC19@microsoft.com... >i get the...

Unable to lock scroll area or Cell selection
Hi, I'm trying to lock the scroll area on my worksheet. In VB I've got: Private Sub Workbook_Open() Worksheets("Board").EnableSelection = xlUnlockedCells Worksheets("Board").ScrollArea = "$A$1:$u$21" End Sub In the sheet Properties I input: EnableSelection 1-xlUnlockedCells ScrollArea $A$1:$u$21 I save. I protect the sheet. I save again. This works when I go into the sheet, the necessary cells cannot be selected and the scroll area is locked the way I want. I save and close, but upon opening the VB code ...

Combining rows of data that have one cell in common
Hi everyone, let me explain... I'm working with a large excel file (13,000+ rows) of cancer cases recorded in the state of Florida. Each time a patient presents with a cancer, that "case" is recorded as a row on the spreadsheet, along with a UNIQUE patient ID assigned to that particular individual. If that same person develops another cancer sometime later, that information appears in a new case/row, but with the SAME patient ID. I need to export this file to another program (SPSS v13.0) to do some advanced statistical analysis. However, the program requires that all of t...

Graph Question. Values from different pages. Not in same cell ranges.
Hi, I have an excel work book which keeps track of players averages in a dart league. Every week gets a different page. I would like pages for individual players, that will keep track of only 2 columns on a weekly basis, which I will use to make a graph. Because the stats are sorted by high average, the players don't always stay in the same cells. The long way would be for me to reference the necessary cells, week to week. I was wondering if there is a script that can do the following pseudocode. Using C1 in the current individual player's sheet. C1 = Where "Week1.Ce...

too many fields on import, or too few brain cells?
I am a self-taught novice so please bear with me: I am a teacher and have created a scantron form using Adobe Live Cycle. The form uses radio-button groups for students to enter their answers into. I export the data as an .xml file and then try to import the data into access which will create a new record with the student's name, date, and test ID, whereupon I will have some calculated fields which will generate the student's test score. My problem: There are just over 300 fields on the adobe form. The access limit is 255, which makes it impossible to import all of the data. My ...

Concatenate cells without specifying/writing cell address individually
Hi, Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&...

Exclude a cell again
Thx that will help with part of my problem but how would I make A16 variable.........for instance.....the last cell I enter could be A17 not A16 but I don't want to keep going back to my formula to amend it........ "Ardus Petus" <ardus.petus@laposte.net> wrote in message news:uBCw3W1oGHA.2444@TK2MSFTNGP03.phx.gbl... > To exclude cell A16: > =SUM(A:A)-A16 > > Not very elegant, but effcient! > > Cheers, > -- > AP > > "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: > xB6sg.51172$181.12831@fe3.news.blueyo...

I cannot edit in a cell
I have the " allow edit in cell" box cheked inder toolos - options, but when I double click in a cell I do not get a cursor. All the other spreadsheets in the workbook are ok. The sheet is not protected. Hi Kelli, Are you saying that the cursor completely disappears? Perhaps the cursor changed to another form. http://www.mvps.org/dmcritchie/icons/pointers.jpg If it disappears, when does it come back? Look in the name box to the left of the formula bar, is the cell shown there visible. Right click on the sheettab, view code, are there any code there other than the Option E...

How to point to (select) a cell to the left from a cell where I enter the = equal sign?
Hello! How to point to (select) a cell to the left from a cell where I enter the = equal sign? Clicking on it does not select it. Best regards, Dima I'm not sure what yo're trying to do Dmitry. If you type a equal sign in a cell you are commencing to enter a formula.....and then if you left-click on another cell and then press Enter, you have just asked Excel to bring the value from that other cell and place it in the cell where you typed the equal sign. This is a short-cut method instead of having to type the other cells address. If this is not what you're trying to do,...

Formatting Cells #14
Is it possible to format cells to accept text and display that text in title case? I am using Excel 2003. Not through formatting alone. You could use a helper cell: =upper(a1) Or you could use an event macro like the one at Chip Pearson's site: http://www.cpearson.com/excel/case.htm look for: Changing Case On Data Entry Paul wrote: > > Is it possible to format cells to accept text and display that text in title > case? I am using Excel 2003. -- Dave Peterson ...

Protected cells -automatically format to a different color
My old dos spreadsheet (supercalc 5) used to colour the text of protected cells in a different color ,(which made the entry of data easy because the protected cells usually contain formulae ).When reentering new data I was then able to select the whole spreadsheet and "blank " all cells that are not protected in one keystroke. This left all cells that were protected with there formulae intact and highlighted in a different color. Can anyone suggest the way to do this in EXCEL? The ideal solution would be to create a format or a style that will automatically be used whenever a cel...

Change Date By Macro or Code Leaving Only One Record
Hi, I have a table called RUNDATE which has only one record in the date which my whole program looks at as the running date. I would like to run a Macro on a form or a Code upon which once I press it that single date in that table to change to the next day, lets say from 3/5/07 to 4/5/07 to delete the previous record of the 3/5/07 and to be left only with one record with the new date in this case lets say 4/5/07. Much Appreciated make a query of this single record table field - and then put that query into your form as a subform.....you can keep it not visible if you wish......

use same cell w/in a function in a entire column.
I want to be able to create a entire column in which every cell in the column pulls a data point from the same cell. If I high light and paste from my initial, lets say B1 to b2 through b100. It changes the A1 data in each cell to the next to a2 a3 a4 a5 a6 a7 a8 ect.. i know this is simple but thanks anyway. Todd Put a $ in front of the row reference (ex: a$1 instead of a1) to keep the row from advancing as you fill down. (Similarly you could put a $ in from of the column reference to keep the column from advancing as you fill right). Check Excel's help on 'cell and range re...

Search end/beginning of cell
I know I can do this with functions but is there anyway to do search/replace indicating this? In word I just do ^pWord or Word^p but not sure how/if I can do this in Excel For searching, you can look at the whole cell (match entire cell contents) And look for something like: MSNY* This will find the cells that start with MSNY *MSNY will find the cells that end with MSNY The replace is gonna be a problem for you (I bet). What do you want to start with and what do you want to end with? (save your work before you start. I'm guessing that it won't do what ...

replace cell value
how to replace sheet1. cell a1 with shhet2 cell a1 if sheet 2 cell a2 matches sheet1 cell a2 Run this small macro: Sub ReplaceIt() If Sheets("Sheet1").Range("A2").Value = Sheets("Sheet2").Range("A2").Value Then Sheets("Sheet1").Range("A1").Value = Sheets("Sheet2").Range("A1").Value End If End Sub -- Gary''s Student - gsnu200909 "hershel" wrote: > how to replace sheet1. cell a1 with shhet2 cell a1 if sheet 2 cell a2 > matches sheet1 cell a2 > . > On ...

Best way of copy range of cells without select
Hi, Why doesn't this work? "Method 'range' in object sheet failed" . I try to get rid of the copy paste select activat - operations.... Blad105.Range("B6:B25,B29:B44,B52:B56") = Blad104.Range("B6:B25,B29:B44,B52:B56") /Thanks to any help I answer myself: Has to be a ".value" after the specified range - othervice it try to copy the range, and it can't define the same worksheet name on two sheets... /"" The value is needed baecause "Imbecill" <Imbecill@roll.se> skrev i meddelandet news:Kj_oc.59034$mU6....

the line is extending to the next cell
Hi, Could anyone pls help on this? http://cid-17ec75244bac022f.skydrive.live.com/self.aspx/.Public/t11.doc Bst Rgds, HuaMin What is it you are asking?, as far as i can tell if the text did extend out of cell C7 it would destroy the look of your sheet, that said i believe you have the cell formatted as a number, format it as TEXT it should help. HuaMin;708937 Wrote: > Hi, Could anyone pls help on this? 't11.doc - Windows Live' (http://tinyurl.com/23hyzz5) Bst Rgds, HuaMin -- Simon Lloyd Regards, Simon Lloyd 'Excel Chat' (http:...