Excel Chart for Consecutive Months.

[nandhamnk]

Hi,


I have a problem of updating chart every month.I usually make a presentation for year (trailing 12 months)and i need to update data each month for chart.Data's are different and i need to present nearly 75 charts.

For Example,

Chart1 - Revenue - Trailing 12 months

Chart2 - Cost- Trailing 12 months

Chart3 - Profit - Trailing 12 months

Chart4 - Attrition - Trailing 12 months

Chart1 - Employee - Trailing 12 months etc


I update this every month and change source date for each chart,as it is for trailing 12 months.I saw some method like adding names to the data,but its difficult for me,since it is nearly 75 charts.My concern is only on changing data source for each chart and it takes bunch of time.Is there is any better way to this.


Thanks in Advance

 

[Luke M]

Jon Peltier covers this exact topic here:

http://peltiertech.com/Excel/Charts/Dynamics.html#DynoLast12


Note that while it may take some work to initially setup all the named ranges (and a lot depends on how your data is setup), it comes down to doing work now and taking it easy in the future, or doing work each month.

 

[nandhamnk]

Thanks Luke.


I saw the link,but it takes lot of time to define names for each chart.is there any chance to create easily

 

[Luke M]

In 2003, you can use Insert - Name, Create, and XL will create name(s) based off of some selected cells. This is handy if you want to just quickly type some names and write (in text formatted cells) the formula for the named range. Assuming a lot of your chart data is simply shifting 1 column at a time, this should be too hard.


Again, this will take some time, but manually changing source data for 75 charts can't be all that fast either.

 

[nandhamnk]

Thanks Luke.I will try this.Is there any quick solution?