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

[fabien.calendar]

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 !

[#2]

Hi,

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

[#3]

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. 

[#4]

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.

[#5 · Accepted solution]

File > Options > Advanced > Chart >

deselect 'Properties follow chart data point for current workbook'

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

[#6]

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

[#7]

@mbiscotto 

thanks! for the wonderful solution - it helped the issue 

[#8]

@mbiscotto 

Thanks a lot. This helped me a lot. 

[#9]

Thanks! It is good and help me so much.

[#10]

@mbiscotto 

Whoa! Thank you seriously for saving my day!

[#11]

@mbiscotto thanks a lot. seriously.

[#12]

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

[#13]

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'

---

[#14]

@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

[#15]

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

[#16]

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

Application.ChartDataPointTrack = False

ActiveWorkbook.ChartDataPointTrack = False

[#17]

@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.

[#18]

@mbiscotto 

you are a f***** LEGEND!

[#19]

@WillHughes 

[#20]

@mbiscotto You are a life saver!! Thanks!