Conditional formatting is a powerful and useful Excel tool–but not everyone understands how to put it to work. Here are two ways to apply a complex conditional format to accomplish a tricky goal.
www.office.com/setup Blogs: Excel‘s conditional formatting feature confuses a lot of readers. They try, but they simply can’t get all the pieces to work together correctly. Sarina inherited a workbook that stores a contract date with an expected fulfillment date that’s always 60 days out. When the actual fulfillment date falls outside of a three-day window (either way), she wants a conditional format rule to highlight the fulfillment date.
www.office.com/setup Blogs: In this article, I’ll show you how to use an OR() operator to evaluate the two ends of the three-day window and the 60-day conditions. You’ll also see two solutions: one that satisfies the requirements and a second that’s more flexible. To be fair to Sarina, her requirements are more complex than the solution I’m sharing, but the concept is the same.
I’m using Excel 2016 (desktop) on a Windows 10 64-bit system, but this solution will work in earlier versions. For your convenience, you can download the demonstration .xlsx and .xls files. Excel‘s browser version supports existing conditional formatting, but you can’t apply a formulaic rule in the browser.
SEE: Build your Excel skills with these 10 power tips (TechRepublic download)
Accommodating what you have
Sarina has three dates: a date where counting begins, the contract date, and a date where counting might end, a 60-day expected fulfillment date. When the actual fulfillment date is outside the theree-day +/- window based on the expected date, she wants the actual date highlighted. The expected date is implicit—it doesn’t exist in the sheet. Translated into an expression, you end up with the following syntax:
Figure A shows this expression in use. The expressions in columns E and F return the +/- dates for each contract date; you don’t need them, but they help illustrate what’s happening.
A conditional formatting rule highlights dates outside the expected window.
The simple expressions in columns E and F follow:
We could reduce the integers to 57 and 63, but we’re staying true to the specifications. Cutting corners often works, but it can have unexpected consequences that are hard to troubleshoot down the road. There’s a reason for both the 60-day and +/- window, and either or both could change.
To implement the formatting rule, do the following:
- Select D3:D6, the range of actual dates.
- Click the Home tab.
- Choose New Rule from the Conditional Formatting dropdown (in the Styles group).
- In the resulting dialog, click the Use A Formula To Determine Which Cells To Format option in the top pane.
- Enter the formula =OR($D3<($C3+60)-3,$D3>($C3+60)+3) in the lower pane.
- Click Format and specify what you want. For this example, click the Fill tab, choose red, and click OK. Figure B shows the rule and format.
- Click OK to return to the sheet.
Specify a rule and choose a format.
By comparing the results to the dates in columns E and F, it’s easy to see why the rule works as it does. Remember, these two columns aren’t part of the solution, but they do help clarify what’s going on. For example, August 26 falls before the expected window of 60-3; September 25 falls after the expected window of 60+3. The other two dates, September 15 and September 30, fall within the +/- span of the expected date.
This rule works in large part due to the OR() function, which evaluates multiple conditions using the following syntax:
If any condition returns true, the function returns true. OR() returns false only if all conditions are false.
Let’s look at the first record a bit closer. The expected date is August 30, 2017—you don’t see that in the sheet, but the expression accounts for it. Three days before is August 27, 2017, so the first condition in the OR is true: The actual date, August 26, 2017, falls before the first date in the +/- window.
This rule also works with a Table object. As you add new contract records, the rule extends automatically. Now let’s look at a setup that’s more flexible.
SEE: Windows spotlight: 30 tips and tricks for power users (Tech Pro Research)
It’s never that simple
As soon as you get this working, someone will change one of the conditional components. They’ll either change the 60-day expectation rule or they’ll change the +/- rule. For that reason, you might want to consider using input values for those two components and referencing those values in the rule instead of entering the values in the rule.
Figure C shows a slightly modified setup that includes two input values and a new column for the expected date. The expected date expression:
references the contract date values and the expected window value in C1. Currently, C1 is blank, so the expression returns the contract date. The conditional formatting rule references all three dates and the +/- value in C2.
This configuration is more flexible than the first.
There’s one last change: The data range is now a Table object because as I mentioned earlier, a Table automatically applies the conditional format to new records. If your data range is stable, you won’t need to make this change. In addition, remember that Table objects have limits, so it won’t be an appropriate choice for every situation. To convert the data range into a Table, do the following:
- Click anywhere inside the data range.
- Click the Insert tab.
- In the Tables group, click Table.
- The feature does a great job of anticipating your needs. Adjust the range if necessary and check or uncheck the My Table has headers options if necessary. For this example, you shouldn’t need to make any changes, so click OK.
The next step is to add the formatting rule as follows:
- Select E5:E8.
- On the Home tab, choose New Rule from the Conditional Formatting dropdown in the Styles group.
- Click the Use A Formula To Determine Which Cells To Format option in the top pane.
- Enter the formula =OR($E5<($D5-$C$2),$E5>($D5+$C$2)) in the lower pane.
- Click Format and specify what you want. For this example, click the Fill tab, choose red, and click OK. Figure D shows the rule and format.
- Click OK to return to the sheet. Because both input values are empty, the rule formats all four values.
Specify this new rule and a format.
Figure E shows two different results and the only changes are the input values in C1:C2. The expected date values and the formatting update accordingly. This adaptation of the original solution might not be necessary, but it’s easy to implement if it is. It’s definitely more flexible. Once you grasp the concept, conditional formatting is a great tool.
This setup allows you to easily change both conditional values.
If your efforts don’t work, check the expression’s referencing. Each rule uses a mixture of absolute and mixed references, and they matter.
Also published on Medium.