Recent Colors
When you customize a color and apply it to an object in Microsoft Office, the color is stored as part of the Office file, and presented in a “Recent Colors” category on the various colors dropdowns. Up to ten of these custom colors are displayed, with the most recent at the left of the list and the oldest moving further to the right until a new color pushes it off the list.
The screenshot below shows three custom colors applied to cells B4:B6 of a worksheet, and where they appear in the color dropdown. The pink color was the latest applied, so it is furthest left. It is highlighted with a thin red outline because it is also the fill color of the active cell.
The numerical and alphanumeric codes in columns C and D are the decimal and hexadecimal representations of these colors. They were generated using the following VBA procedure:
Sub OutputColorInfo()
Dim c As Range
For Each c In Selection.Cells
c.Offset(, 1).Value = c.Interior.Color
c.Offset(, 2).Value = WorksheetFunction.Dec2Hex(c.Interior.Color)
Next
End SubColors are the combination of the three color channels (red, green, and blue), and each channel can have a value between 0 and 255 (or between 0 and FF in hexadecimal notation). The yellow color above has a blue channel value of 66, and red and green channel values of FF, so the code for this yellow is 66FFFF. Note that VBA presents the channels in BGR order, but it’s still called “RGB”.
A customer asked how to get rid of some but not all recent colors used in Excel. This is not too hard to do, but you should remember that any time you format an object with a new custom color, this new color becomes a new Recent Color.
Where are Recent Colors Stored?
Recent Colors appear in the color dropdowns only when the file they were defined for is the active file in its Office application (in this case, the active workbook in Excel). This means they must be stored somewhere within the document.
The first step in modifying the Recent Colors is to close the Office file. Below we see the workbook “recent colors.xlsm” in a Windows Explorer window.
Office files are really zip files which contain xml and other files within a well-defined directory structure. You can access this structure by simply adding the file extension “.zip” to the file name, and dismissing the warning about changing file extensions.
Double click the zip file to see the top-level structure of the file.
Double click on the “xl” directory within the file.
Finally, copy the “styles.xml” file, navigate out of the zip file to the directory the Office file was saved in, and paste the xml file here.
Open “styles.xml” in your favorite text editor. I use Notepad++, which can apply syntax-based color coding to files it recognizes. This is the entire content of my particular styles.xml file. Note the <mruColors> tag near the end of the file. This contains three <color rgb="blahblah"> tags that contain the definitions of our MRU (Most Recently Used) colors.
Changing Recent Colors
Microsoft Office doesn’t bother making the XML easy to see by using line feeds and spaces, but you can go ahead and apply your own, as I’ve done below.
Note that the RGB values in the <color> tags begin with an extra FF (for the transparency channel), and list the red, green, and blue pairs in the RGB order, not the BGR order produced by our VBA routine above. No big deal, Office uses the XML RGB for its user interface and VBA uses BGR, and they don’t talk to each other, so they don’t confuse each other, they only confuse us.
Delete the <color> tag(s) you don’t want (you could also change the color or add new ones if you can determine the desired RGB values). If you delete all of the <color> tags, you must also delete the <mruColors></mruColors> node that remains, or Excel will tell you there’s a problem with the file and will offer to recover as much of it as possible. Leaving an empty <colors></colors> node doesn’t lead to this problem, nor does removing it.
Save the xml file, and copy it back into the “xl” folder in the zip file. Delete the “.zip” extension you added to the Office file earlier, so it is left with its original Office extension (“.xlsm” for our workbook), and reopen the file.
The screenshot below shows the three colored cells, but only the two non-pink Recent Colors in the dropdown. We have successfully removed the recent color from the workbook’s remembered list, but we haven’t removed the color from any objects we formatted with it.
Note that reapplying the pinkish custom color to any object in the workbook will reestablish it as one of our Recent Colors.
Thanks to PowerPoint MVP Steve Rindsberg for showing me the <mruColors> tag I was too busy to notice the first time I looked. Check out Steve’s PowerPoint FAQ Pages and his PPTools – PowerTools for PowerPoint.
gerdami says
I prefer the “XML Notepad 2007” editor. It shows an XML file with a tree structure. A very nice tool to discover the structure of Excel files.
Jon Peltier says
Since I wrote this article, I’ve discovered the XML Tools add-in for Notepad++, which can insert and remove line feeds and perform a few other tricks. And there’s also 7-Zip, a free archive file manager which recognizes Office files as archives without having to change the file extension. You can set Notepad++ as the default editor for files in 7-Zip, and do all this a little easier.
I’ll check out XML Notepad 2007 while I’m at it.
eljay says
Interesting. How could a color be added to the recent via VBA? (In PowerPoint VBA, the ExtraColors.Add is used, making it easy.) How can this be done in Excel? Thanks.
Jon Peltier says
Eljay –
VBA does not have this capability in Excel. You need to hack into the XML code of closed files.
Recent colors is a fragile thing to rely upon, though. I’m working on an article that covers Custom Colors (and also regular colors).
Eljay says
Thanks, Jon. Pity, MS didn’t give access to that object in Excel like in PPT. I wrote a PPT addin that adds one of a choice of set company colors to the Recent area upon a click, which can then be used with fill, font, lines, etc. No, I can’t use Themes because they have more colors than Themes allow (about 20). With my addin, they have a menu of all those colors on the Ribbon, then use the Recent color area to apply the chosen color. (Works flawlessly for past 9 years in 30 countries for over 80,000 users.)
Now I want to do the same in Excel. How would you suggest that be done with button-click automation for my users? (They can’t hack XML every time they need a color.) What can I look into? Again, I set the Theme to company colors, but still need this other functionality in an addin for a larger set of colors. Any words of wisdom on that?
I’m sure I could explore code that separately changes colors fill, lines, or fonts for the selected object(s), but then a massive amount of logic would have to be written to inspect and determine the object type, etc. (Which is what made the Recent color strategy so superior.)
Also, I want to do it in Word too if you have any thoughts on that.
Jon Peltier says
What I’ve done is build color chooser dialogs with colors I’ve prepopulated. The colors are not adjustable by the user, but I can set up differents set of colors for different users. It’s easy enough to set up so it applies the color to whatever is the selected object (it doesn’t take a “massive” amount of code).
However, you can add “Custom Colors” to a theme, which are additional colors beyond the so called “Theme Colors” in the built-in color choosers. These appear below the theme colors an another category with the “Standard Colors”. Unfortunately in Excel these can only be used for shapes and chart elements, but those are a major place they are needed.
Eljay says
Thanks, Jon. From experience working with shapes in PPT, I often had to write logic that first determined what was selected, but I’m about to take on doing this in Excel/Word now. Being I can’t just add to the recent colors, I envision 3 drop lists in the Ribbon for Fill, Line, and Font that displays my colors. Perhaps a 4th for cell fill, and then there’s cell border color, etc. (That’s why I found recent colors so much easier.)
I’m sure I’ll have questions when details hit me. What’s the best Excel VBA discussion group these days? Thanks again.
Jon Peltier says
I’ve used a modeless dialog with a color picker and about three buttons, for fill, border, and font. I select an object, pick a color, then click one of the three buttons, and the code figures out what is needed to apply the color to the feature identified by the selection and the clicked button. TypeName(Selection), plus code to color Line or Border, Fill or Interior, and Font. The object selected could be a shape, a chart element, a cell, …
The dialog remains open to allow coloring additional objects, and closes when the user is done.
Eljay says
Sent you an email, Jon. Didn’t want to get too off topic here.
Ernest Ghjuvan says
Hello Jon,
Many thanks for your extremely comprehensive (and very handy) recap!
A great fan of your talent for clarification which makes everything “digestible”.
Mark says
Thanks for the great explanation! However, I’m having some trouble:
When I try to paste the styles.xml file back into the zip folder, I get an error that says, “Windows cannot open the folder. The Compressed (zipped) Folder is invalid.”
If I click OK to that error, remove “.zip” from the file name and open the file, I get another error that says, “Excel found unreadable content in . Do you want to recover the contents of this workbook? If you trust the source of the workbook, click Yes.”
If I click Yes to that error, it appears to recover properly and even correctly includes the edits that I made to the recent colors. However, I can’t take the risk that I might be overlooking something and it didn’t recover completely. I’d rather look at a couple recent colors that I don’t really want to see than take that chance.
Any thoughts?
Note: I just tried again with a new Excel file and it worked just fine (without the errors), so it must have to do with the file I’m using (complex file with VBA custom functions, conditional formatting, etc.).
Mark says
I figured it out:
The file I was trying to modify was saved to my company’s network shared drive. The second file that worked for me was saved to my hard drive. I copied my original file to my hard drive and it worked perfectly. Thanks!
Fred J. Cimo Jr. says
Correction: You DON”T give an example of how to remove ALL Recent Colors!!!
You neglected to say that the ENTIRE section MUST BE REMOVED when ALL (or the LAST) Recent Colors are to be deleted! I found this the HARD WAY!
Also, XML Notepad 2007 make the saved file “pretty” with linefeeds && this causes Excel to generate the readability error!
I aslo use PeaZip to edit the “zip” file & adds the changes back via Drag-n-Drop.
Michael Kiely says
Thanks this is great … I’ve always wanted to be able to do this.
Linda says
Hello!
Very useful Jon!! One quick question… where can I find in PowerPoint XML files?
Thank you!
Jon Peltier says
Linda –
I don’t know where to find this in PowerPoint. I added some colors to a presentation, then unzipped it, but I couldn’t find any sign of the new colors.
If it helps solve an issue in a different way, my colleague Echo Swinford shows how to add “Custom Colors” to PowerPoint (which you can’t add to Excel), in PPT Custom Colors.
Dmerces says
Wonderful tip. Tks
Diana Martin says
Is there a way to do this in Office 365, or is it outdated now? I can’t find the folder you mention anywhere.
Jon Peltier says
Diana –
Add “.zip” to the end of your file name, changing it from (for example)
MyWorkbook.xlsxtoMyWorkbook.xlsx.zip.Open this zip file. You should see several folders, one of them being named
xl.The
styles.xmlfile that you need to edit is in this folder.Jack Henry says
Deleting from colors to colors removed all recent colors with no errors.
William Wei says
My problem is My Recent Colors stays same since some months ago. How can I change it?
William Wei says
where is your RecentColors folder located?
Jon Peltier says
William –
Each workbook contains its own list of recent colors. The article explains: you have to unzip the workbook, look for ‘‘ in the ‘styles.xml’ file that’s found in the ‘xl’ folder. Delete everything between ‘ ‘ and ‘ ‘ to remove the recent colors. Repeat for every workbook as required.
anthony says
Create a new workbook and copy the ‘old’ worksheet into it by right clicking the tab.
Mark Ballard says
Jon,
I’m writing this in May 2022. Wonderful older article here.
Mar 19, 2015 you mentioned “Recent colors is a fragile thing to rely upon, though. I’m working on an article that covers Custom Colors (and also regular colors).”
Did that come to fruition? I’m trying to change Theme colors in Excel (365) and would love your insights.
Thanks for your incredible work. It helps me repeatedly and immensely.
Jon Peltier says
Mark –
I never got to my Custom Colors article. I don’t even see a partially finished draft.
What do you need to know about Theme Colors?