Quote:
Originally Posted by Tony O[_3_]
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