Using Conditional Formatting: If Statements in Excel

*Steps in this article will apply to Excel 2007-2016. Images were taken using Excel 2016.

If you are a fan of Excel’s conditional formatting feature, you probably find looking for even more and more ways to highlight useful information in your data. A question that often comes up among these “conditional formatting addicts” is Can I use If/Then formula to format a cell?

The answer is yes and no. Any conditional formatting argument must generate a TRUE result, meaning that at a literal level, your conditional formatting rule is an If/Then statement along the lines of “If this condition is TRUE, THEN format the cell this way”.

What conditional formatting can’t do in a single rule is an IF/THEN/ELSE condition such as “If # is greater than 10 format red, else format green”. Instead, this would require TWO rules, one for “greater than 10” and one for “less than 10”.

Let’s look at a few scenarios to get a sense of how we can create the effect of IF/THEN conditional formatting, even if we can’t use it in the feature itself:

To follow using our examples, download 04-If-Then Conditional Formatting.xlsx

Scenario 1 (Birthdays tab): You want to highlight all employees in your department who have a birthday this month with Red, and all other departments blue.

Solution: Create two rules – one for your department, one for all others

Step 1 – Highlight birthdays in your department

The formula to identify birthdays in the current month will be (see this article for more about using dates in conditional formatting):

=MONTH(C2)=MONTH(TODAY())

To create a formula that generates a TRUE/FALSE statement that highlights birthdays only in one department, you would use the formula:

=AND(MONTH(C2)=MONTH(TODAY()),D2=”Sales”)

If-Then Conditional Formatting Screenshot

This example was created in April, so April birthdays will be highlighted. If you are reproducing the exercise in a different month, you will see different results!

Then, create a second rule for the same range using this formula to highlight birthdays that are not in your department:

=AND(MONTH(C1)=MONTH(TODAY()),D1<>”Sales”)

If-Then Conditional FormattingIf-Then Conditional Formatting Screenshot

BONUS!

In this example, we applied the rule to the department cell to show the relationship to the formula. By changing the Applies to range, however, you can easily highlight a different cell – such as the birthdate – or the entire row. See Get the Most Out of Excel’s Conditional Formatting for more ideas.

Scenario 2 (Retainers tab): You have a table of how many hours your employees have worked for specific clients, and you have a table of how many hours each client has in their retainer budget. You want to highlight the clients who are over their retainer.

Solution 1: Create a helper column using IF/THEN formula to call out whether a client is over their retainer budget. If your worksheet already has the IF/THEN/ELSE logic you need embedded in a cell, Conditional Formatting can act based on those results. You don’t necessarily need to reproduce the logic in the rule itself.

In this example, we already have an IF/THEN formula that returns the result “YES” if our client is over their retainer budget. Our Conditional Formatting rule, then only has to look for the text string “YES” and apply the formatting when true.

Highlight the cell range, Click on Conditional Formatting > Highlight Cell Rules > Text that Contains to create the Rule, then type YES in the Text that Contains dialog box.

Solution 2: Create a formula to calculate retainer budget.

If you don’t have, or don’t want to create, a helper column with an IF/THEN statement, you can use the same method as the first scenario by creating a rule that determines whether a client is over budget. In this example, we applied the rule to the Client cells and the formula would be:

=(F8-G8)<0

If-Then Conditional Formatting sceenshot

If you are used to creating complex formulas that cover all cases in one cell, it may take a little re-learning to figure out the approach for conditional formatting that works more incrementally. The best hint is to remember that you can apply multiple rules to the same cells – break up your formatting criteria into separate steps, and you’ll most likely be able to get where you need to be!