In Excel, an “if statement” for conditional formatting is usually a formula-based rule that returns TRUE or FALSE; Excel formats cells when the rule is TRUE. For example, with status values in column C and rows of data starting at row 2, apply a rule to the rows you want to highlight using =$C2="Late".
What an If Statement Means in Conditional Formatting
Conditional formatting changes a cell’s appearance when a specified condition is met. Unlike an IF formula entered in a worksheet cell, a conditional-formatting formula does not normally display a result such as “Yes” or “No.” It evaluates a condition in the background and applies a format—such as a fill color, font color, or border—when the result is TRUE.
You do not have to use the IF function to create this kind of rule. A comparison such as =$C2="Late" already evaluates to TRUE or FALSE, so it is enough. You can use the IF function when it genuinely helps express the logic, but it is often unnecessary. For example, with numeric values in B2:B20 and a goal in D1, a rule applied to B2:B20 could use =IF(B2>=$D$1,TRUE,FALSE). The shorter condition =B2>=$D$1 produces the same TRUE/FALSE result.
This distinction matters because worksheet formulas and conditional-formatting formulas serve different purposes. A cell formula writes a value into the grid. A conditional-formatting formula supplies a test to the formatting rule, and the result is visible through the cell’s appearance rather than as text in the cell.
How Excel Evaluates a Rule
A formula-based rule is evaluated for each cell in the range named in the rule’s “Applies to” setting. Excel treats the formula as if it were written for the top-left cell of that range, then adjusts relative references as it evaluates other cells. That is why a rule’s references and the selected range need to be planned together.
For example, if the range is A2:D100 and the formula is =$C2="Late", Excel checks the value in column C for each row. The dollar sign before C locks the column, while the missing dollar sign before 2 lets the row change. Thus, the rule checks C2 for row 2, C3 for row 3, and so on, applying the format across each matching row in A:D.
When a formula returns TRUE, Excel applies the chosen formatting. When it returns FALSE, that rule does not apply its format. A cell can be affected by more than one rule, so the final appearance may also depend on rule order and whether rules stop further evaluation.
Create a Formula-Based Rule
In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter the condition, choose Format to select the appearance, and confirm the dialogs. Check the “Applies to” range in the Conditional Formatting Rules Manager if the rule needs to cover more than the selected cells.
- Select the target range before making the rule. For a row-based rule, select the full rows or columns of cells that should receive the formatting, not just the cell containing the test value.
- Write a formula that evaluates to TRUE or FALSE for the range’s top-left cell. Use a leading equals sign, as in
=$C2="Late". - Choose a format that makes the condition easy to recognize, such as a pale fill and dark text.
- Confirm the rule and inspect several rows, including one that should match and one that should not.
In Excel for Mac, the Conditional Formatting controls are also available on the Home tab, though the dialog layout can differ by version. Excel for the web supports conditional formatting, but some rule-management options may be presented differently or be less convenient than in the desktop app. If a rule is difficult to edit in the browser, open the workbook in desktop Excel.
Use Relative and Absolute References Correctly
Reference style is one of the most important parts of a conditional-formatting formula. A relative reference changes as Excel evaluates the next cell; an absolute reference stays fixed; and a mixed reference locks only the column or row. The dollar sign controls this behavior.
A2lets both the column and row adjust.$A2keeps the column fixed but lets the row adjust.A$2lets the column adjust but keeps the row fixed.$A$2keeps both the column and row fixed.
For a row-wide highlight based on a value in column C, a formula like =$C2="Late" is usually appropriate when the selected range begins on row 2. If the selected range instead begins on row 5, the formula should refer to row 5, such as =$C5="Late". A mismatch between the first selected row and the formula’s row reference is a common cause of highlights appearing one row too high or too low.
To compare every cell in a selected numeric range against one fixed threshold, keep the threshold’s location absolute. With values in B2:B20 and a threshold in D1, use =B2>=$D$1 for a rule applied to B2:B20. The cell reference B2 adjusts as the rule moves down the range; D1 remains fixed.
Useful If-Style Examples
Formula rules can test text, numbers, dates, blanks, or combinations of conditions. These examples assume the selected range starts on the same row referenced in the formula. Choose the formatting range deliberately: some rules are intended to format one column, while others format entire records.
- Highlight overdue tasks: With due dates in B2:B100 and completion status in C2:C100, apply to A2:C100 and use
=AND($B2<TODAY(),$C2<>"Complete"). The rule is true when the date has passed and the task is not marked complete. TODAY updates as the workbook recalculates. - Flag values above a target: With scores in B2:B50 and a target in D1, apply to B2:B50 and use
=B2>=$D$1. A score equal to the target qualifies as well as one above it. - Highlight rows with a particular text value: With status in C2:C100, apply to A2:D100 and use
=$C2="Review". Excel compares the cell’s contents with the text in quotation marks. - Identify blank cells: To format blank cells in A2:A100, use
=A2="". If cells contain formulas that return an empty string, this test can also treat them as blank-looking results. - Test two conditions together: With a region in A2:A100 and sales in B2:B100, apply a rule to A2:B100 using
=AND($A2="West",$B2>=10000). Both conditions must be true for the formatting to appear.
For more involved tests, AND requires every listed condition to be true, while OR requires at least one. These functions are useful when a rule should depend on multiple columns. Keep formulas readable and test them against sample rows so it is clear which cases are intended to match.
Built-In Rules or Formula Rules?
Excel’s built-in conditional-formatting rules cover many common needs without requiring a formula. From Home > Conditional Formatting, options include Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets. These are convenient for tasks such as highlighting values greater than a number, finding duplicate values, or showing relative magnitude with bars or colors.
Choose a formula-based rule when the condition depends on another cell, multiple columns, a combination of tests, or a pattern that the built-in choices do not express. For example, a built-in “Greater Than” rule can compare a selected range with a value, but a formula is clearer when each row must be compared with a different value in another column. Built-in rules are often quicker to set up; formulas offer more control but require careful references.
Conditional formatting is not the same as changing the underlying data. A red fill does not make a value invalid, and it does not prevent someone from entering it. If you need to restrict what users can enter, consider Data Validation rather than relying on a visual warning.
Common Problems and How to Fix Them
If a rule does not behave as expected, check the formula, the selected range, and the rule manager before rebuilding it. A formula can be valid but still apply incorrectly because its references do not match the “Applies to” range.
- Nothing changes: Confirm that the condition is true for at least one cell, that the rule is enabled, and that its “Applies to” range includes the cells you are viewing. Check spelling and extra spaces in text values.
- The wrong row is highlighted: Make the formula’s row number match the first row of the applied range. Review whether the column should be locked with a dollar sign.
- Only one cell changes: The applied range may include only one cell. Edit “Applies to” in Home > Conditional Formatting > Manage Rules.
- A different color appears: Another rule may also apply to the cell. In Manage Rules, check rule order and the “Stop If True” setting. A higher-priority rule can affect which formatting is displayed.
- Dates seem wrong: Excel stores dates as serial values, but imported dates may be text rather than actual dates. Check that the cells contain real date values and that the formula compares like with like.
Also remember that conditional formatting changes visual presentation, not the stored value. Copying and pasting cells can bring formatting or rules along with the data, so inspect the rules manager when a workbook accumulates unexpected formatting. Avoid creating many overlapping rules when one well-designed formula can cover the intended range.
Managing Rule Order and Workbook Performance
When several rules apply to the same cells, Excel evaluates them according to their order in the rules manager. You can move rules up or down there. “Stop If True” can prevent lower-priority rules from being evaluated for a cell after a higher-priority rule is true. This is useful when rules represent distinct categories, but it can also suppress a format you expected to see, so use it intentionally.
For a tidy workbook, apply rules to the smallest practical range and reuse a rule across the relevant cells rather than creating many nearly identical rules. A formula applied to an entire worksheet can require Excel to evaluate far more cells than necessary. When a workbook becomes slow or difficult to maintain, review broad ranges and duplicate rules in Manage Rules.
Conditional formatting is also not a reliable substitute for data analysis. Filters, sorting, formulas, and PivotTables can help you inspect or summarize the underlying values; formatting is best used as a visual cue. If the result must be communicated to someone who may view the workbook in software that handles formatting differently, include an explicit status or explanation in the data as well.
Frequently Asked Questions
Do I need the IF function in a conditional-formatting formula?
No. A condition such as =B2>10 directly returns TRUE or FALSE, which is what the rule needs. Use IF only when it makes a more complex test easier to understand.
Can conditional formatting highlight an entire row?
Yes. Select the row-wide range, such as A2:D100, and write a formula that checks the relevant column while allowing the row number to adjust. For a status in C, a typical formula is =$C2="Late".
Why does my conditional-formatting formula start with an equals sign?
The equals sign tells Excel to interpret the entry as a formula. The formula should produce a logical TRUE or FALSE result for each cell or row being evaluated.
Can I use conditional formatting to stop incorrect entries?
No. It changes appearance but does not block data entry. Use Data Validation when you need to limit acceptable entries, and use conditional formatting to make conditions visible.
Why does a rule work in one row but not the next?
Check whether the references are relative, absolute, or mixed as intended, and verify that the formula’s row corresponds to the first row of the “Applies to” range. A locked row or an incorrect starting reference can make Excel test the wrong cell repeatedly.
Related
- Conditional Formatting Definition and Practical Examples
- Power Query Join Tables: A Practical Guide
- How to Compare Data in Two Excel Sheets Step by Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel FILTER: What the Include Argument DoesIn Excel’s FILTER function, include is the test that decides which rows or columns to return: TRUE keeps…
- Excel Date Countdown Formula: Calculate Days Left in ExcelIf the target date is in cell B2, enter =B2-TODAY() in another cell to show the number of…
- How to Add a Formula to an Entire Row in ExcelTo add a formula across a row in Excel, enter it in the first cell where you want…
- IFERROR with VLOOKUP in Excel: Hide Lookup ErrorsYes. With lookup IDs in A2:A100, return values in C2:C100, and the value to search for in E2,…
- Excel QR Code Formula: Create QR Codes from Cell DataExcel has no built-in QR-code function, but in Microsoft 365 for Windows you can display a QR code…
- How to Create Rules in Outlook to Sort Email AutomaticallyTo create a rule in Outlook, open Settings > Mail > Rules in new Outlook or Outlook on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.