Most folks want visual clues to make things easier to read and to comprehend. Our brains can identify and process visual clues many times faster than they can process text or numeric information. Using colors in spreadsheets can not only help us navigate, they can also facilitate comprehension.
There are a lot of different techniques you can use to make your spreadsheets more user friendly. Here are just four examples of how you can use color to improve your spreadsheets and make them easier to navigate and to use.
Color Your Tabs
On workbooks with multiple sheets, use different colors to help guide people to different sheets. Colors help distinguish the tabs and what information is on each.
Should you wish to change the colors of your tabs, simply right-click on a tab, select the Tab Color option, and select the color you prefer:
A consistent color scheme means that people will have an easier time navigating workbooks, even if they have never seen it before. So, if it’s just for you or if it’s a company-wide policy, come up with a consistent color scheme and use it.
Color Your Cells
Using a specific color to identify cells where users are expected to enter data, is very helpful. Conversely, use color or shading to indicate which cells are calculated, or are not to be edited by users. [Cells with formulas may also benefit from being “protected”, but we’ll save that topic for another day…]
Conditional Formatting
There are a lot of different options for using Conditional formatting to highlight certain information for the user. It is called Conditional Formatting because the formatting of the cell (the font size, the font color, the fill color, etc.) can all be set to change if a certain “condition” is met. So, you can set up conditions that act as “triggers” such as when the value of a cell is greater than 50. (Or turn all the cells for three-bedroom houses valued less than $50,000 green.) This is done by editing the Conditional Formatting Rules and formatting the cells based on a formula:
Rather than setting this up on a cell by cell basis, an easier way to do it is to highlight a range and then just select a color scheme (colored scales in this example) to apply. The colors of the cells will be determined based on the contents of the cell and based on the value relative to all the other cells selected. There are different patterns to choose from depending on your data and what it is that you are trying to draw attention to. Feel free to play with this. Excel’s “live preview” means that you can see what it will look like just by hovering over the selection without even selecting it.
In this example, the lowest numbers are deeper red and the higher numbers are brighter green.
You can get even more visual by using “data bars” to show the relative size of the numbers:
Any of these tricks can help users better understand the values (comparative or absolute) at which they are looking.
Colors as Filters
Although colored tabs and colored cells are handy for navigation, and conditional formatting can be used to automatically highlight certain information for the user, there is another great trick you can use to direct the spreadsheet user to specific information.
Filters are helpful because they enable you to see only the relevant part of a long list, enabling you to focus only on the rows that are important. Well, in addition to text and numbers, you can also filter that long list based on colors. So, if you are using Conditional Formatting to color the cells whose value is greater than or equal to 50 yellow, you can then apply a filter to see only those cells that are yellow:
Filters are a great way to look at your data and this is another way that they can be helpful.
So take advantage of your brain’s ability to quickly process visual information. You can identify shapes and colors and other visual factors in a fraction of the time it takes to read and process numbers or text. Use color to make your spreadsheets easier to navigate and to understand.