Updating 100 Charts (Excel 2010)

**munkybut** · 11-20-2012, 02:49 PM

Hi everyone,
This is my first post here *pop* and I will do my best to keep this clear and not be a nuisance.

I have a rather large report that I run monthly that contains charts for over 100 different applications in Excel 2010. Once I got it all setup, it was great, but the problem is that every so often they suggest a change be made. While this wouldn't be an issue, making an update to 100 charts can be VERY cumbersome. I have done a lot of searching into this and I have found that I can use Named Ranges to make this a bit easier, but I would still need to make 100 named ranges and still need to update 100 charts to look at the correct ranges.

I currently have my data setup as follows:

Capacity Table:

Application	Release 01	Release 02	...	Release 10
Application 1	10	11	...	9
Application 2	50	57	...	51
...	...	...	...	...
Application 100	17	14	...	19

Demand Table:

Application	Release 01	Release 02	...	Release 10
Application 1	9	12	...	8
Application 2	49	60	...	50
...	...	...	...	...
Application 100	15	19	...	19

Followed by several other data points.

These all populate about 10-20 pages of charts, which again, isn't the issue, the issue is making updates or adding new data points to each chart for 100 applications.

I have tried recording a macro, but when I update the Series formula, the Macros do not pick up the edits and only shows that I selected the Series.

Do any of you have any clever ideas or perhaps can tell me if I'm approaching this whole thing from the wrong direction?

-Thanks in advance!
Munkybut

**jnp15** · 11-21-2012, 11:22 AM

Hi Munkybut,

I was faced with a different but similar problem. Hopefully you can use something from the way I have handled it. If you convert the data that you are graphing into a table, create pivot tables on the base table and the create charts from those pivot tables you should find that the charts are much more accomodating to any changes that you make to the talbe or pivot tables. Each case is unique so I'm not sure how well this will work in your situation. Also when making lots of charts it is good to have them based on the default chart colors etc. That way, if you find that want to change the colors on the charts, you can just change the default colors and all the charts will make the change automatically. When you have charts that you want different than the default then you should save it as a template and then apply that template to all the other charts that you want to have the same look.

Hope this helps.

Jon

**munkybut** · 11-21-2012, 12:58 PM

Hey Jon. Thanks for the follow up!

I didn't know that you could do templates with charts. That will certainly go a long way towards decreasing the amount of time I have to manually update these.

If anyone has any other thoughts, I'd love to hear them!