SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-336069%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-336069%22%20slang%3D%22en-US%22%3E%3CP%3EFile%20%26gt%3B%20Options%20%26gt%3B%20Advanced%20%26gt%3B%20Chart%20%26gt%3B%3C%2FP%3E%3CP%3Edeselect%20'Properties%20follow%20chart%20data%20point%20for%20current%20workbook'%3C%2FP%3E%3CP%3Edeselect%20'Properties%20follow%20chart%20data%20point%20for%20all%20new%20workbooks'%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-198812%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198812%22%20slang%3D%22en-US%22%3E%3CP%3EI%20had%20the%20same%20problem.%20This%20%3CSTRONG%3Emay%3C%2FSTRONG%3E%20help%3A%3C%2FP%3E%3CP%3EIf%20possible%2C%20remove%20the%20filter%20and%20let%20excel%20draw%20the%26nbsp%3Bchart%20with%20all%20data%20(this%20may%20take%20a%20long%20time%20or%20even%20excel%20may%20hang%2C%20so%20save%20your%20data!).%20Setting%20back%20filters%2C%20you'd%20hopefully%20see%20the%20problem%20has%20gone.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80950%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80950%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20this%20is%20a%20pivot%20chart%2C%20take%20a%20look%20at%20Jon%20Peltier's%20article%20%3CA%20title%3D%22Pivot%20Chart%20Formatting%20Changes%20When%20Filtered%22%20href%3D%22http%3A%2F%2Fpeltiertech.com%2Fpivot-chart-formatting-changes%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EPivot%20Chart%20Formatting%20Changes%20When%20Filtered%3C%2FA%3E%20It%20has%20a%20few%20suggestions%20and%20workarounds.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80499%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80499%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20data%20source%20for%20your%20charts%3F%20Are%20they%20power%20charts%2C%20or%20they%20are%20built%20on%20tables%2Franges%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-692594%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-692594%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20Tip%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3Bit%20was%20driving%20me%20crazy%20!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-695938%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-695938%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks!%20for%20the%20wonderful%20solution%20-%20it%20helped%20the%20issue%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-743636%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot.%20This%20helped%20me%20a%20lot.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80475%22%20slang%3D%22en-US%22%3EHow%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80475%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20chart%20with%20past%20and%20forecasted%20figures%20for%20several%20series%20of%20data.%3C%2FP%3E%3CP%3EI%20set%20the%20colors%20of%20each%20serie%20in%20order%20to%20have%20something%20nice%20and%20meaningful%20%3A%201%20color%20per%20serie%2C%20dotted%20for%20forecast.%3C%2FP%3E%3CP%3EEach%20time%20I%20filter%20my%20data%20to%20see%20a%20particular%20serie%2C%20Excel%20destroys%20my%20work%20to%20put%20back%20its%20automated%20layout%20which%20are%20ugly%20and%20meaningless.%3C%2FP%3E%3CP%3EHow%20can%20I%20prevent%20this%20%3F%3C%2FP%3E%3CP%3EI'm%20using%20excel%20professional%20plus%202013%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-80475%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharts%20%26amp%3B%20Visualizing%20Data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855613%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855613%22%20slang%3D%22en-US%22%3E%3CP%3EThanks!%20It%20is%20good%20and%20help%20me%20so%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-959857%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-959857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhoa!%20Thank%20you%20seriously%20for%20saving%20my%20day!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1280067%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1280067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3Bthanks%20a%20lot.%20seriously.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281013%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281013%22%20slang%3D%22en-US%22%3E%3CP%3EI%20dont%20have%20this%20in%20my%20options%20tab%2C%20any%20other%20solutions%20possible%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1487697%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1487697%22%20slang%3D%22en-US%22%3E%3CP%3Ethank%20you%20so%20much%20for%20this%2C%20it%20has%20been%20driving%20me%20crazy%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EFile%20%26gt%3B%20Options%20%26gt%3B%20Advanced%20%26gt%3B%20Chart%20%26gt%3B%3C%2FP%3E%3CP%3Edeselect%20'Properties%20follow%20chart%20data%20point%20for%20current%20workbook'%3C%2FP%3E%3CP%3Edeselect%20'Properties%20follow%20chart%20data%20point%20for%20all%20new%20workbooks'%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1725410%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1725410%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3BI%20am%20looking%20to%20find%20this%20on%20excel%20365%20on%20mac%2C%20any%20ideas%20where%20it%20is%3F%20I%20checked%20preferences%20charts%20but%20couldn't%20find%20the%20option%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728878%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728878%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813620%22%20target%3D%22_blank%22%3E%40tharangaj%3C%2FA%3E%26nbsp%3Bnot%20a%20Mac%20user%20(or%20know%20one)%2C%20so%20can't%20help.%26nbsp%3B%20feel%20free%20to%20write%20back%20after%20you've%20switched%20to%20PC%2FWin10...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1729010%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1729010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3Bused%20the%20VBA%20script%20below%20and%20it%20worked.%20actually%20switched%20from%20PC%20recently.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EApplication.ChartDataPointTrack%20%3D%20False%3C%2FPRE%3E%3CPRE%3EActiveWorkbook.ChartDataPointTrack%20%3D%20False%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1729015%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1729015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813620%22%20target%3D%22_blank%22%3E%40tharangaj%3C%2FA%3E%26nbsp%3BExcellent!%26nbsp%3B%20suspected%20VBA%20could%20offer%20such%20a%20solution%2C%20but%20digging%20into%20VBA%20a%20bit%20much%20for%20typical%20users%20and%20hadn't%20time%20to%20validate%20this%20possibility.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1825147%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20stop%20Excel%20from%20changing%20the%20colors%20of%20my%20chart%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1825147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282979%22%20target%3D%22_blank%22%3E%40mbiscotto%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20are%20a%20f*****%20LEGEND!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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 !

17 Replies
Highlighted

Hi,

 

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

Highlighted

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. 

Highlighted

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.

Highlighted
Best Response confirmed by cuong (Microsoft)
Solution

File > Options > Advanced > Chart >

deselect 'Properties follow chart data point for current workbook'

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

Highlighted

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

Highlighted

@mbiscotto 

 

thanks! for the wonderful solution - it helped the issue 

Highlighted

@mbiscotto 

 

Thanks a lot. This helped me a lot. 

Highlighted

Thanks! It is good and help me so much.

Highlighted

@mbiscotto 

Whoa! Thank you seriously for saving my day!

Highlighted

@mbiscotto thanks a lot. seriously.

Highlighted

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

Highlighted

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'


 

Highlighted

@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

Highlighted

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

Highlighted

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

 

Application.ChartDataPointTrack = False
ActiveWorkbook.ChartDataPointTrack = False

 

Highlighted

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

Highlighted