# Excel 2007 charts: want to avoid zero values

T

#### Tony O

I have a series of charts in an Excel 2007 spreadsheet which are
showing sales values by group over the 2012 year.

I want the axis for every month to be the same throught the year, for
example, the May report will show charts
with the months January, February, March, etc., until December
everytime the report is mailed out.
The sales totals for each month are the numbers that I want as the
values for January, February, March, etc. for all twelve months.

I have a formula that adds up all the cells in each row, and gives the
grand total for the value to be graphed.
If there is no data for upcoming months, there is a zero that fills in
in the sum column. Again, there are multiple charts with multiple
summed totals that I am working with.

I would like the charts to populate automatically with the new data
for each month that is being entered,
instead of me manually going in to each chart, selecting the data,
updating the range, etc.

How do I get the chart to only graph values that are not zero for each
month?
Do I have to format the sum cells so that they are not showing
anything when there is a zero? If so, how do I do this?

Maybe there is another setting in the graph? If this is the case,

P

#### Peter Lyons

'Tony O[_3_ said:
;1601525']I have a series of charts in an Excel 2007 spreadsheet whic
are
showing sales values by group over the 2012 year.

I want the axis for every month to be the same throught the year, for
example, the May report will show charts
with the months January, February, March, etc., until December
everytime the report is mailed out.
The sales totals for each month are the numbers that I want as the
values for January, February, March, etc. for all twelve months.

I have a formula that adds up all the cells in each row, and gives the
grand total for the value to be graphed.
If there is no data for upcoming months, there is a zero that fills in
in the sum column. Again, there are multiple charts with multiple
summed totals that I am working with.

I would like the charts to populate automatically with the new data
for each month that is being entered,
instead of me manually going in to each chart, selecting the data,
updating the range, etc.

How do I get the chart to only graph values that are not zero for each
month?
Do I have to format the sum cells so that they are not showing
anything when there is a zero? If so, how do I do this?

Maybe there is another setting in the graph? If this is the case,

Hello,

I cannot answer specifically which menu's to enter but the zero valu
issue your having can be solved I think in data>Properties>Chart ther
is an option of how to handle zero values.

The way I get around the issue of having to select the series each wee
in my instance is that I have set the series for all 26 weeks of th
half year, and, use the hide function on the weeks that have no data i
them yet. Select the whole row or column depending on how you have th
data setup.

Using the same menu to deal with the zero value there should also be
tick box that selects to ony chart the visible data.

Once I want to enter data for a week I unhide everything then enter th
data and rehide everything else again.

Hope this makes sense and helps.

Pet