Make Complex Formulas for Conditional Formatting in Excel

Add to Favorites

How to make complex formulas for conditional formatting rules in Excel. This will serve as a guide to help you build the formulas that you need for conditional formatting custom rules.

Sections:

Make a Formula for Conditional Formatting

Making Complex Formulas

Notes

Make a Formula for Conditional Formatting

Every formula that you use for conditional formatting must evaluate to True or False. That means that if you put that formula into a worksheet, the output would be True or False. This is the most important aspect of making conditional formulas.

Once you have a formula that you want to enter, select the desired cell and go to the Home tab > Conditional Formatting > New Rule > select Use a formula to determine which cells to format and then enter the desired formula in the input box and click the Format button to choose what the cell will look like if the formula evaluates to True.

f770838fc8438c75578256a24254f6db.png

Here, I am using a simple function to see if the cell is blank or not:

=ISBLANK($A$1)

This is a simple formula and I just typed it into this window, but, for complex formulas, follow the guide in the next section.

Making Complex Formulas

Complex formulas should always be made and tested in the worksheet before you put them into a conditional formatting rule.

This is because the place where you input formulas for conditional formatting does not check the formula for errors and you cannot troubleshoot issues.

When you create the formula in the worksheet, you can visually see that the correct cells are referenced, check the output of the formula under different conditions, and basically verify that they work correctly.

Note: all formulas used for conditional formatting must evaluate to True or False.

Here is our sample formula:

=IF(AND(ISBLANK(A2),NOT(ISBLANK(A1))),TRUE,FALSE)

6c02f11736f0537d8869003faa4ec4d6.png

Though it's not terribly complex, you can see that the syntax is highlighted and the cells involved in the formula are also highlighted and it is generally easy to read and understand.

Also, when I hit Enter, you can see the output and ensure that it is correct:

3f5c7f9362bc6db083c8942a8bb0aa16.png

While the formula is here in the worksheet I can run formula auditing on it to ensure that it is working exactly the way that I want it to work.

Go to the Formulas tab and the Formula Auditing section to see these tools:

3503f6887b6df969627a06f855d7ad36.jpg

Insert Formula into Conditional Formatting

Once you have a tested and working formula, put it into the Conditional Formatting feature.

Select the desired cell and then go to the Home tab > Conditional Formatting > New Rule > select Use a formula to determine which cells to format and then paste the formula into the input box and click the Format button to choose what the cell will look like when the formula evaluates to True.

5b21c2f4efc5654e9adb26c9c8387792.png

Hit OK and you will see the result:

83d75adfc30f7da173a8f8051572c6c3.png

Notes

Do not hit any of the arrow keys to move the cursor within the formula input area for Conditional Formatting; doing this will cause cell references to be input into the formula and it is a real pain to remove them. Follow the instructions above and you will avoid all major issues when it comes to using formulas with Conditional Formatting in Excel.

I left the formula for the last example in the sample file so it is easier for you to see but, in practice, you should remove it once you add it to Conditional Formatting.

Make sure to download the sample file for this tutorial so you can work with the above examples in Excel.


Excel Function: AND(), IF(), ISBLANK(), NOT()
Downloadable Files: Excel File

Similar Content on TeachExcel
Apply Conditional Formatting to Multiple Cells with a Single Formula
Tutorial: How to use a single formula to apply conditional formatting to multiple cells at once in E...
Format Cells in The Number (Numerical) Number Format in Excel
Macro: This free Excel macro formats selected cells in the Number or Numerical number format in E...
Format Cells in The General (default) Format in Excel Number Formatting
Macro: This free Excel macro formats a selected cell or range of cells to the General number form...
Quickly Clear all Formatting in Excel
Tutorial: How to remove all formatting from cells at once in Excel. This includes removing any and...
Format Cells in The Short Date Number Format in Excel
Macro: This free Excel macro applies the Short Date number format to a selection of cells in ...
Format Cells in The Long Date Number Format in Excel
Macro: This free Excel macro formats a selection of cells in the Long Date number format in Excel...