Conditional Formatting Formula If Statement in Excel

In Excel, a conditional-formatting formula must evaluate to TRUE or FALSE for the cell or row you want to format. You can use an IF statement, such as =IF($C2="Late",TRUE,FALSE), but a direct logical test, =$C2="Late", is usually simpler and does the same job.

What a conditional-formatting formula does

Conditional formatting changes a cell’s appearance when a rule is met: for example, it can add a fill color to overdue dates or bold an entire row when its status is marked Late. A formula-based rule lets you define that condition with an Excel formula instead of choosing only from preset comparisons such as “greater than” or “text that contains.”

Excel evaluates the formula for each cell in the rule’s Applies to range. When the result is TRUE, Excel applies the selected format; when it is FALSE, it does not. The formula does not need to return a word such as “Red,” and it does not change the cell’s stored value. It only controls the cell’s appearance.

The formula is written from the perspective of the top-left cell in the Applies to range. Excel adjusts relative references as it evaluates the other cells. That detail is why a rule can format many rows correctly with one formula—and why an incorrectly placed dollar sign can make a rule appear to behave unpredictably.

Create a formula-based rule in Excel

In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format, enter the formula in the formula box, choose Format to set the appearance, and select OK. The rule is saved for the selected range.

For example, suppose a table has task names in A2:A100, owners in B2:B100, and status values in C2:C100. To color the whole row when the status is Late:

Select A2:C100 before creating the rule.
With statuses in C2:C100 and the selected range beginning at A2, use =$C2="Late".
Choose a fill color, then confirm the rule.

The dollar sign before C locks the status column, so Excel continues checking column C as it formats A, B, and C. The row number has no dollar sign, so it changes from 2 to 3, 4, and so on for subsequent rows. If you select only C2:C100, the same test can format the status cells alone.

On Excel for Mac, use the Home tab’s Conditional Formatting controls and create a new rule; the dialog’s wording or arrangement can differ from Windows. Excel for the web also offers formula-based rules, though its rule dialogs and management options are not identical to the desktop app.

When to use IF—and when to skip it

IF is useful when the result must choose between two values, but a conditional-formatting rule generally needs only a yes-or-no test. In the status example, both of these formulas return TRUE when C2 contains Late and FALSE otherwise:

With statuses in C2:C100 and A2:C100 selected for formatting, the IF version is =IF($C2="Late",TRUE,FALSE). For the same layout, the shorter direct test is =$C2="Late".

Because the direct test already produces a logical result, adding IF does not improve the rule. It can make formulas longer and harder to troubleshoot. Use IF when you need to build a condition that genuinely depends on alternative outcomes, not just to wrap a comparison in TRUE and FALSE.

For instance, a rule can use AND to require several conditions at once. With due dates in D2:D100, completion statuses in E2:E100, and A2:E100 selected, use =AND($D2<>"",$D2<TODAY(),$E2<>"Yes") to highlight rows with a nonblank due date before today that are not marked Yes. The empty-date check prevents blank due-date cells from being treated as overdue. TODAY returns the current date and updates as Excel recalculates.

Relative and absolute references

Reference style is central to formula rules. A reference without dollar signs can shift by both row and column; a dollar sign before the column letter locks that column, and a dollar sign before the row number locks that row. These are the same reference rules used in ordinary worksheet formulas.

Relative reference: Use it when the rule should check a different row or column as it moves across the Applies to range.
Locked column: Use a form such as $C2 when every cell in a row should look at column C, but each row should check its own status.
Locked row: Use a form such as C$2 when formatting across columns based on a value in row 2.
Fully locked reference: Use a form such as $C$2 when every formatted cell should compare against one fixed cell.

A practical check is to identify the top-left cell in the Applies to range, then ask which cell the formula should inspect for that first formatted cell. Write the formula for that starting point. If the formula refers to the wrong row or column at the top-left, the rest of the range will usually be wrong too.

Useful formula examples

Formula rules can do more than test one status. The examples below show common patterns. In each case, select the described range first and choose a format that makes the condition easy to notice.

Highlight repeated IDs: With IDs in A2:A100 and that same range selected, use =COUNTIF($A$2:$A$100,$A2)>1. Each nonblank ID that occurs more than once will meet the test; if blank cells should not be highlighted, use =AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1) instead.
Shade every other row: With A2:F100 selected, use =MOD(ROW(),2)=0 to format even-numbered worksheet rows. The pattern follows worksheet row numbers, so if you want the first row of the selected table shaded regardless of its sheet position, adjust the test to match the table’s starting row.
Highlight a value above a threshold stored in a cell: With scores in B2:B100, the threshold in F1, and B2:B100 selected, use =B2>$F$1. The score reference adjusts for each row while the threshold remains fixed.

For the duplicate-ID example, COUNTIF counts how many times the current row’s ID appears in the full list. A count greater than one identifies repeated values. The version with AND adds a nonblank test because a blank can otherwise be counted as a repeated entry. For alternating rows, MOD returns the remainder after division; the comparison selects rows whose number is even.

Common problems and how to fix them

If a rule formats the wrong rows, inspect the reference and the Applies to range together. A formula written for row 2 may be wrong if the range begins at row 1 or row 3. Likewise, using C2 without locking the column in a rule applied across A:C can cause Excel to test different columns as it moves across the row.

Check the starting cell: Make sure the formula’s row and column references match the top-left cell of the selected range.
Check the Applies to box: Open Home > Conditional Formatting > Manage Rules and confirm the rule covers the intended cells. In the rule manager, the display can be scoped to the current selection or worksheet.
Check text exactly: A comparison such as Late may fail if the cell contains a different word or extra characters. Check the actual cell contents and whether the rule should allow partial matches.
Check for blanks and errors: Blank dates, empty IDs, or error values can produce surprising results. Add a condition to exclude blanks when that is appropriate, and check the source cells for errors.
Check rule order: Multiple rules can apply to the same cell. In Manage Rules, review their order and whether Stop If True is selected for a rule; those settings can affect which formatting is visible.

Also distinguish the formula’s result from its formatting. A rule can be correct but appear ineffective if its chosen font color is hard to see, or if another rule applies a competing format. To diagnose it, temporarily choose an obvious fill color and inspect the rule’s range and order.

Formula rules compared with preset rules

Preset conditional-formatting options are quicker when the test is straightforward. For example, Home > Conditional Formatting > Highlight Cells Rules includes ready-made comparisons such as greater than, less than, and text that contains. Color scales, data bars, and icon sets are useful when you want to visualize relative values rather than define a custom TRUE-or-FALSE condition.

A formula rule is the better choice when the condition depends on another cell, combines multiple tests, or needs to format an entire row based on one column. A preset rule can often express “score is greater than 80” directly; a formula gives you more control for a condition such as “score is above the threshold in F1 and the status is not Complete.” Choose the simplest rule that accurately expresses the requirement, because simpler rules are easier to maintain.

Frequently Asked Questions

Does a conditional-formatting formula need IF?
No. A formula that returns TRUE or FALSE is enough. Use IF only when choosing between outcomes adds something the direct logical test cannot express more clearly.

Can I use a formula to format an entire row?
Yes. Select the full row range you want formatted, then write the formula to test the appropriate column. Lock that column with a dollar sign while leaving the row relative, as in the status example above.

Why does my formula work in one cell but not across the range?
The formula may use the wrong mix of relative and absolute references, or the Applies to range may start in a different cell than the formula assumes. Check the top-left cell and test how the references should shift across rows and columns.

Can conditional formatting use dates and functions such as TODAY?
Yes. Formula-based rules can compare dates and use worksheet functions. For date rules, account for blank cells where necessary, and remember that TODAY changes as the workbook recalculates.

How do I change or remove a formula rule?
Choose Home > Conditional Formatting > Manage Rules, select the rule, and edit or delete it. Confirm the manager is showing rules for the intended worksheet or selection so you do not overlook a rule stored elsewhere.

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.