SOLVED

How can I stop Excel from changing the colors of my chart ?

Occasional Visitor

I have a chart with past and forecasted figures for several series of data.

I set the colors of each serie in order to have something nice and meaningful : 1 color per serie, dotted for forecast.

Each time I filter my data to see a particular serie, Excel destroys my work to put back its automated layout which are ugly and meaningless.

How can I prevent this ?

I'm using excel professional plus 2013

 

Thanks a lot !

20 Replies

Hi,

 

What is the data source for your charts? Are they power charts, or they are built on tables/ranges?

Hello,

 

if this is a pivot chart, take a look at Jon Peltier's article Pivot Chart Formatting Changes When Filtered It has a few suggestions and workarounds. 

I had the same problem. This may help:

If possible, remove the filter and let excel draw the chart with all data (this may take a long time or even excel may hang, so save your data!). Setting back filters, you'd hopefully see the problem has gone.

best response confirmed by cuong (Microsoft)
Microsoft Verified Best Answer
Solution

File > Options > Advanced > Chart >

deselect 'Properties follow chart data point for current workbook'

deselect 'Properties follow chart data point for all new workbooks'

Thanks for the Tip @mbiscotto it was driving me crazy !!

@mbiscotto 

 

thanks! for the wonderful solution - it helped the issue 

@mbiscotto 

 

Thanks a lot. This helped me a lot. 

Thanks! It is good and help me so much.

@mbiscotto 

Whoa! Thank you seriously for saving my day!

@mbiscotto thanks a lot. seriously.

I dont have this in my options tab, any other solutions possible? @mbiscotto 

thank you so much for this, it has been driving me crazy


@mbiscotto wrote:

File > Options > Advanced > Chart >

deselect 'Properties follow chart data point for current workbook'

deselect 'Properties follow chart data point for all new workbooks'


 

@mbiscotto I am looking to find this on excel 365 on mac, any ideas where it is? I checked preferences charts but couldn't find the option

@tharangaj not a Mac user (or know one), so can't help.  feel free to write back after you've switched to PC/Win10...

@mbiscotto used the VBA script below and it worked. actually switched from PC recently.

 

Application.ChartDataPointTrack = False
ActiveWorkbook.ChartDataPointTrack = False

 

@tharangaj Excellent!  suspected VBA could offer such a solution, but digging into VBA a bit much for typical users and hadn't time to validate this possibility.

@mbiscotto 

you are a f***** LEGEND!

@mbiscotto You are a life saver!! Thanks!