+ Reply to Thread
Results 1 to 11 of 11

How to Make Custom Formatting Stick

  1. #1
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    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.
    Last edited by AliGW; 11-02-2015 at 07:57 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    Re: How to Make Custom Formatting Stick

    Anyone?

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,473

    Re: How to Make Custom Formatting Stick

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    Re: How to Make Custom Formatting Stick

    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
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,473

    Re: How to Make Custom Formatting Stick

    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.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: How to Make Custom Formatting Stick

    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?
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    Re: How to Make Custom Formatting Stick

    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.
    Last edited by AliGW; 10-26-2015 at 01:23 PM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    Re: How to Make Custom Formatting Stick

    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.

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: How to Make Custom Formatting Stick

    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/

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    Re: How to Make Custom Formatting Stick

    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!

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    Re: How to Make Custom Formatting Stick

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Can't make Enable Editing stick
    By simonc2 in forum Excel General
    Replies: 2
    Last Post: 06-19-2014, 08:02 AM
  2. Make A new rule for Custom Formatting
    By Mr.WalterJones in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-16-2012, 12:10 PM
  3. Ading data and make excel add a number and stick with it
    By Hemmiv in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-07-2008, 08:02 AM
  4. [SOLVED] Make pivotchart formatting stick after pivottable refresh.
    By larry garka in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-16-2006, 02:55 PM
  5. [SOLVED] Help: Custom view will not stick
    By Beemer in forum Excel General
    Replies: 2
    Last Post: 05-12-2006, 07:10 AM
  6. Replies: 4
    Last Post: 03-17-2006, 01:50 PM
  7. make custom formatting permanent
    By simha in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2005, 07:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1