Microsoft

Pro tip: Accommodate changing conditions in Excel's conditional format feature

Conditional formatting adjusts formats automatically as data changes. Learn how to build in a bit of flexibility by referencing those conditions in the sheet instead of in the rule's formula.

Excel's conditional formatting feature allows on-the-fly formatting. For instance, you might highlight records when a due date is the current date — or you might highlight records for a specific project leader. However, if your rule changes, you must update the rule. If this happens frequently, you might need a more flexible solution.

To follow this article, you can work with any simple data set or download the example .xls or .xlsx workbook.

The problem

Ordinarily, conditional formats adjust as you enter and update values. It's easy to allow for expected changes — that's what the feature does! But what happens when the condition, rather than the data, changes? Let's consider a simple data set that highlights records for a project's leader. If the leader changes, you have to update the conditional rules. Updating one rule once isn't a big deal. However, updating these rules frequently or updating several rules at once is tedious!

The simple data set in Figure A is a simplified version of what you might encounter. A simple conditional rule, =$A2="Bill", updates records for the project's leader, Bill. If you change the name, the rule removes the highlight. Here's where things get a bit tricky. When you reassign the record, you want the highlight to disappear, as shown in Figure A. The record no longer belongs to Bill, the project leader. That's how conditional formatting works. On the other hand, if John is the new project leader, you must update the underlying rule because you want the highlight to remain. The highlight belongs to the project leader, not exclusively to Bill or John.

Figure A

Figure A

A conditional rule highlights records for Bill; when you change the name, the highlight disappears.

In a perfect world, you'd simply update the rule, =$A2="John", and go on. When changes occur frequently, you should re-evaluate the setup. The project leader position is the condition, not the name of the person who happens to be the project leader at any given time. Accommodating this changing condition in your data set and subsequently, in your conditional rule, is the best solution. You might do so by adding a column for each person's position on the team and using that column for this particular rule. However, doing so in the real world isn't always an option.

A referencing solution

Try not to get too hung up on the simple and (very) contrived example. The example data set isn't important — the option that I'm offering is. When altering the data set (to include a more stable condition) isn't possible, you can reference the changing condition in the sheet as shown in Figure B.

Figure B

Figure B

Referencing the changing condition in the sheet.

As you can see, I've entered the project leader's name into cell A2. Now, let's enter a rule to match the data in the cell:

  1. Select the data set you want to format — A6:B9.
  2. On the Home tab, click Conditional Formatting in the Styles group. In Excel 2003, choose Conditional Formatting from the Format menu, and skip to step 5.
  3. Choose New Rule from the drop-down list.
  4. In the top pane, choose the Use a formula to determine which cells to format option.
  5. Enter the formula =$A6=$A$2 In Excel 2003, you must choose Formula Is from the drop-down before entering the formula. Match the absolute and relative referencing exactly. You want Excel to consider all of the rows in the selected data set, but the rule should always reference the text you're matching in cell A2.
  6. Click Format. On the Fill tab (Patterns in earlier versions), select a color and click OK. At this point, you can preview your format (Figure C).
  7. Click OK to return to the sheet.

Figure C

Figure C

At this point, you can preview your format.

Most likely, you'll want to delete the original rule by choosing Manage Rules from the Conditional Formatting option. Then, choose This Worksheet from the Show Formatting Rules For drop-down. Select the original rule and then click Delete Rule. If the rule includes other conditions, you'll need to modify it rather than delete it.

Now, you can reassign the record and change the project leader without adjusting the underlying rule. For instance, if you enter John into cell A6, the highlight disappears. Bill is still the project lead and there are no records for him in the data set. If you enter John into A2, the highlight adjusts accordingly, as shown in Figure D. John's record (instead of Bill's) is now highlighted.

Figure D

Figure D

Allow for conditional changes.

Complicating the condition

A more complicated condition doesn't change this solution. Simply find the component that changes, enter the changing value into the sheet, and reference that component in the conditional rule's formula. For instance, let's suppose you want to highlight records where the date in column B is relative to the current date, but the relative factor changes. Sometimes you want to highlight dates that match the current date. You might also want to highlight dates that are a week into the future (or the past). You could assign several conditional highlights, or you can reference the changing value. Figure E shows the necessary adjustment to the sheet — the referencing day component in cell B2.

Figure E

Figure E

Add a cell for the changing day component.

Now, let's add the new rule:

  1. Select the data set you want to format — A6:B9.
  2. On the Home tab, click Conditional Formatting in the Styles group. In Excel 2003, choose Conditional Formatting from the Format menu, and skip to step 5.
  3. Choose New Rule from the drop-down list.
  4. In the top pane, choose the Use a formula to determine which cells to format option.
  5. Enter the formula =$B6=TODAY()+$B$2 Match the absolute and relative referencing exactly. If you're using Excel 2003, don't forget to choose Formula Is from the drop-down so you can enter the formula.
  6. Click Format. On the Fill tab (or Pattern tab in earlier versions), select a color and click OK. At this point, you can preview your format (Figure F).
  7. Click OK to return to the sheet.

Figure F

Figure F

Create a new formatting rule.

Figure G shows the resulting conditional format. As you can see, the highlight is dependent on the value in cell B2. Five days from August 10 is August 15. Similarly, the day before August 10 is August 9, and the conditional format updates accordingly. (If you're working with the downloadable example file, keep in mind that the current date won't be the same as the day I shot these figures, so the format will adjust accordingly.)

Figure G

Figure G

A highlight based on a changing number of days.

I added the TODAY() function to B4 so you could see the actual date. The technique doesn't require this function; it's a visual convenience.

Accommodating change and adding flexibility

If your conditional update is a one-time change, you don't need to adjust your sheet — just update the rule. If the condition changes frequently, work those updates into your sheet to add a bit of flexibility. Changing conditions can easily be accommodated this way when using Excel's conditional formatting feature.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For instance, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

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