Productivity Portfolio

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 scanning 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. You can use a different background color, font color or border. The goal is to make important cells stand out so you can find them easier.

Excel already does some of this for you. As example when you format cells with numbers, there are options such as displaying negative numbers in red. This is an example of a predefined format.

Click to enlarge

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.

Click to enlarge

Some Cell Value examples might include:

• Apply a red background if the cell value is less than 50
• Apply an italic bold font if the cell 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 formulas is you can reference the values in another cell. In the example below, I’m requesting that if the value in cell F2 is greater than the value in cell H2, apply a green background color to the cell. This can be useful if you like to do comparative analysis as you can color code items that fall above or below certain ranges.

Click to enlarge

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 formatting using the “Cell Value Is” method, you’re allowed up to three conditions. For example, you might want these rules:

Click to enlarge

These conditions seem simple to understand. 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 to the cell. 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 spreadsheet. What are your audiences’ interests and what actions might they take.

To apply conditional formatting to one or more cells,

1. Highlight your cell or range of cells.

2. From the Format menu, select Conditional Formatting…

3. In the Conditional formatting dialog, select Cell Value Is in the first drop-down box.

4. Select a cell evaluator from the second drop down box. Your dialog may change and add another field.

5. Enter your values in the remaining text boxes.

6. Click the Format… button.

7. Choose your format options from the Font, Border and Patterns tabs.

8. Click OK.

9. Click OK or add another condition.

Used appropriately, Excel's conditional formatting can make your spreadsheets more useful. People won’t have to spend extra time looking for important changes or that “needle in the haystack”.

4 Minute Conditional Formatting Tutorial (Uses Excel 2003)

Related Excel Articles