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,
please post.

Hello,

I cannot answer specifically which menu's to enter but the zero value issue your having can be solved I think in dataPropertiesChart there is an option of how to handle zero values.

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

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

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

Hope this makes sense and helps.

Pete

In Excel 2007:

Select the chart you want to adjust,
Then select 'Design' on the main menu bar.
Choose 'Select Data'.
At the bottom of the 'Select Data Source' window displayed is a button labeled 'Hidden and Empty Cells.'
Press that and you should see another window called 'Hidden and Empty Cell Settings.'
This gives you an option to show empty cells as 'Gaps', 'Zero', or 'Connect data points with line.'
An additional tick box in that window allows you to set 'Show data in hidden rows and columns.

Cheers.

William B.

P.S. Anyone discovered how to quickly copy settings from one chart to another in Excel 2007 ???
In Excel 2003 you could just copy a chart with some desired format settings and paste it as
'settings' to another new chart. Which then only required minor corrections to scales or axis
settings. How to do this in 2007 ???