Is there a trick for changing chart Data without losing all your Formating?

[tonywatsonhelp]

Hi Everyone,

I've created a Quartly chart report that has the same 10 charts for each quarter,
I've finished setting up quarter onr and thought id copy the charts and just change the cells it ref, but when i change the range i lose the formating
i'm i doing something wrong?
can i some how keep the formating?

 

[offthelip]

There is one trick that I find very useful when producing charts for various quarters. I set up an index that picks up the data from the 4 different columns depending on whether I set the indices to 1,2,3 or 4
I then set the charts to look at this single column ( or columns you can just keep repeating the index) . I then create a button that I can click which just cycles the "index" cell round the 4 values. So I click a button and look at all the graphs for the next quarter. Obviously the formatting never change because the charts are still looking at the same place

 

[tonywatsonhelp]

Thank you off the lip thats very helpfull
Tony

 

[Jon Peltier]

As I describe in my tutorial Properties Follow Chart Data Point, an obscure setting causes customized formatting in an Excel chart to follow the cells containing the data and not the plotted points. The name of this property is not explanatory and seems to me is the opposite of what happens. Microsoft doesn't seem to document this property anywhere, other than a mouseover in the Options dialog:

What you can do is uncheck this property, change your chart data ranges, then recheck it (I keep it checked when not changing around my data, because I'm afraid of losing whatever benefit this property may provide).

To get to the Properties Follow Chart Data Point setting, click on Excel’s File tab on the ribbon, choose Options, then click on Advanced. Scroll down to Chart, and you’ll see “Properties Follow Chart Data Point for All New Workbooks” and “Properties Follow Chart Data Point for Current Workbook”. I usually ignore the first (all new workbooks) and check/uncheck the second (current workbook).

To make it easier to toggle this setting, I've added a checkbox to the ribbon of my professional Excel charting software, Peltier Tech Charts for Excel: