Conditional formatting is a hidden gem in Excel. It’s fairly easy to use, works well, and doesn’t get in your way. Here’s how we use it to assist in data exploration.
Conditional formatting is found in the Format menu. Most people are probably familiar with using it this way:
These settings would make any cells with a value greater than 30 have a bright red background. So far, so good.
However, you can do many more powerful things by setting the condition to a formula that is either true or false. For instance, in the dashboard that I created to show the in-cell bar charts, I use a formula like this:
This formula compares the value in the cell to the 75th and 25th percentiles of the data in the cells column. If the value is greater than the 75th percentile, use a green font color, if the value is less than the 25th percentile, then use a red font color. The percentiles update automatically with your data.
To get this to work, you need to be mindful of your relative and absolute references. The cell reference on the left hand side of the formula is relative, but the percentile column reference is absolute in rows. This allows you to copy this formula around while maintaining the appropriate ranges. When you start entering formulas for conditional formats, Excel defaults to use absolute references, which doesn’t give you the flexibility you need. You’ll have to edit the formulas by hand.
Here’s another useful case. Sometimes you need to shade alternate rows to make a table more readable. But if someone sorts the table, your shading sorts too. Conditional formatting is an elegant solution and it doesn’t even mess with the relative/absolute cell references.
The formula =ROW()=EVEN(ROW()) is easy to understand once you know what these obscure functions do. =ROW() returns the row of the current cell. =EVEN(...) rounds some number to an even number. Thus, if ROW() is an even number, the formula is true, and it’s false on odd numbered rows.
This last trick is what I use most often. Sometimes you have a very large amount of data that is divided into records with multiple rows per records. Consider something like sales records per customer. You’d like to break this data into chunks—by customer, for instance—as you scan through it.
This conditional format puts a line below each row whenever a particular column value changes.
The function is quite simple. If column D has changed, then place a line above the row. However, you do have to be quite careful about relative vs. absolute formatting.
Here is a spreadsheet that contains all the examples discussed above for you to explore.