Walk into most offices and you’re apt to find someone using an Excel spreadsheet. It’s one of those common business tools. The problem with many spreadsheets is they make the reader find the key information rather than using Excel to highlight these cells. Fortunately, Excel’s conditional formatting can make your data automatically stand out. (Check Resource section at bottom for video tutorial.)
I see it happen all the time. You get a spreadsheet from someone with rows of data and it all looks the same. But is the data the same? Are there cell values that are different from the rest? Is something outside the norm? These are the type of questions Excel users have when they see spreadsheets.
Formatting Excel Cells Based on Conditions
Instead of having the reader scan each cell, you can have Excel do some of the legwork by using conditional formatting. This allows Excel to apply a defined format to cells that meet specific criteria. These formats might include a different background color, font color or border. The goal is to make important information stand out so you can find them easier.
Excel already does some of this for you. As example when you format numbers, there are options such as displaying negative numbers in red. This is an example of a predefined format.
Excel allows you to use formatting on individual cells or rows. There are two methods involved. The simplest method is to have Excel apply the conditional formatting if the cell meets a certain criteria. This uses the “Cell Value Is” method.
Some Cell Value examples include:
- Apply a red background if the value is less than 50
- Apply an italic bold font if the value is between 70 and 90
- Apply a green font color if the value is Montana
Excel also allows you to use formulas for conditional formatting. One benefit to excel formulas is you can reference the values in another cell. In the example below, I’m requesting that if the value in F2 is greater than the value in H2, apply a green background color. This can be useful if you like to do comparative analysis as you can color code items that fall above or below certain ranges.
You don’t need to apply the formatting to one cell. You could apply conditional formatting to a row or column. The only difference is you highlight multiple cells instead of one when you start the process.
The Order of your Conditions Matters
When you apply the conditional formatting using the “Cell Value Is” method, you’re allowed up to three conditions. For example, the screen snap below shows three rules based on cell values and colors.
- Cell value < 100 format for green background
- Cell value < 50 format for yellow background
- Cell value < 10 format for red background
These formatting conditions seem simple. You’re probably thinking that if you have a cell with a value of 10, it would show with a red background since it is less than 25. Sorry, the value of “10” will produce a green background.
The reason the green background would display is that Excel takes the value of “10” and applies it to Condition 1 to see if it is true. Since 10 is less than 100, the condition is met and the green background is applied. Conditions 2 and 3 are not evaluated. Excel stops when it meets a true condition.
There are two ways to fix the problem. One way is to reverse the order of the conditions so the most restrictive is at the top. For instance, have Condition 1 be Cell Value Is less than 25. A second way would be to change the “less than” criteria to “between” and state the ranges for each of the conditions.
How to Apply Conditional Formatting
Adding these formatting rules is easy. The hardest part is finding out what you want to emphasize on your Excel spreadsheet. What are your audiences’ interests and what actions might they take.
To apply conditional formatting to one or more cells,
- Highlight your cell or range of cells.
- From the Format menu, select Conditional Formatting…
- In the Conditional formatting dialog, select Cell Value Is in the first drop-down box.
- Select a cell evaluator from the second drop down box. Your dialog may change and add another field.
- Enter your values in the remaining text boxes.
- Click the Format… button.
- Choose your format options from the Font, Border and Patterns tabs.
- Click OK.
- Click OK or add another condition.
Excel’s conditional formatting can make your spreadsheets more useful if used appropriately. People won’t waste time looking for important changes or that “needle in the haystack”.
