Zero Values in Chart

Can anyone please tell me how to stop a chart from displaying zero values.

EX: 10 x axis entries but only the first 5 contain non-zero values.

Thanks in advance.... Russ


0
cpdadmin (3)
7/13/2005 8:00:55 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
505 Views

Similar Articles

[PageSpeed] 9

If the cells are truly blanks, try Tools menu > Options > Chart tab > 
Show blank cells as... Not Plotted.

If the cells only look like blanks, such as formulas that return "", 
change the "" to NA():

=IF(A1>0,A1,NA())

The NA() produces an ugly #N/A error in the sheet, but in line and 
scatter charts they aren't plotted. Debra Dalgleish shows how to hide 
the errors with conditional formatting:

   http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


RUSS wrote:

> Can anyone please tell me how to stop a chart from displaying zero values.
> 
> EX: 10 x axis entries but only the first 5 contain non-zero values.
> 
> Thanks in advance.... Russ
> 
> 
0
7/14/2005 12:40:47 AM
Reply:

Similar Artilces:

Sumif returning incorrect value
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have data in columns c to f and rows 2 to 20 <br> I also have data in a2 to a20. <br> I am trying to sum the data in s2 to f20 when the row matches my criteria in a2 to a 20. The formula that I am using is <br><br>sumif(a2:a20,&quot;=12&quot;,c2:f20) <br><br>the result will only sum the values in column c. <br><br>Does anyone know what the problem is? <br><br>Thanks <br><br>Allan SUMIF() has various limitations. this will work,...

Where free 2 sigma statistical process control chart, free?
where can I download a free statistical process control chart template? ...

Copying values from place to place
Hello community: Is it possible say, to type a value in a textbox in one form (Form A), automatically copy that value, then open another form( Form B) and automatically paste that value into another textbox in Form B? Is this possible? Thank you for your help. -Gabriel M. If you open Form B from Form A you can use the OpenArgs of the OpenForm command line to pass a value to Form B. docmd.OpenForm "FormName",,,,,,Me.TextBoxName On the OnLoad event of the Form B you can use set the value in the text box If not IsNull(Me.OpenArgs) Then Me.TextBox = Me.OpenArgs End If -- ...

Constant List
Is there an easy way to return the values of the ChartType constants? Excel 97 does not seem to list them in help and I have not been able to find them in google.group searches. Thanks M Select xlChartTypes in the Classes pane of the object browser. The constants are named in the members panel. If you select one, it provides the numeric equivalent at the bottom of the object browser, e.g., Const xlArea = 1 Member of Excel.XlChartType - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ M wrote: > Is there...

Excel --- Physical dimensions of a chart object
Is there a way to know the physical dimensions of a chart object on a worksheet? I have data in several worksheets and I would like all my charts to be of the same size (obviously this means that data with a smaller range of values will be more spread out than data with a larger range of values). Thanks, Nimmi This is just an example: Sub Macro2() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select n = ActiveSheet.Shapes("Chart 1").Width m = ActiveSheet.Shapes("Chart 1").Height MsgBox (n & " BY " & m...

Controlling Gap Width for BAR Chart
Hi, I have the following data for example. A = 2 B= 3 C = 4 I can create a 2 D Bar chart from this quite easily. Now If I make the data to A = 2 , 3 B= 3 , 4 C = 4 , 5 I do want to plot the data on the basis of first two values as mentioned in the first example, but I want to control the width of the Bar from the value given after comma. Please help. On Sat, 24 Feb 2007, in microsoft.public.excel.charting, Fazi <farazahmedtoor@gmail.com> said: >I have the following data for example. >A = 2 , 3 >B= 3 , 4 >C = 4 , 5 > >I want to plot the data on the basis of firs...

How do I add y-axis labeling on the right side of chart?
I'd like to add an axis with same values on the right side of the chart. You'll need to have at least two series in order to enter a secondary axis. On one of the series, change the Axis to Secondary axis. "DavidNHFCU" <DavidNHFCU@discussions.microsoft.com> wrote in message news:28759EE0-759E-4964-8F5E-6C49099653B6@microsoft.com... > I'd like to add an axis with same values on the right side of the chart. How can I force the y-axis at the bottom of my chart to appear for all 12 months when I only have Jan, Feb and March figures to put in the first thre...

excel chart showing movable graph at what point we make a profit
in a business would like to make a chart based on accumulated Earnings & Profit/Loss for year to date, perhaps movable showing our break even point and by moving chart to a another earnings figure what profit we should make. For example put in earnings for YTD Profit/Loss YTD then I can move the earnings part of the chart to show what our profit would be for a given earnings figure. Alternatively If For example if we earn $200 for year so far and profit is $0 but any amount over that is 18% profit so I can move chart line to say $240 and it will say what profit I will make. Hope ...

What is the best chart for this....
When I want to compare how each salesperson's sales contributes to Total Sales, I can use a pie chart. But what do I use when I want to compare how each salesperson performed against the *average* of total sales? I am envisioning a vertical bar chart with a horizontal line at the average, but I need a solution that can be generated by a macro. Any advice? Thanks in advance. Patti Patti - That's how you would do it, a column chart with a horizontal line. On this web page, I give a couple techniques to achieve the horizontal line: http://peltiertech.com/Excel/Charts/AddLine...

"Print to Scale" Excel 2007 Charts
In Excel 2003, one could double-click a chart, print, and the chart would auto-size to fill a page. I am unable to print my newly-created Excel 2007 charts so they auto-size to print one-to-a-page (now requires several time-consuming steps for reformatting titles, axes, etc.). I do not have a "print to scale" option in my page setup. Printing to a Ricoh Aficio AP610N. Thanks so much. I will assume we are talking of an embedded chart (one sitting on a worksheet) and not a chart sheet. In Excel 2003 (all earlier) you did not double click, you just single clicked to...

Bar Chart (2D) won't let me 'gap' one bar behind the other?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a nice, functioning bar chart that has 2 data fields: ACTUAL and BUDGET. <br><br>Actual is on one axis and Budget is on the 2nd axis. <br><br>These charts look nice side by side but I want one bar to be partly behind the other. <br><br>So, I select a data set. <br> Choose Format Data Series <br> Options <br> Overlap... (and no matter what value I choose, I can't overlap)???? <br><br>But, if I can adjust the Gap Width. <br><...

Stacked and single column in same chart?
How can I do a chart with a stacked column beside a single column? When I build a stacked column chart, any new source data I add wants to put it in the same stacked column. Use one of the links on this page. You need to set up the data so the single column is in a stacking position with no other columns of data. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Einstine wrote: > How can I do a chart with a stacked column beside a sing...

Logical Filtering based on Value
I've been using this formula to sort some values and return results if( or( and( b9<>"", isnumber( find( Left(b9,1 ,"AWFUY") ) ), isnumber( find("SHORT",g9 ) ), sum( countif(c9,{"BOG","BLM","CMO"}) ) ) ) ) ,i9-0.01,j9 ) --I used ALT-ENTER between each formula so as to see thing clearly Column A values can be "B01" Column B values can be "BRN" Columns I & J are values, one being lowest the other being highest allowed. On another sheet I...

How to force the user to fill in values for yes/no fields ?
Hi ! I'm using MS Access 2003. I have a table with a number of yes/no fields. When a new record is started, I want the user to explicitly specify a value for each of these fields, e.g. by choosing one of two radio buttons associated with the field. Now, it was no problem for me to arrange an option group with two suitable radio buttons for each of these fields. The user can then choose explicitly between yes and no. However, all fields seem to have implicit values immediately after a new record has been started. Therefore, the new record can be stored in the db without the user making...

Find a value in cells
Hi, all, I have something like this : a 1 b 4 c 5 d 3 I'm doing a max function in the number column and it returns me '5' But, after that, I want to know the letter which corresponds to the max number How can I do that? Thanks Nic -- nicgendron ------------------------------------------------------------------------ nicgendron's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25151 View this thread: http://www.excelforum.com/showthread.php?threadid=386417 Let's say that...

Average & eliminating zero value Need HELP !! PLEASE
In cell C4 im calculating average hours for D5:D65 of all employees In cells Z5:Z65 If L is entered means laid off In cells AB5:AB65 IS AVERAGE OF ALL EMPOLYEES HOURS in cells D5:D65 if L is entered in Z Column In cells D5:D65 IS all employees hours FORMULA FOR C4 IS {=AVERAGE(IF(ISNUMBER(D5:D65),IF(D5:D65>0,D5:D65,0)))} Formula for cells AB5:AB65 IS =IF(Z5="L",R4,"") Formula for D5:D65 IS =IF(Z5>0,0,IF(B5="","",AA5+SUM(E5:J5)*1.5+K5*2)) I want to average all employees hours except if L is entered in Z Column so say 65 empl...

Infopath w/ manually entered values in drop-down and qry results
I had originally posted this elsewhere, but was told this forum is the appropariate plase. I have an Infopath form with a drop-down listbox, that is poulated with manually-entered values. I choose a value, submit the updated data, and it does put the correct value in the SQL 2005 database. However, the next time I query the data, the value in the drop-down list box is the default value for the list-box, not the value from the database, which is misleading. I would have expected the drop-down listbox to display the value from the database instead. Thanks. On Tue, 4 Sep 2007, in...

adding a zero in front of number
how do you add a zero in front of other numbers, I am using item numbers and most start with zero, just shows whole numbers when I enter. example 095421 when I enter shows 95421. help. Hi When the number must remain numeric data, then format the cell as Custom "00000" (the number of 0's determines to which length is the entry padded). When you want the number to be converted to string, then use the formula (in my example the original number resides in cell A1) =TEXT(A1,"00000") (again, the number of 0's in format string determines the length of padding) Arvi ...

Can anyone help with this chart?
I would like to have the following values/labels on the y and x axis of any style of chart: X-axis: July, August, September etc for a full financial year; Y-axis: FB, AP, DR, LA, PC. OR the other way around. Presently the Y-axis simply displays the labels 'Jan Jan Jan Jan Jan' etc. Thank you. Any help would be much appreciated. Check out Jon Peltier's article on creating a vertical category axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "A little stuck" wro...

center data labels on my column chart
How do i get my data labels to appear in the center of each column on my chart? Thanks. Click on one of the labels (that will select all the labels in the series) Choose Format>Selected Data Labels Select the Alignment tab From the Label Position dropdown, choose Center Immediately after formatting the first series, if there are other series in the chart -- Select a label in another series Press the F4 button on the keyboard, to repeat the formatting from the previous series Repeat for all series Dan wrote: > How do i get my data labels to appear in the center of > e...

Dynamic Chart #2
Can you please help me to create a dynamic chart by using VBA? Thanks in advance!! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements VBA may not be needed. See the Excel | Tutorials | 'Dynamic Charts' page of my web site. -- [Posted directly to the Usenet newsgroup -- no affiliation with Excelforum] Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel...

2007 charting problems
Is anybody having these problems, and can anybody tell me how to fix them? 1. In Excel 2003, I could select a range of cells and drag them into a chart and they would be pasted as a new series into the chart. Is this feature gone in 2007? 2. When I have a rotated Y-axis label, I can click on the text and see an insert cursor, but when I start typing, only the first letter appears in the label and the rest end up in the formula bar. When I hit enter, only the new text from the formula bar shows up in the label. 1) Just Copy the range, right click the chart and use Paste 2) Do not use ...

chart zero
Hello, first Post - so here goes.... I am ploting a simple chart in Excel - when the value of a cell, say F5, is zero (calculated as =F4/F3) or F4 and F3 are zero and F5 returns #DIV/0! - I don't want to plot that cell at all. At the moment it is plotted as zero in both cases. I'm using Excel 2000. Many Thanks Andy Have just found the other threads dealing with this issue - #N/A and a white font will do it for me. Thanks anyway guys Andy "First & Last" wrote: > Hello, first Post - so here goes.... > > I am ploting a simple chart in Excel - when the va...

adding a line to an existing chart
Hi, I have a chart with bars and two lines but I want to add another line on top of what I already have...can I do this if so, how? I have tried many things but it is not showing up. Please provide as much detail as possible, as I am fairly new to this. JudyT Select the data for the new line (say F2:F20) Click the Copy tool Click the chart Use menu command Edit | Past Special; specify New Series; click OK Suppose the data is G2:F20 with new x-values in column F Select the data for the new line (say G2:F20) Click the Copy tool Click the chart Use menu command Edit | Past Special; speci...

Charting Problems #2
I need to create a chart that has axes in "exact" inches. For example, the printed plot area is 6" x 3", or at least 1 axis is exactly inches. ...