Spreadsheets in full color
There’s no need for spreadsheets to be shades of grey, this is how you can design colorful spreadsheets that stand out
This article is going to give you 5 ways to add more color to your spreadsheets:
- How color can improve data visualizations
- Skipping the default color palettes
- Breaking free from the grid layout
- Customizing charts
- Understanding table styles
Over the last few years I’ve built up a reputation as a data viz “color guy”. It’s a strange thing to be known for but I kind of like it. To me, it represents the overlap between data and design. Spreadsheets are so well known for being bland, grey and boring that it almost feels like an oxymoron to describe them as colorful.
So I’m outlining some of the techniques I use to make spreadsheets that leverage color to be more engaging and more actionable.
Better data visualization with color
Color isn’t all style. It’s also a tool for categorizing and differentiating our data.
Humans have all sorts of visual metaphors used for color, one of the most common being green=good red=bad. We can use colors like green and red as an easy way to signal that a metric is increasing or decreasing.
We also can use colors to categorize metrics. For example in this dashboard each KPI is assigned a color that is kept consistent. It helps us link data together across different sections.
Last we can use color to show polarity or sequence. For example a a gradual change from one color to another can be used as a way to show intensity.
But how do you choose your colors?
Skip the default color palettes
Excel and Google Sheets have a very recognizable set of default colors. Those colors are so well known that people see them and instantly associate them with every other spreadsheet that they’ve seen.
But, if you take a few minutes to come up with your own color palette, it will have a big impact and make your sheets stand out.
I wrote another article that goes into detail about how to select your colors: https://bootcamp.uxdesign.cc/data-visualization-in-full-color-ff6a5fcfb8e
The basic idea is to either borrow colors from your website or powerpoints, use a style guide (if you have one) or use a color palette maker.
Once you have those colors, you need to put them into your spreadsheet. That process is much easier when you change the way you think about the structure of your spreadsheet.
Break free from the grid layout
People often make the mistake of thinking about spreadsheets as nothing but a grid of cells. Each one representing a coordinate (A1, B2, etc.).
But almost all spreadsheet tools also include other visual design features: shapes, images, lines and charts.
When you combine those design elements together, you can create spreadsheets that look more like a powerpoint slide than an Excel sheet.
I start most of my designs using rectangles as my base to block out each section. Then I layer on other elements to start making a cohesive design.
This gives you a lot more opportunities to include colors and imagery like you would if you were building a website or powerpoint presentation.
Customizing charts
Another important skill is getting familiar with the chart customization features in your spreadsheet tool. For Excel there are a ton of different ways to do this but the basics are easy to learn.
You can select individual elements on your chart to style them. My typical workflow includes changing the chart background to “No fill”, updating my label and legend fonts, updating the gridlines on the chart, then selecting each data series and changing the color to match my overall design.
You can also add and edit elements in the upper left under the “Design” tab in Excel.
Charts are a really effective way to add color and to help you summarize your data in a way that is easier to interpret.
One of the easiest ways to learn this skill is to find a spreadsheet file that you like and deconstruct it to understand all the style settings. I offer this to folks in my newsletter by sending out free monthly XLSX files they can use. If you want to get my next free template then you can join here: http://eepurl.com/hZPvKr
Understanding table styles
If you’re stuck using regular old tables in Excel then it can be tough to figure out how to add colors in a way that’s useful.
More importantly, most spreadsheet apps make it pretty challenging to update your table or pivot table designs.
This is a rare situation where it can make sense to use the default color options given by Excel:
If you absolutely need to make your own custom styles, then right click on any of the default color options that is closest to your desired style, click ‘duplicate’ and you’ll be given a menu to update colors.
This gives you a long list of sections that you can manually update. Be aware, this is a tedious process and it can be unpredictable. Test out your settings as you go. It is very unlikely that you’ll get everything right on your first attempt.
There you go! With those 5 tips you should be able to start building spreadsheets that use color in new and visually engaging ways.
If you have any questions then let me know in the comments. I’ll do my best to reply to all of you.
If you found this article helpful please subscribe here on Medium and sign up for our free template newsletter: http://eepurl.com/hZPvKr
If you want my full Excel toolkit you can find it here: https://exceldashboardtemplate.com/
If you’re looking for a service to connect your marketing data to Excel or Google Sheets, then try out Supermetrics.com
