How can I update multiple excel charts at the same time?

[emsy85]

Hi. I have about a zillion charts to update on a daily basis in the same
worksheet. I wondered if there was a way to update all the charts at once?
Thanks

 

[David Biddulph]

The charts are presumably based on data in one or more worksheets, so to
update your charts you need to update the worksheets which include the data.
I'm sorry if you find that the answer is as unclear as the question was.

 

[emsy85]

Thanks for the response. The charts are feeding from various spreadsheets
which are updated daily with large amounts of data. At the moment I manually
update each chart and extend the data source by one day each day. I wondered
if there was a way of updating all the charts at the same time once the data
is updated.

 

[Jon Peltier]

There are a few options.

1. If you're running Excel 2003, define the source data as a List (data
menu). In Excel 2007, Lists were renamed Tables. In both cases, any
formulas, including chart series formulas, which reference the entire column
of a List or Table will update as the list or table adds or removes rows.

2. If you can't do #1, make dynamic charts which have dynamic ranges
defining their data regions. This is covered here:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
http://peltiertech.com/Excel/Charts/Dynamics.html

3. If the changes are simple, and the sheet names are not too intricate, you
could try the (free) series formula editor on my web site:

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

This is particularly useful if all you have to change is, for example, the
last row number in the source data. It does the active chart or any number
of charts at a time.

- Jon

 

[emsy85]

Jon - Thanks so much. I already found some info on your website which has
helped.

 

[Richard Valle]

I saw your post from 2008 about wanting to easily update multiple excel charts or graphs without having to change the SourceData for each graph every day or week (however frequent the time-scale happens to be).

If this subject is still of any slightest interest, please let me know.

I use scores of 12-week graphs that must update every week (adding the new figures each week and discarding the figures from 13 weeks ago). Manually, this could be VERY time consuming.

I just conceived, tested and now use a tamper-proof and simple solution that works beautifully and automatically. I just implemented this in my own graphs and pie charts, et al. The charts update as soon as the most current figures are entered.

It is a comparatively simple and is a clever use of embedding the functions "VLOOKUP()" and "NOW()".

Because it uses "NOW()", the right-hand side of the chart (if you want a line or bar chart) is always the most current data.

To do this, you create what I call a "mirror worksheet". This mirrors the worksheet containing that contains the SourceData. Because the mirror uses "NOW()" the results in the "mirror worksheet" are constantly updating (on their own) to the newest data on the SourceData sheet.

The "VLOOKUP()" function is used to create the mirror and "NOW()" is used to be constantly looking up the most current SourceData information based on your computer clock.

The beauty is that the chart's data source never has to change because the mirror constantly reflects only the most current information on its own, based on your computer clock.

Once this is done, the mirror sheet can be HIDDEN and never touched again. The graphs will update automatically from then on and once it is set up.

Exactly how to set this up can be figured out with the above information, but I will be doing a full write-up on how to do this with more specific directions later.

Let me know if there is interest and I will hurry up with the full write-up.

Richard

 

[joek003]

Yes, Richard, I would be interested in your write up.

Joe K.