Conditional Formatting in Excel: If-Then Color Rules

Yes. In Excel, select the cells you want to format, then choose Home > Conditional Formatting and create a rule that applies a fill color when a condition is true. For example, choose Highlight Cells Rules > Greater Than, enter 100, and select a color to highlight values above 100; use a formula-based rule when the condition depends on another cell or needs more control.

What “if then color” means in Excel

Conditional formatting is Excel’s way of changing a cell’s appearance automatically when its value meets a rule. In plain language, the instruction is “if this condition is true, then show this format.” The format can include a fill color, font color, bold text, a border, or an icon. The underlying value is not changed: a cell containing 125 remains 125 even when Excel displays it with a red fill.

This is useful for making patterns visible in a worksheet without sorting or manually coloring cells. You might flag overdue tasks, highlight sales below a target, distinguish repeated entries, or shade a whole row based on its status. If the data changes, Excel reevaluates the rule and updates the formatting, subject to the workbook’s calculation settings.

Conditional formatting is visual emphasis, not a replacement for data validation or a calculation. A red cell does not prevent someone from entering an invalid value, and it does not prove why a value is unusual. Use it to direct attention, then keep the underlying data and formulas clear.

Create a basic if-then color rule

For a straightforward condition on the selected cells, use one of Excel’s built-in rules. In Excel for Microsoft 365 on Windows:

Select the cells you want Excel to check, such as B2:B20.
Choose Home > Conditional Formatting > Highlight Cells Rules.
Choose a condition, such as Greater Than, Less Than, or Text that Contains.
Enter the comparison value or text, select a formatting option, and confirm.

To choose a custom fill rather than one of the preset styles, select Home > Conditional Formatting > New Rule. Choose a rule type that fits the condition, set its details, and use the formatting controls to select a fill, font, or border. The wording of some dialog controls can vary slightly as Microsoft updates Excel, but the Conditional Formatting commands are on the Home tab.

For a quick test, select B2:B20 and create a Greater Than rule with the value 100. Cells whose numeric values exceed 100 receive the chosen color; a value of exactly 100 does not meet “greater than.” If you want 100 included, choose a greater-than-or-equal condition where available, or create a formula rule. Selecting the intended range before creating the rule helps avoid formatting unrelated cells.

Use a formula for more flexible conditions

Formula rules let you test one cell and format another, combine conditions, or use a condition that built-in rules do not offer. Select the range to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that returns TRUE for cells that should receive the format, choose the appearance, and confirm.

For example, with values in B2:B20 and the rule applied to B2:B20, enter =B2>100 to color values over 100. Excel evaluates the expression for each cell in the selected range. Because B2 is a relative reference, it adjusts as the rule is evaluated down the range: the next row is checked against B3, then B4, and so on.

To color complete rows based on a value in one column, with task names in A2:A20, statuses in B2:B20, and the rule applied to A2:D20, use =$B2="Complete". Choose a fill color, and each row from columns A to D will be formatted when its status in column B is Complete. The dollar sign locks the column B reference as the rule moves across a row; leaving the row number unlocked lets it adjust for each row.

For a combined test, with task names in A2:A20, due dates in C2:C20, and the rule applied to A2:D20, use =AND($C2<>"",$C2<TODAY()). This flags a row only when its due-date cell is not blank and the date is earlier than today. If the current date is November 2, 2026, a valid date of November 1, 2026 meets the test, while November 2 does not. Excel stores dates as numbers, so make sure the entries in column C are real Excel dates rather than date-looking text.

Relative and absolute references

Reference style is one of the most important details in formula-based formatting. A relative reference such as B2 changes as Excel applies the rule to different cells. An absolute reference such as $B$2 stays fixed. A mixed reference such as $B2 keeps the column fixed but lets the row change; B$2 does the opposite.

Think about the range’s top-left cell when writing the formula. If a rule applies to A2:D20, a formula using $B2 is evaluated as a test of B2 for the first row, B3 for the next, and so forth. If the same rule is accidentally applied starting at A1, the reference may be offset and highlight the wrong rows. Set the range and reference pattern deliberately, then check several rows in the preview or worksheet.

When the condition compares each value with one fixed target, you can lock the target cell. For example, with scores in B2:B20, a target in E1, and the rule applied to B2:B20, use =B2>$E$1. Each score is compared with the same value in E1. This makes it easy to change the target later without editing the conditional-formatting rule.

Choose a useful color and rule type

Choose a visual treatment that makes the important distinction easy to see. A single fill color works well for a yes-or-no condition, while a font color or border can emphasize a result without covering the worksheet’s existing fills. For a range of numeric values, Color Scales can show relative highs and lows; Data Bars can show approximate magnitude inside cells; and Icon Sets can group values into visual categories.

These options answer different questions. A formula rule or Highlight Cells Rule asks whether a specific condition is true. A color scale shows where a value falls relative to other values in its range, so its colors can change when the data distribution changes. A data bar emphasizes size, while an icon set conveys categories such as high, medium, and low. For exact thresholds, use a rule whose comparison is explicit rather than assuming a color scale represents a fixed cutoff.

Color should not be the only way important information is communicated. Some readers may have difficulty distinguishing certain colors, and a printed worksheet may appear in grayscale. Consider adding a clear label, icon, or text status alongside the color. Also check that the chosen font and fill have enough contrast to remain readable.

Manage overlapping rules and applies-to ranges

A cell can meet more than one conditional-formatting rule. For example, a low-score rule and a duplicate-value rule may both apply to the same cell. To inspect or change rules, select a relevant cell or range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, check the displayed range, edit or delete a rule, and change rule order where needed.

Rule order matters when formats overlap. Excel evaluates the rules in the order shown, and the Stop If True option can prevent lower-priority rules from being applied after a matching rule. The exact appearance also depends on which formatting attributes the rules set; one rule may set a fill while another sets a font color. When the result is confusing, inspect the rules manager rather than adding another rule on top of the existing ones.

The Applies to range determines which cells can receive a rule’s formatting. If the range is too small, new rows may not be covered; if it is too large, cells outside the intended table may be colored. When adding rows, verify that the rule includes them. Excel tables can be convenient for expanding data, but do not assume every existing conditional-formatting rule will have the intended range after a worksheet is rearranged.

Common problems and practical fixes

The wrong rows are colored: Check the top-left cell of the Applies to range and the formula’s row and column references. A formula written for row 2 may be offset if the range starts on row 1.
Nothing is highlighted: Confirm that the condition is actually true for the cell, that the rule applies to the selected cells, and that the values have the expected type. A number stored as text may not behave like a numeric value in a comparison.
Blank cells are colored: Add a nonblank test when blanks should be ignored. For dates, the AND example above checks that the date cell is not empty before comparing it with today.
A new entry is not formatted: Open Manage Rules and inspect Applies to. Extend the range or arrange the data and rule so newly added rows are included.
The color does not match the value: Look for other rules on the same range, examine their order, and check whether Stop If True is selected. Also confirm that the rule is not a color scale that recalculates its relative colors.

To remove formatting without deleting cell contents, select the affected cells and choose Home > Conditional Formatting > Clear Rules. The menu offers options to clear rules from the selected cells or from the entire worksheet. Use the narrower option when other parts of the sheet still rely on conditional formatting.

Conditional formatting versus manual color

Manual fill color is appropriate when a person is making a one-time annotation, such as marking a reviewed item. It does not automatically respond when the cell’s value changes. Conditional formatting is better when the color represents a repeatable rule: if a task becomes Complete, color its row; if a balance falls below a threshold, flag it.

That difference can prevent misleading worksheets. A hand-applied red fill may remain after the value has been corrected, while a correctly configured conditional rule updates according to its condition. On the other hand, conditional formatting can be harder to interpret if a workbook contains many overlapping rules. Keep the rule set understandable and document unusual logic near the data or in worksheet notes.

Frequently Asked Questions

Can Excel color a cell based on another cell?
Yes. Select the cells to format, create a formula-based conditional-formatting rule, and refer to the other cell with the appropriate relative or absolute reference. For example, a whole row can be formatted based on the status column by locking the column and letting the row adjust.

Can I color an entire row when one cell says Complete?
Yes. Select the full row range you want formatted, such as A2:D20, and use a formula rule that checks the status cell in each row. Use a mixed reference such as $B2 when statuses are in column B and the first data row is row 2.

Does conditional formatting change the cell’s value?
No. It changes how Excel displays the cell, not the value stored in it. A displayed color is not a replacement for a formula, a text label, or a data check.

Can I use more than one condition?
Yes. You can add multiple conditional-formatting rules, or use a formula with functions such as AND or OR when a single rule needs multiple tests. Check rule order and overlap if more than one condition can be true for the same cell.

Does the rule work in Excel for Mac or Excel for the web?
Conditional formatting is available in Excel for Mac and Excel for the web, although dialogs, menus, and some editing options can differ from Excel for Microsoft 365 on Windows. If a menu label or rule-management control is not in the same place, use the Conditional Formatting commands on the Home tab and consult the version’s available rule options.

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.