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]

@mbiscotto 

Doesn't work in Excel Pro Plus 2016.  Using a slicer to filter reassigns the line colors to the remaining items.  Both of those advanced boxes defaulted to being checked... I deselected, saved, closed Excel, reopened.  This is a very, very simple chart - 2 locations over time, line chart, colors assigned.  Slicer filter to either location, they both use only one of the two colors.  Thanks!

[#3]

mbiscotto, thanks for your answer. You're my hero! :)
For already a year or so I am getting angry at my current Excel version for changing the perfected layout of my data series every time I copy a graph and adjust the actual input for the data series. Thanks!

[#4]

Thanks a lot

[#5]

Thank you my friend !!!

[#6]

I have similar issue, when i prepared the pivot pie and bar chart out of pivot table, after refresh the format of charts are changing, the grand total is occupying its place in charts, which is not looks good in the report, even i made many changes to it, after refresh it regains back.

Then i went for a solution, opened power bi, opened excel there and imported datainto it and downloaded the excel to my local, then i made my changes, that worked.

[#7]

@fabien.calendar 

If you're unlucky enough to still be using this dinosaur of a program in 2022, quite frankly, you can't stop it from changing colors back to the template defaults.  Basically this happens because Microsoft believes they know what you want better than you do in every single instance, even down to the colors you choose.

Go get Smartsheet, or basically any other modern spreadsheet software.  It's the 21st century.

[#8]

This used to work, but Microsoft fixed in recent versions so that it doesn't.

[#9]

Thank you so much!! It was driving me crazy haha

[#10]

Awesome suggestion!

[#11]

@tharangaj 

Very nice thanks.

I have adjusted to my preferences as:

Make workbook xlsm

Under Thisworkbook

Private Sub Workbook_Open()
ThisWorkbook.ChartDataPointTrack = False
End Sub

By doing this, even if other people open this chart won't go crazy as well :)

[#12]

I was dealing with his issue for quite some time (I'm on MS 365 Excel) - especially the series in my stacked bar charts would change the colour every time I would add another column and sort the value by the last data set added.

I have tried everything I managed to google, but nothing worked.

But then, I had a discussion and error/trial session with a colleague and voilà, I think we cracked it.

So, what was done --> legend was formatted (I know, it sounds insane, but it works in MS 365)

In the chart which you don't want to change any series colours, double-click on the coloured shape next to the series name and *format legend entry* --> in the fill colour, even if the colour is the same as you want it to be, just confirm it selecting it once again from the palette.
This way it works for me just the way I need - no series colour changes anymore, and I can sort the date up and down and all stays the same.

I hope it works for some of you at least too.

Let me pls know.

[#13]

@mbiscotto You're a saviour! Been thinking about that one for ages, many thanks :)

[#14]

@mbiscotto  Thank you!  It's my life saver of the month.

[#15]

Is there an equivalent setting/fix for MacOS?