How to make global changes to charts

[Rick]

Hi
I have had occasion to make spreadsheets with 30 or more charts. I have
needed to make the same change on all the charts, such as changing the x-axis
label. Is there a way to do that all at once (all changed to the same thing)
or must I tediously change each one at a time? Thanks

 

[ShaneDevenshire]

Hi Rick,

If its the x-axis label the easiest solution is to create the charts
initially with the label referencing a spreadsheet cell rather than being
hard coded. When you want to update all the labels you would just change the
entry in the cell. All charts would use the same cell reference.

How to reference a cell from a Title, label, or text box on a chart:
Click the item once, type = and then click on the cell where the text entry
is. Then press Enter.

You can also do mass updates via VBA - you would need to write code.

Cheers,
Shane Devenshire

 

[Rick]

Thanks Shane - unfortunately finding out after the fact. I will seek to
reference the labels to a cells next time around. I stopped coding about 20
years ago so that would not be my first option. Thanks for the info.
Rick

 

[Del Cotter]

Thanks Shane - unfortunately finding out after the fact.

In Excel versions 2003 and earlier, you can lessen the pain by using the
F4 key. Make a single change in the first chart, then select the
equivalent feature in another chart and press F4. The last change you
made repeats. Go through all your charts, and start again with the next
little change. It can be faster that way.

This can be useless, as when you want to change the line of a bar but
not the color, and F4 changes both. That's why if I want to change the
color of bars I always edit the color palette and leave the bars on
automatic. F4 then selects "automatic" color, and only the line is
changed, as wanted.

It works differently in 2007, but I haven't used that version, so I
don't know what the differences are or whether they're an improvement.

Another tool might be Copy.. Paste Format, but that doesn't always work
well, and things like chart title are treated like Format when they're
obviously Content.