How to Make Custom Formatting Stick

I am having a very frustrating time trying to override the theme colours for a chart I have created. I have meticulously gone through and changed each data series to the colours I require (which need to be different to the theme colours) by applying a solid fill to each. Whilst the workbook is open, all is fine, but as soon as I save, close and re-open, the colours revert to what they were before, switching back to automatic. What am I doing wrong? Does anyone know how to do this so that the theme is permanently overridden for that chart? I'm using Excel 2016.

Anyone?

Disclaimer -- I am not at all familiar with 2016.

I don't think I have ever seen this on the earlier versions of Excel that I use. When I format a chart, the formatting is saved with the workbook, as long as I save the workbook before closing it.

I don't know if it will help, but you might upload a sample workbook that exhibits the problem. If nothing else, that would allow us to test this behavior on our computers to see if the problem seems isolated to your machine, or if it is something embedded in the workbook.

I didn't really want to have to upload the sheet, but I've removed the names of the pupils concerned so that you can have a look. the Analysis Dashboard has a bar chart with two series of data: when the series is set to All using the Tracking Criterion drop-down, it appears correctly (gold for the tracking grade, blue for the assessment). Select any other criterion and this swaps round. I've tried editing the other series manually and have saved before closing, but each time I re-open the sheet, it has reverted to the automatic setting.

Thanks for looking - it's doing my head in!

Below is what I want it to look like (top) and what it changes to (bottom) when I change the Tracking Criterion.

Bar Chart AliGW.png

I'm afraid I cannot replicate the problem in 2007. I can change the formatting of the two series, and then switch the tracking criteria between All and M, and the formatting stays the same as your upper picture. I can save, close and reopen, and it reopens with the same formatting I closed with. Perhaps someone with other versions of Excel can check to see if any of them can recreate the behavior you are seeing.

Hi,

I've right clicked on the bars themselves and then selected a fill color. Saved and reopened. The colors came back with my changes included. Are you clicking on the bars themselves and then selecting a new fill color?

Yes, Marvin - tried everything I can think of. I'm going to have to assume, then, that this is a quirk in Excel 2016. Very irritating, although I doubt the vast majority of users will even notice!!! Thanks for looking. In the file you attached, they are even more messed up than before!!! Must be something here.

Thanks, MrShorty - the formatting stays for me until I save and close, then I lose it. Thanks to you, too, for looking.

Well, at least there is a pattern to this: I can go through and save religiously each time I change a series, and the changes stick until I close and re-open, and when I do, it is only the series I have left selected that retains the formatting: all others revert. Must be a bug in Excel 2016. Thanks again for trying to assist, chaps.

Hi Ali,

I think this must be a design decision from the Office team. If I were to pull a chart into a word document and then change the theme colors, should I expect the chart to reflect my theme change? I think this priority on sharing documents/data/charts must be causing this. Maybe you can find a theme color that works and then tell Excel this theme is your standard. Then you can save it and see if the theme goes with the workbook.

Sorry I don't have a good answer. Maybe these will help?
https://social.msdn.microsoft.com/Fo...forum=exceldev
and in a section called Charting Notes on
http://spreadsheetpage.com/index.php..._theme_colors/

Hi, Marvin!

If this is the case, then it's sloppy programming, but as it doesn't happen for you in Excel 2013, I doubt it's the case. I've already changed the theme to make the colours on the pie chart work, and when selecting colours for the bar chart, taken care to select from the Standard palette as opposed to the Theme, so in my view, this override SHOULD be saved with the file for portability. I can't see any good reason why it should not be so. Thanks for your thoughts!

Just as an update to this, and for information, I have just opened the Excel file in Excel 2010, which is what we have here at school, and the custom colours have taken immediately: I changed one series and all changed to match, and they survived closing and reopening the workbook. I can only assume, therefore, that what I am experiencing at home is a bug in Excel 2016, and as such, I'm going to mark the thread as solved. Thanks again to all who responded!