Excel Conditional Formatting If: How Rules Work

In Excel, conditional formatting applies a format when a cell or formula meets a condition—in effect, an “if this, then format it” rule. For a simple test, use Home > Conditional Formatting and choose a built-in rule; for more control, create a formula rule that returns TRUE or FALSE.

What “if” means in conditional formatting

Conditional formatting changes how cells look when specified conditions are met. You might highlight overdue tasks, flag sales below a target, or shade an entire row when its status is “Complete.” The underlying cell values do not change: the formatting is a visual signal layered over the worksheet.

Excel offers two broad ways to express the “if.” Built-in rules let you pick a condition from a menu, such as “greater than” or “text that contains.” Formula rules let you write a logical test yourself. Both approaches evaluate a condition and apply a chosen format when it is true.

A common point of confusion is whether every rule needs the IF function. It does not. The formula in a formula-based rule can directly return TRUE or FALSE. An IF function can be useful when a formula needs to choose between outcomes, but using IF just to return TRUE for a condition and FALSE otherwise is usually unnecessary.

Choose a built-in rule for a simple condition

For straightforward comparisons, a built-in rule is often quicker and easier to maintain than a custom formula. In Excel for Microsoft 365 on Windows, select the cells, then use Home > Conditional Formatting. Choose the relevant option, such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, or Icon Sets, and complete the dialog.

For example, to highlight values below a target, select the relevant number cells and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter the threshold and pick a format. Excel creates and applies the rule without requiring you to write a formula.

Built-in rules are a good fit when the condition concerns the selected cells themselves. Formula rules are more useful when the decision depends on another column, multiple tests, or a relationship between cells. You can also edit a built-in rule later through Home > Conditional Formatting > Manage Rules.

Create a formula-based conditional formatting rule

To create a custom rule in Excel for Microsoft 365 on Windows:

Select the cells the formatting should affect. For a row-based rule, select the full block of rows and columns you want to format.
Choose Home > Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
Enter a formula that evaluates to TRUE or FALSE for the first cell in the selected range.
Choose Format, set the font, fill, border, or number format, and select OK.

In the New Rule dialog, the formula does not need to be wrapped in an IF function. Excel evaluates it for each cell in the rule’s Applies to range, adjusting relative references as it moves. The selection’s top-left cell is important: write references as if the rule were being tested for that cell.

For the example below, assume the values to check are in C2:C100. Apply the rule to C2:C100 and enter =C2<70. This highlights values below 70; a value of 65 meets the condition, while 70 does not. The reference C2 is relative, so Excel checks C3 for the next row, then C4, and so on.

Use IF, AND, and OR for more involved tests

Formula rules can test conditions that built-in menus cannot express directly. For instance, a rule may depend on a status in one column and a date or amount in another. Excel’s AND and OR functions combine logical tests: AND is true only when all its tests are true, while OR is true when at least one is true.

Suppose a task list has descriptions in A2:A100, due dates in B2:B100, and task status in C2:C100. To highlight overdue tasks that are not marked Complete, select the rows you want formatted and use this formula, with the range beginning in row 2: =AND($B2<TODAY(),$C2<>"Complete"). The dollar signs keep the tests in columns B and C as the rule moves across a row, while the row number changes for each task. If the task is not complete and its due date is before today, the formula returns TRUE.

To require two conditions, such as a high amount and an Open status, assume amounts are in C2:C100 and statuses in D2:D100. Apply the rule to the records you want formatted, starting in row 2, and use =AND($C2>=1000,$D2="Open"). A row with an amount of 1,200 and status Open qualifies; a row with an amount of 900 does not.

IF can be included, but it is often redundant in a conditional formatting rule. With statuses in B2:B50, apply the rule to B2:B50 and enter =IF(B2="Overdue",TRUE,FALSE). It works, but =B2="Overdue" expresses the same test more simply. Use IF when the logic genuinely needs different outcomes, rather than only translating a comparison into TRUE or FALSE.

Understand relative and absolute references

Reference behavior is one of the most important details in formula-based formatting. Relative references change as Excel evaluates different cells; absolute references, marked with dollar signs, stay fixed. A mixed reference locks either the column or the row, but not both.

A2 lets both the column and row adjust.
$A$2 keeps both the column and row fixed.
$A2 fixes column A but lets the row change.
A$2 lets the column change but fixes row 2.

These distinctions matter when formatting multiple columns based on one status cell. Assume records occupy A2:D100 and the status is in D. To format every cell in a row when its status is Late, select A2:D100 and use =$D2="Late". The column is fixed so the test always reads D, but the row changes for each record. If you accidentally use =$D$2="Late", every row will be judged by the status in D2.

Excel may adjust references when you change the Applies to range or copy rules. After making a formula rule, use Home > Conditional Formatting > Manage Rules to check both the formula and the range it applies to.

Apply formatting to rows, blanks, and dates

Formula rules are especially useful for formatting a full record based on one field. Select the intended table area before creating the rule, then anchor the relevant column and leave the row relative, as in the Late-status example. Selecting only the status cells will format only those cells, even if the formula refers to other columns.

Blank checks can also be combined with another condition. Assume identifiers are in A2:A100 and review dates are in C2:C100. If you want to highlight a missing review date only when the row has an identifier, select the cells to format and use =AND($A2<>"",$C2=""). This avoids flagging unused rows where both fields are empty.

For date-based rules, remember that Excel stores dates as serial values. A comparison with TODAY() can identify dates before the current date, but a blank date may also behave like an old date in a comparison. Add a nonblank test when empty cells should not be highlighted. With due dates in B2:B100, use =AND($B2<>"",$B2<TODAY()) to test for a populated date earlier than today. TODAY() updates when Excel recalculates the workbook.

For common tasks such as duplicate values, use the built-in Duplicate Values option under Home > Conditional Formatting > Highlight Cells Rules. A custom formula is not automatically better: built-in rules are easier to recognize, while formula rules earn their added complexity when conditions involve other cells or combined logic.

Rule order, overlap, and editing

A cell can satisfy more than one conditional formatting rule. For example, one rule might make low values red and another might add a data bar. Excel evaluates rules in the order shown in the Rules Manager, and rule order can affect the result when formats conflict. In Manage Rules, use the up and down controls to change priority. The Stop If True option can prevent lower-priority rules from being evaluated for a cell when a higher rule is true.

In the Rules Manager, check the Show formatting rules for setting. The choices include the current selection and the worksheet, and selecting the wrong view can make a rule seem missing. The Applies to box shows the range governed by each rule; an accidental range such as one cell instead of the whole table is a common reason formatting appears incomplete.

On Excel for Mac, conditional formatting is also available from the Home tab, although dialog layouts can differ by release. Excel for the web has conditional formatting controls as well, but the available rule-editing options can differ from the Windows desktop app. If you cannot find a particular formula-rule control in the web interface, open the workbook in the desktop app and check the rule there.

Common problems and practical checks

If a rule does not work as expected, check the following before rewriting the formula:

Wrong starting reference: Compare the formula’s row and column references with the top-left cell of the Applies to range.
Incorrect dollar signs: Lock the column or row that should stay fixed, but leave the other part relative where the test needs to move.
Text stored differently: Check for trailing spaces, inconsistent spelling, or numbers stored as text. A test for “Open” will not match a cell that contains “Open ” with an extra space.
Formatting is hidden: A rule may be true but difficult to notice if its font or fill is similar to the existing format. Inspect the rule’s Format settings.
Overlapping rules: Review priority and Stop If True settings in Manage Rules.
Wrong range: Confirm Applies to includes all intended cells and does not include unrelated rows.

A useful debugging method is to enter the logical test temporarily in an unused worksheet column for one or two sample rows. If it returns TRUE where expected, the issue is more likely the selected range, rule priority, or chosen format. Remove the temporary check after diagnosing the rule.

Frequently Asked Questions

Does conditional formatting require the IF function?
No. A formula rule can directly return TRUE or FALSE, such as a comparison between a cell and a threshold. Use IF only when you need its branching behavior; a basic condition is usually clearer without it.

Can conditional formatting highlight an entire row based on one cell?
Yes. Select the full row range you want formatted, then write a formula that fixes the condition’s column and lets its row change. For example, a status in column D can govern a selected range starting in row 2 with =$D2="Late".

Why does the rule highlight the wrong rows?
The most common cause is a mismatch between the formula’s reference and the top-left cell of the Applies to range. Also check dollar signs, the range boundaries, text spelling, and whether another rule has priority.

Can one conditional formatting rule check two conditions?
Yes. Use AND when both tests must be true, or OR when either test is enough. A formula rule evaluates the combined result and applies the format when that result is TRUE.

Does conditional formatting change the cell’s value?
No. It changes the cell’s appearance, not its contents. Formulas, sorting, filtering, or copying values continue to use the underlying data unless you separately edit it.

Choosing the simplest reliable rule

Start with a built-in conditional formatting rule when the condition is a direct comparison or a standard highlight, duplicate, color scale, or data bar. Move to a formula rule when the outcome depends on another cell, a whole-row condition, or multiple tests. Keep references aligned with the Applies to range, use absolute references only where needed, and review rule order when formats overlap. Those habits make the familiar “if this, then format it” behavior easier to build, inspect, and maintain.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.