Make Reports and Spreadsheets More Dynamic with Conditional Formatting
Published: October 6, 2015
Author: Oliver Eldredge
Excel spreadsheets and reports can overwhelm users with a sea of data, but not if you know how to apply tricks and visuals with Conditional Formatting to call attention to the right numbers. In this post, we’ll walk you through some basics and some advanced techniques to help your reports pop.
Conditional Formatting has default options that are functional and require almost no work to set up. It is also a feature that benefits greatly from a little bit of technique and style. We’ll start with the basics and then introduce some ideas for bringing your conditional formatting implementations to the next level.
The easiest method for setting up conditional formatting is to highlight the cells that you want to enable conditional formatting for, and then clicking on the conditional formatting drop down in the Home ribbon. From there you will find several options, but to get started we’ll focus on three options that stand out and are also instructive:
At this point if you simply select the formatting that you want from the available options there’s a pretty good chance that you’ll get the style of conditional formatting that you wanted, but that it won’t be calibrated correctly. If you chose red, yellow, and green icons, you may have found them oriented backwards with red where you wanted green. If you chose a color scale, you may not have the right colors associated with the right values. To tune things up you’ll want to select the cells in question and the click on “manage rules” under the conditional formatting button.
Editing Icons, Data Bars, and Color Schemes
The “manage rules” button is where you can go to tune up how conditional formatting rules are set up. After clicking on this button, you’ll be able to choose between seeing the rules for the currently selected cells, or all of the rules for the entire workbook.
If you select a rule and click on the “edit rule” button, you will then be able to tune up exactly how that rule is governed. A few interesting options available include reversing the order of icons, the direction of a data bar, or the colors available in a color scale. There are also several options available to change how the scales themselves are calibrated. They are all fairly intuitive and are easy to figure out with just a little tinkering.
One option that is worth calling out is the show icon/bar only buttons. These will cause Excel to only display the conditional format (icon or data bar) and not show the actual contents of the cell. We’ll take a look at this later, as it allows you to customize things a little bit if you combine it with a few simple formulas. You can also mix and match different icons, and exclude having them show up at all (see screenshot below).
Editing Options for Icons and Data Bars
Additional Rules for Conditional Formatting
There are several other options to choose from in addition to Icons, Data Bars, and Color Schemes. Most of these are fairly self-explanatory. You can easily set up rules to format cells that contain certain values, are the top/bottom ranked values, are above/below average, and contain unique/duplicate values.
Other options available:
Using Formulas within Conditional Formatting
The final option on that previous list was to use formulas to determine which cells to format. This allows you to write a formula that determines if a cell will receive a special format or not. These formulas will have to resolve to true/false, and when they pass a true value, your desired format will be used on that cell. A lot of creativity can go into these.
In the example below we’re comparing the amount of spend in our back-end data worksheet to the amount of spend that we’re reporting on in a total cell in our dashboard. This will alert us to a situation where our dashboard has failed to account for all of the data that we’ve entered into our report.
Another advantage of the formulas is that they can reference other cells besides the one that will receive the conditional formatting. In the example below, we demonstrate a technique where we use several cells within the A column to plug different tests into the spreadsheet itself. We then use a simple formula within the conditional formatting to reference to collective result of those tests. This will cause us to get an alerting format if any of the conditions described within our spreadsheet have been met.
Using cells in a spreadsheet to aggregate different criteria, and then using the conditional formatting:
Mixing and Matching Techniques
Combining some of these techniques can open new doors for conditional formatting. In our basic example, we were limited to having formats that were governed by the values within the cell itself.
In the below example, we’re controlling icons with formulas in the cells that analyze other factors (spend vs. budget). We use an IF statement to deliver a numeric value of one, two, or three. Then we configure the settings within conditional formatting to basically customize how the icons will work. This all happens in a column that’s designated for the conditionally formatted icons with the “show icon only” button checked. We’re also using a simple data bar with the show bar only button checked.
An example of combining formulas within a cell, and conditional formatting for the icons, and simple conditional formatting for the data bars:
The last example we’ll look at is using conditional formatting to add light grey highlighting to the row that corresponds to a current selection from a drop-down menu. This offers a huge advantage over manually formatting every other row because the conditional formatting automatically adjusts itself when you delete rows.
Using conditional formatting to highlight rows:
Advanced Conditional Formatting
Practice using different cells and formulas to control your own conditional formatting. You’ll find that you can get results that go way beyond the simple functionality that the basic options provide. A few examples to challenge yourself with are creating every-other-row highlighting, or creating a checkerboard by highlighting every other cell (hint: use =mod(), =rows(), and =columns()).
(Stumped? Find your answer on page 596.)
There are a lot of creative examples out there if you search around. Have fun adding some functional flair to your next workbook!