Microsoft

Three tips for using Excel's conditional formatting more efficiently

These three tips will help you work more efficiently when using Excel's conditional formatting feature.

Instructions and figures are for Office 2010. Instructions for 2003 and 2013 are given when significantly different.

Conditional formatting is a huge feature and with the new 2007/2010 rules it's easier than ever to implement. Even with the newer predefined rules, you'll need to create your own rules occasionally - many of them based on formulas. When doing so, you might work harder than necessary, if you don't know these three tips:

  • Use OR() to combine criteria into a single rule.
  • Copy rules by using appropriate referencing.
  • Apply a single rule to a contiguous range.

Combine conditions

Occasionally, you'll want to apply different conditions to the same range. You can do so by applying two completely separate rules; when possible, consider combining criteria using the OR() function. For example, we'll use an OR() function to highlight values that are greater than $5,000 and less than $1,500 in the sheet below as follows:

  1. Select the range. In this case, that's B3:B14.
  2. Click the Home tab (if necessary) and from the Conditional Formatting dropdown (in the Styles group), choose New Rule. In Excel 2003, choose Conditional Formatting from the Format menu.
  3. Choose the Use A Formula To Determine Which Cells To Format option from the upper pane. In Excel 2003, choose Formula Is from the first dropdown.
  4. Enter =OR($B3>5000,$B3<1500) in the formula control.
  5. Click Format and then click the Fill tab. Click the Fill tab in Excel 2003.
  6. Choose a color from the palette. (I chose light blue.)
  7. Click OK twice.

The OR() function evaluates similarly to an OR operator and accepts several expressions in the form

=OR(<em>cond1</em>, <em>cond2</em>[, <em>cond </em>n…])

Each cond expression must return TRUE or FALSE. The function returns TRUE if any of the cond arguments are true and returns FALSE only when all cond expressions return FALSE. Using the same format for each condition won't always work but when it does, combine them into one rule.

Copy rules

Setting up a conditional format rule can take a few tries to get things right. Applying the same rule to several ranges would be tedious work. Fortunately, it's easy to copy a conditional format. Initially, try Format Painter. If you don't get the desired results, use Paste Special.

We can illustrate how to copy a conditional format by copying the one we created above to column C of the same sheet as follows:

  1. Select B3, the formatting source, and press [Ctrl]+C.
  2. Select the destination range, which in this case is C3:C14.
  3. Don't press [Ctrl]+V to copy values. Instead, click the Home tab (if necessary) and then choose Formatting (R) from the Other Paste Options section of the Paste dropdown (in the Clipboard group). In Excel 2003, choose Paste Special from the Edit menu, and check Formats in the Paste section.

Unfortunately, the copied format doesn't work as expected. It's formatting the cells adjacent to column B. There's your clue -can you guess the problem? We used an absolute column reference in the conditional rule's OR() function so the copied rule continues to refer to column B. Okay, I apologize. I walked you into a mistake on purpose, but there's a good reason.

Now we can discuss cell references in conditional formats. It's common to find them using absolute references. Sometimes, they're necessary. When they're not, don't use them - for exactly the reason you just experienced. You can't copy them!

You can easily fix this problem by updating column B's rule. Either delete it and recreate it using a relative reference (B3) or open the existing rule and edit it appropriately. Then, repeat the steps above to copy the formatting rule.

One more thing before you do: you can copy a formula rule that uses relative references to multiple columns. Instead of copying the rule to only column C, copy it to columns C, D, and E by selecting C3:E14 as the paste range before choosing the Formatting (R) Paste option.

One rule to rule them all

You can omit the copy task if you know that you'll be applying the same rule to multiple columns or rows. Simply format the entire range at once. Doing so is more efficient and the resulting rule will be easier to manage.

The trick? There's no trick. Simply select the range entering the relative referencing formula as you would for a single column. Remember that the top-left cell in the range is the anchor to the entire rule. In the case of this example, you'd do the following:

  1. Select B3:E14. (n the first example, you selected only column B, B3:B14.)
  2. Click the Home tab (if necessary). From the Conditional Formatting dropdown (in the Styles group), choose New Rule. In Excel 2003, choose Conditional Formatting from the Edit menu.
  3. Choose the Use A Formula To Determine Which Cells To Format option from the upper pane. In Excel 2003, choose Formula Is from the first dropdown.
  4. In the formula control, enter =OR(B3>5000,B3<1500). Notice the absence of the $ character - you're using a relative reference.
  5. Click Format and then click the Fill tab. Click Fill in Excel 2003.
  6. Choose a color from the palette. (I chose light blue.)
  7. Click OK twice.

The result is the same, but this more efficient method requires only one rule.

Efficient and manageable rules

When applying conditional formats, use as few as possible. When applying multiple conditions to the same range, use the OR() function to express all the criteria in a single rule, if possible. If you have an existing rule, you can quickly copy it. When using the same rule across multiple columns or row, apply the rule only once; don't apply the same rule individually to each column or row.

One rule is far superior to several. Your workbook will perform better and one rule is easier to maintain than several. When things change - and they will - you update just one rule!

Demo Excel files are available for your convenience.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks