# Make Complex Formulas for Conditional Formatting in Excel

Author:

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. 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)
`````` 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: 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: ### 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. Hit OK and you will see the result: ## Notes

Excel Function: AND(), IF(), ISBLANK(), NOT()

#### Question? Ask it in our Excel Forum ##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

Similar Content on TeachExcel
8 Tips to Become an Expert in Conditional Formatting for Excel
Tutorial: Become a master of Conditional Formatting in Excel! This tutorial covers 8 tips and trick...
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 ...
Make Better IF Statements in Excel - Learn to Convert Complex Formulas into True False Values
Tutorial: How to change any formula to make it return a TRUE or FALSE value - you need this for usi...
The Magically Appearing Cell in Excel
Tutorial: A fun way to make a cell's contents appear when a specific action occurs. This works when...
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...
Tutorial Details
Excel Function: AND(), IF(), ISBLANK(), NOT() 