Conditional Formatting in Excel: Rules and Examples

How-to basics · Updated November 18, 2026

To conditionally format cells in Excel for Microsoft 365 on Windows, select the range, choose Home > Conditional Formatting, pick a built-in rule or New Rule, set the condition, choose a format, and select OK. The cells’ appearance then changes automatically when their values meet the rule; conditional formatting does not change the underlying values.

What conditional formatting does

Conditional formatting applies visual cues—such as a fill color, font color, border, data bar, or icon—to cells that meet a condition. You might highlight overdue tasks in red, shade the highest sales figures, mark repeated invoice numbers, or show a color scale across a range of scores. Because the formatting responds to cell contents, it can keep a report readable as values change without requiring you to recolor cells manually.

The feature changes how a cell looks, not what it contains. A cell highlighted as overdue still holds its original date or text, and formulas that refer to it continue to use that value. The color is a visual signal, not a new status or a substitute for checking the data. If you copy the value elsewhere without its formatting, the signal may not travel with it.

How Excel evaluates formatting rules

A conditional-formatting rule contains a condition, a format to apply when that condition is met, and an Applies to range. Excel checks the rule against cells in that range and displays the chosen format where the condition is true. The condition can be a built-in test, such as a value greater than a threshold, or a formula that returns TRUE or FALSE.

For formula rules, Excel interprets cell references relative to the top-left cell of the Applies to range. That makes relative and absolute references important. A dollar sign locks a column or row: $A2 always checks column A while allowing the row number to change. A$2 locks row 2 but lets the column change. $A$2 locks both. A reference without dollar signs can shift in both directions as Excel evaluates cells across the range.

Rules can overlap. For example, one rule might color a cell red when it is below zero, while another adds a green fill when it is part of a target range. In Excel, rule order and the Stop If True setting affect how overlapping rules are applied. Open the rule manager to review priority and arrange rules when the result is not what you expect.

Create a built-in rule

For common comparisons, a built-in rule is usually quicker than writing a formula. In Excel for Microsoft 365 on Windows:

  1. Select the cells to evaluate. Include only the intended data range, rather than an entire column, unless you specifically want the rule applied to future entries throughout that column.
  2. Choose Home > Conditional Formatting and select a rule family, such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, or Icon Sets.
  3. Choose the specific condition. Depending on the rule, enter a threshold, select a comparison, or choose how the values should be divided.
  4. Choose a preset appearance or select Custom Format to set options such as fill, font, and border, then select OK.

For example, to flag values greater than 1,000, select the numeric cells, choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than, enter 1000, select a format, and confirm. Use the built-in Duplicate Values rule to find repeated entries in a selected range. These options are useful when the condition is a straightforward comparison and you do not need to combine several tests.

Use a formula for more specific conditions

Choose Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format. Enter a formula that evaluates to TRUE or FALSE, set the format, and confirm. The formula should be written for the top-left cell in the Applies to range. Excel adjusts relative references as it checks the rest of the range.

For a row-highlighting example, assume a task list has status values in C2:C100 and the range to format is A2:D100. Enter =$C2="Overdue". For row 2, the formula returns TRUE when C2 contains the text Overdue; as the rule moves down, it checks C3, C4, and so on. The locked column reference keeps the test in column C, while the row remains relative, so each row is judged by its own status.

For a date example, assume due dates are in B2:B100 and payment status is in D2:D100; apply the rule to B2:B100. Use =AND($B2<>"",$B2>=TODAY(),$B2<=TODAY()+7,$D2<>"Paid"). If today is November 2, 2026, and row 2 has a due date of November 6, 2026, with a status other than Paid, the formula returns TRUE. The blank check prevents empty date cells from being treated like dates, and the other tests keep the highlight to dates from today through seven days ahead. TODAY updates as the date changes, so the qualifying window moves over time.

To find duplicate IDs, assume the IDs are in A2:A100 and apply the rule to A2:A100. Use =COUNTIF($A$2:$A$100,A2)>1. If the value in A2 appears at least twice in that range, COUNTIF returns 2 or more and the condition returns TRUE. This rule highlights every occurrence of a repeated ID, including its first occurrence; it does not identify only the later copy.

Choose the right visual format

Pick a visual treatment that communicates the condition without making the sheet harder to read. A strong fill color can make exceptions easy to spot, but a page full of bright colors is difficult to scan. Consider limiting your palette to a few consistent meanings—for example, red for an issue and amber for a warning—and keep text contrast readable.

  • Highlight Cells Rules suit clear yes-or-no comparisons, such as values greater than a target, dates before a deadline, or text that contains a word.
  • Data Bars give a quick sense of relative magnitude within a range. They can be useful for comparing quantities, but labels and units should remain clear.
  • Color Scales map lower and higher values to different colors, with intermediate shades between them. They show patterns across a range, but the colors represent relative values in that selected range, not necessarily an absolute good-or-bad judgment.
  • Icon Sets categorize values using symbols such as arrows or traffic lights. Review the thresholds so the symbols communicate the intended categories rather than merely looking decorative.

A color scale can be misleading when readers assume its colors have a fixed meaning. If the range changes, the values assigned to its color bands can change too. For a report with a fixed target, a rule tied to that target is often clearer than a scale whose meaning depends on the other values in the selection.

Manage, edit, and remove rules

To inspect existing conditions, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, check which rules apply to the current selection or to the worksheet, review their order, edit a rule, and adjust its Applies to range. This is particularly useful when a copied range has acquired extra rules or when a rule formats cells outside the intended table.

To remove formatting, select the relevant cells and choose Home > Conditional Formatting > Clear Rules. The menu offers options to clear rules from selected cells or from the entire worksheet. Clearing rules removes the conditional formats; it does not erase the cell values. If you only want to change the appearance, edit the rule instead of clearing it and rebuilding it.

Copying cells can also copy conditional formatting. That may be helpful when extending a table, but it can create duplicate or overlapping rules. After pasting, check Manage Rules and confirm the Applies to range covers the right cells. If a rule should cover new rows, extend that range rather than creating a separate copy for every row.

Common problems and practical checks

  • The rule highlights the wrong row: Check the formula’s reference anchors and the top-left cell of the Applies to range. A formula written for row 2 may be offset if the range actually begins at row 3.
  • A rule appears to do nothing: Confirm the condition matches the stored value. A number stored as text, an unexpected space, or a date entered as text may not behave like a genuine number or date. Test the condition on a small sample and inspect the rule’s range.
  • Some cells show a different color: Look for another rule that applies to those cells. Review priority and Stop If True in Manage Rules, and check whether direct cell formatting is making the result confusing.
  • Blank cells are highlighted unexpectedly: Add a test that excludes blanks when appropriate. In formula-based date rules, a blank check such as $B2<>"" can prevent empty entries from qualifying.
  • Colors do not update as expected: Check whether the rule uses a value that has changed, whether the Applies to range includes the cell, and whether the condition depends on a formula such as TODAY. A formula-based rule should return TRUE for a qualifying sample.

When troubleshooting, temporarily simplify the rule or test it against a single known example. A complicated formula is easier to diagnose if you check each condition separately—for instance, whether the date is nonblank, whether it falls inside the intended window, and whether the status is unpaid. Also remember that conditional formatting is not a data-validation feature: it can signal an entry that looks unusual, but it does not stop someone from entering it.

Excel for Mac and Excel for the web

In Excel for Microsoft 365 on Mac, the main workflow is similar: select the range, then use Home > Conditional Formatting to create or manage a rule. Dialog layouts and some controls can differ from Windows, but the same ideas about ranges, formulas, and references apply. If a formula rule behaves differently than expected, confirm that the selected range and its top-left cell match the references used in the formula.

In Excel for the web, conditional-formatting commands are also available from the Home tab, and common rules can be created or edited in the browser. The available controls can differ from desktop Excel, particularly for more involved rule management. If an option you need is not available in the browser interface, open the workbook in the desktop app and use its Conditional Formatting and Manage Rules controls.

Frequently Asked Questions

Does conditional formatting change the value in a cell?
No. It changes the cell’s display when a condition is met, but the stored value remains the same. A highlighted number can still be used normally in formulas and calculations.

Can I highlight an entire row based on one cell?
Yes. Select the rows’ full range, create a formula rule, and lock the column that contains the test while leaving the row relative. For example, with status in column C and a range beginning in row 2, the pattern =$C2="Overdue" checks each row’s status.

Can conditional formatting find duplicates?
Yes. Select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. A formula rule can also find duplicates and can be adapted to a specific range or condition.

Why does my conditional-formatting formula use dollar signs?
Dollar signs control which references move as Excel evaluates cells across the Applies to range. Lock a column when every cell should check the same column, and leave the row relative when each row should be checked separately.

Can I apply more than one rule to the same cell?
Yes. Multiple rules can apply to the same cell, but their order and Stop If True settings can affect the displayed result. Use Manage Rules to review priority and resolve conflicts.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.