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.

6 comments
rameshvnkl
rameshvnkl

Hello madam Thanks for your article & it is useful. I want to know how to format the characters and numbers inside a cell and not the entire cell. Is it possible and if yes let me know to the following address

malramnkl@gmail.com


isoares
isoares

Great use of conditional formatting ! Can someone let me know how I can find out if a formula has been overwritten by a value. I know it can be done using the "Go To- Special - Formulas" option, but I would rather automate using a conditional format to check all cells and format those that do not have a formula in them. Thanks

BeeVee222
BeeVee222

Nice article Susan. I use conditional formatting intensively to automatically highlight many different things in many different columns in a template file for event management that will typically be something like 50 columns and about 250 rows. During the lifespan of a project, rows will be deleted, added, copied and pasted. The issue is that Excel automatically copies the conditional formats and/or breaks up the range in a rule into multiple sub-ranges. Two consequences: inevitably at some point the formatting behaviour becomes erratic and the rules need cleaning up. This (or simply the need to change some of the rules) then leads to a second consequence... Say I have, even using the OR functions (which I frequently do), 5 rules in a single column or range, when I select the column and click on manage rules I see that there is often a massive repeat of the various rules, all for only 1 or a few rows, and that the original column range has been split into sub-ranges. I now must clean up a long list of recurring rules. The challenge here is that one cannot select multiple lines in the same rule to delete these in one go. You can only select one line at the time and hit delete, which takes really forever. You can sit there clicking delete for several minutes. Deleting them all and start over again would be quicker but many of the rules were tricky to get right in the first place, so it is safer not to start all over again. Does anyone have experience with solving what looks like a shortcoming in the functionality of Excel's conditional formatting? Bruno

sh10453
sh10453

Always great and useful tips. Thanks Susan.

Full Tao-er
Full Tao-er

The sheets I inherit from others tend to be the worst, though I've revisited some of my own and found issues, too. One spreadsheet came to me with numerous rules that were all the same, but for various ranges. As I looked at the rules and the structure, it became apparent the data being formatted was contiguous and one rule would cover the entire range. The next time I open some of my spreadsheets, I'll have to look if the OR() tip can be applied. I had not thought of using it in conditional formatting before. Thanks for that one, Susan!

ssharkins
ssharkins

OR is a great efficiency tool combined with conditional formatting -- hope you can put it to good use!