Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Suppress Zero Values In Line Charts?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How can I suppress the plotting of zero values in line charts? The source
cells have a formula that evaluates as zero for future dates in the date

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
Print All Charts That are in a Worksheet
- This macro will print all of the embedded charts which are on the current active worksheet. Each chart will be printed o
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the

Similar Topics

How can I suppress the plotting of zero values in line charts? The source
cells have a formula that evaluates as zero for future dates in the date

Please can someone tell me how to stop my charts plotting zero values
at the end in cases where the chart runs for a longer period than my

For example, my chart total date range is say Jan 05 to Dec 05, and I
have several lines to plot, the longest one extending for the whole 12
month period, but others for shorter periods from say Jan to Jun 05,
and Feb to Nov 05 etc. At the moment the way the source data is set
up, is that for the line item that ends in June, there are zero values
in the column for Jul to Dec 05 and my chart is plotting these zero
values, however I want my line to just end in June 05 otherwise my
chart looks odd.

The easy answer might be to delete the zero values from my source data,
but that will take a lot of fiddling around if I need to do that every
time I want to update my charts.

I am sure I remember turning off zero values once before, but I cant
remember how!

Thanks in advance


How do I suppress zero values from being charted in a data series? I have a line chart and I want my chart to skip drawing a line when it encounters a zero value in the data series on the y axis. In other words the line is discontinuous when it encounters zero values.


I'm having a problem with setting the values of a series collection on a chart. I have several hundred charts to plot with this same routine. It plots the first two fine but once it gets to the third chart, I get a Runtime Error 1004 "Unable to set the values property of the series class" error message when I specify the range containing the "values". The macro seems to have no problems setting the XValues.

One possible explanation I can think of is that the ranges containing the data to be plotted on the third and subsequent charts contain empty cells whereas the data ranges for the first two charts do not. I know Excel is more than capable of plotting charts from ranges containing empty cells when you're plotting charts manually but could this be the cause of my problem? If so, why are there no issues setting the XValues even though the range for the XValue inputs contains the same number of empty cells at the same locations as range containing the Values input? When I put an On Error Resume Next command in front of the section of code for plotting the charts, all charts are created with the correct XValues ranges.

I have designed MI Reports that has three bar charts on it. The source data of Charts correspond to three different excel sheets.and the data in excel sheets come from userforms that I have created.

My problem is when I select the source date of the charts and starts putting the values through user form then charts display right result but when I delete all records from the three sheets then the sorce data range in excel charts changes automatically.

And then if I start putting values through userform then the charts will display few values only and the source data range changes to $A$1:$A$3 .depends.

I want to fix the source data range to 15 records.and it should n't change if I remove values from th sheets.

Can anyone plese help me out

The source data for the X axis is by month (dates formatted as mmm yy) but I can't select Time-scale for the Primary X axis.

I can change the formatting for the X-axis values to m/d/yy and back to mmm yy so I know the source is actually a date.

What I'm trying to do is have excel fill in the missing months as zeros in the graph. I'm using a line with markers and some of the pivot table fields are plotting as points only with no line between. I expect the line to drop to zero for the months between data points.

I am using Excel 2003 and I'm a dunce with charts.

What am I doing incorrectly?



i would like to suppress all zeros in specific columns in my spreadsheet and have found that formatting te columns as Custom #;; does the trick. How do I modify this to leave currency formatting in place for values other than zero? Using the Option under the Tools menu to suppress zeros is global so therefore doesn't do exactly as required.



When there are no values is this string.

Hi and thanks in advance for any help that might be given.
I'm using the following formula to obtain the average of 2 numbers but to show as blank if one of the numbers is missing as in no value for future dates.

Fairly simple stuff but when I use a line chart to display the values and trends, the lines drop to the bottom when they reach the data for the future dates. If I leave the cells blank it charts correctly, but as soon as I extend the formula down the column into the future dates, the problem occurs. This happens regardless of how I format the series to show zero and/or null values. This of course prevents the trendlines from showing future trends as they also drop to the bottom of the chart.

Any and all suggestions will be greatly appreciated.

regds, Norm

I'm trying to suppress the /DIV0 when my formula values are blank or the number entered is zero.

We are checking charts and sometimes we don't have any charts that met our criteria so we get zero for the amount of charts and zero for what we should have done. for example:

Amount of smokers that want to quit: 0
Amount of smokers that want to quit, referred to smoking cessation class: 0

Percent of referred to smoking cessation class (that wanted to quit): /DIV 0
In this case we should get 100 percent (we shouldn't get dinged for no referrals if no one wanted to quit in the first place)

I need to check if the smokers want to quit is zero AND check if we haven't gotten to that month yet (future months)

So I need the answer in A3 to be null if the Len(A1)=0 or if A1 = 0

I have tried this in A3, but it doesn't work:


Any help would be greatly appreciated.


Hi guys

I have a small problem with dynamic charts

I have arranged the source data correctly to include only the cells with values.

However, as the values are determined by formulas, the chart will not recognise the cells with no result as 'blank' and thus still includes them as part of the chart (plotting values of 0, of course)

Anybody know a way to get round this?


I have a worksheet on which I want to display several small charts. I want to display only charts which are relevant to the assessment I am entering.

e.g. I have a chart which shows income for people who are over 60. If the age entered is under 60 I want to suppress that chart.

Ideally I would be able to reformat the layout as well but that isn't vital.

Any idea about the best way to do this?



I have a series of charts on a worksheet, and the related data on another worksheet (workbook attached).

The six charts are regular line charts, but with a vertical line added to show where we currently are (i.e. today's date).

The vertical line works fine on my computer (i.e. it sits between august 10 and october 10 as it should). However on a colleagues computer it sits between Jun-11 and Aug-11, even though when hovering over the line it shows the correct values (today's date).

Has anyone had this happen before and can explain why?



Is there a way of excel not plotting zero on a line graph?

The source data is a formula so it always has the value of 0 for future months.

I have used the tools>options>chart tab to select not to plot blank cells and then entered an IF formula so the cell appears blank if the value is zero. However, the data series line jumps back to the x axis and plots the zeros for the future months.

Can anybody help with this one?

have a nice dynamic chart in my portfolio worksheet. a problem i'm having with it is that even with Y-Axis minimum set to auto, it is always zero. quite a pain when im viewing a stocks history with a large share value..

how can i set this value to change depending on the stock i'm choosing? i'm already using the following values:

Dates =OFFSET(Portfolio!$AN$24, 2, 0, Charts!$B$4, 1)
refers to a range for my x-axis from my portfolio sheet with Charts!$B$4 holding the number of days i want to view

Stock =OFFSET(Dates, -2, Charts!$B$2, 1, 1)
tells the chart which stock to choose with a menu linked to Charts!$B$2

Values =OFFSET(Dates, 0, Charts!$B$2, Charts!$B$4, 1)
refers to a range for my y-axis given by the appropriate days and stock values selected

i thought i could do something like this for my Y-Axis Minimum Value
=(OFFSET(Dates, Charts!$B$4, Charts!$B$2, 1, 1))*0.9

so that way it would give a Y-axis min. equal to 90% of the last stock value in that date range. it works, somewhat.. when i put that into Axis Min. it calculates it and just leaves the number there - not updating dynamically with the chart. i also tried naming the cell and using the same name in the Axis Min., but that didnt work either... any suggestions??

I have two problems which may be related and I'm using EXCEL 2007 on VISTA.
1) For some reason I do not understand I am unable to produce a trendline from a data series in a line chart either by right-clicking the data line or by going through the Analysis tab on the toolbar. Both show the trend line function as de-highlighted.
2) I want also to extend the data series (and ultimately the trendline) to a future date but when I try to do this the empty cells for the future dates are read as zero.
I'm sure there is a simple solution but I can't find it so any help would be most welcome.

I have a large pivot table that will be filtered out to different departments. The issue is data with zero YTD values showing up and making the report longer than it needs to be.

How can I suppress zero values in a pivot table?

Thanks in advance!

Hi. I have one line chart and two different bar charts that I would like to stack on top of each other. I would like the 'X' axis to contain the date corresponding to each of the three different charts. I also like to have three different 'Y' axes (one for each graph).

If this is possible, is there also a way to put a crosshair or pointer on the graphs and have it display the values of all three graphs on any date?


I built a pie chart with a selection dropdown list. I wnat to suppress the labels for the zero values. Any suggestions to how I can do this?


Hello, I created a sprearsheet that has about 8 line charts on it. I have a couple of charts that have about 6 different lines in them. How do you make line charts look neater? The charts to me look messy, is there a way to at least make the lines in the chart not on top of one another, the charts are also dynamic with a start date scroll bar and an end date scroll bar. Thank you

Hi There, anybody knows if it's possible to ignore 0 values in a data range for a chart?

I'm using a pivot table and were able to ignore/suppress the zero values (using filter or IF NA() function). However, in my Excel Pivot Chart all of the x-values are shows, although there are no y-values for those fields (or 0 values). This leads to a chart with very much x-points with value 0.

I want the Pivot chart to ignore the x-range with 0 of NA values in my chart, but cannot figure out how to do that.

Can anybody help me out?
Thanks a lot for your help,

Kind regards,



I have a PLC that is logging data for a particular product (number) every 100 seconds.

A user desires to have a macro that would allow entering two different products (two fields) as well as data range (date is one of the columns). The result of executing that form would be two line charts.

Each line charts will show all elements for that that product.

Product number 1:
Line temp1
Line temp2
Line temp 3

Product number 2:
Line temp1
Line temp2
Line temp 3

These two charts would be similar to stock charts. Both have to be automatically adjusted to fit landscape page of a single sheet, in this case Sheet2.
The idea is to compare two products over time in order to see if there is something wrong happening at certain time.

Not sure how to approach it or if somebody has an example of something similar to this, that would be great.

Thanks in advance

I have six embedded column charts with 2 series and an X values series on one worksheet. I have named ranges throughout the workbook that serve as the series for these charts.

One of the charts is acting a little crazy. The X Values Series is not charting correctly. I checked the validity of the the name in the Name Manager console. I even deleted the original chart, copied another chart and replaced the Series values. I still have an X Values problem.

All other charts are acting normal. I know it sounds like I have a name problem, but the name evaluates correctly. Any thoughts are appreciated.


I have a table of weekly figures that automatically updates from a table of daily figures using the basic formula of IF(C1="","",C1).

I then have a Line Chart plotting the historical trend of these weekly figures. However, since Excel seems to translate all non-numerical entries as a zero, the line chart is fine up until the current week, but then drops to zero for future weeks (something which Management don't like to see!).

Is there any way of getting Excel to not plot non-numerical values?
Alternatively, can someone suggest a better way of doing the formula so that the Chart ignores these non-numerical values (I have tried using "NA" instead of just "" but get the same result).

Basically, I do not want to have to manually amend the chart source data each week, and do not want to have to manually update the table of weekly figures each week. I would prefer to avoid Macros as well if possible.

I am using the ancient Excel 2000 btw.

I have read some help topics on how to suppress the error message: #DIV/0!

However, the formula in the cell that I want to suppress the error message is a bit complicated. How would I suppress the above error message in this formula, and to show " --" instead?

=COUNTIF(S6:U8,"X") / (COUNTIF(S6:U8, "X") + COUNTIF(P6:R8,"X"))

The above formula calculates a percentage in a series of merged cells.