Choosing and implementing color palettes is a difficult task in visualizing data. Color can be used to highlight and draw attention, but also to confuse. It can be used to make things seem drab or to make them “pop”. As Maureen Stone once famously wrote, “Color used poorly will obscure, muddle and confuse.”
Using color in some of the default tools such as Excel and Tableau will yield visualizations that look just like everything else. For me, using those default colors suggests a level of laziness—that the creator couldn’t bother to consider alternative colors to better display the data or convey their argument.
But how do you choose alternative color palettes? If you’re not a designer (like me), this might seem an impossible task. Fortunately, there are lots of creative people out there willing to provide you with samples, or at least a start. For example, in two posts from Canva last year, they provided over 150 different color palettes derived from and photos and “impactful websites”. (In my opinion, Canva’s “Design School” provides some of the best beginner design work out there, which is why I’ve included them in my list of Design Resources.)
At the beginning of this year, Pablo Sáenz de Tejada turned all of those color palettes into a free, downloadable text file you can use to input them into Tableau. In turn, I’ve taken Pablo’s color palettes (thanks again, Pablo!) and converted them into 150 files you can input into Excel for your own use.
How I Created Them
In case you don’t know, every modern version of Excel contains more than 50 alternative color palettes to the default Excel colors. In the left side of the Page Layout tab (and this applies to Excel 2010, 2013, and 2016 on PCs; Macs are a little different and I explain this in a Note below), there is a Themes group, which, among others options, contains a Colors dropdown menu (the images here are all from Excel 2013).
Selecting any of these alternatives changes the default colors applied in your Excel workbook. You may also notice at the very bottom that there is a Customize Colors… option. Selecting that option allows you to manually change each of the colors in the palette that you can save in an XML file format.
Instead of creating 150 palettes manually by clicking and typing in each color code, I took Pablo’s text file and created 150 separate XML files that you can input directly into Excel. (Excel requires 6 colors for each palette, plus a Background and Text color), but because the Canva palettes only have 4 colors, I added black and a gray to the 5th and 6th colors. If you want to change those colors, see the Note at the end.)
(Note: I find it interesting, actually, that to create a custom palette manually in Excel you need to use the RGB or HSL color models, but in the direct-input approach, you can use the HEX color model. Fortunately, this is the format Canva and Pablo provided, so I didn’t need to do any converting. I’d be curious to know why that is—if any of you Excel wizards know, please drop me a line.)
How You Use Them
To input the palettes into your own version of Excel, all you need to do is place the XML file in the appropriate folder. At least on Windows 7 computers, this Theme Colors folder can be found here:
C:\Users\USERNAME\AppData\Roaming\Microsoft\Templates\Document Themes\Theme Colors.
If that doesn’t work, you need to find the folder manually. I find the best way is to pretend you are going to save a new Theme and then copy the folder location. So, in that same Themes group, use the drop-down menu under Themes and select Save Current Theme at the very bottom. The folder location that will subsequently open should contain the Theme Colors folder—copy the folder location and drop the color palette(s) you want to use in there.
When you open the Colors dropdown in Excel, all of the color palettes you copied to that Theme Colors folder will appear, as you can see in this side-by-side screenshot.
I hope this is helpful and you find some good colors to use. Be sure to browse the Canva posts to find a palette you like and then install the XML file. You can download a zip file with all of the color palettes below; just unzip and use those you like best.
Note 1: Macs
On the Mac OS, the process is slightly different. For whatever reason, you need to add the Theme Color file through PowerPoint. You basically follow the same procedure described above, but go through PowerPoint and the color palette will then appear in Excel.
Note 2: Changing the Palettes
As I noted above, Excel requires 6 colors for each palette and because Canva provided 4 colors, I simply added a black and gray to each. If you want to change the black and gray or any of the other colors, or you want to create your own palettes, here’s how you do so.
Open any of these XML files in a text editor (I prefer TextWrangler for this purpose). The code looks like this:
Notice the name of the palette on line 3 (and is in quotes). Change this if you like and save the file (with the .xml extension) with that new palette name.
To change any of the colors, simply replace the HEX codes; for example, the first fill color code in this palette is on line 7 and the code is ‘c06014’. The black color palette is on line 11 (000000) and the gray on line 12 (8A8A8A8A). Note that you don’t need to include the hashtag in front of the code as is usually the case. (If you have an RGB palette that you need to convert to HEX, you can use a converter like this one from COLORRRS.) Save the file with the new name of the palette to the Theme Colors menu as described above.