Add Conditional Formatting in Excel: Rules and Tips

How-to basics · Updated November 18, 2026

To add conditional formatting in Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home > Conditional Formatting and pick a rule or select New Rule for more control. Set the condition and the appearance, then choose OK. The formatting updates as cell values change, so you can spot patterns without changing the underlying data.

What conditional formatting does

Conditional formatting changes how a cell looks when it meets a condition. It can apply a fill color, text color, border, data bar, color scale, or icon. The rule does not replace a value or alter a formula; it adds a visual layer that Excel recalculates when relevant values change.

For example, a sales list could shade amounts below a target, show a colored bar inside each amount cell, or mark duplicate invoice numbers. A date column could highlight deadlines that have passed. These cues help readers scan a worksheet, but they should reinforce clear labels and sound data organization rather than serve as the only way to communicate important information.

Conditional formatting is most useful when the condition may change over time. A manually applied red fill stays red even after the value is corrected; a rule can remove the highlight automatically once the value no longer meets its condition.

Add a built-in conditional formatting rule

For a straightforward comparison, Excel’s built-in rules are usually the quickest option. Select the cells first; the selection determines where the rule is applied.

  1. Select the target range, such as B2:B50. Avoid selecting an entire column unless you need the rule there, since a smaller range is easier to inspect and manage.
  2. Choose Home > Conditional Formatting.
  3. Choose a rule family. For example, use Highlight Cells Rules for comparisons, text, dates, or duplicates, or Top/Bottom Rules to identify high or low values.
  4. Choose the specific condition, enter a value if prompted, and select a formatting style. Some options offer a preset; choose Custom Format when you want to set the font, fill, or border yourself.
  5. Review the highlighted cells and adjust the rule if it catches too much or too little. Use Home > Conditional Formatting > Manage Rules to edit its condition or range.

For example, to flag numbers below 100, select the number cells and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter 100, select a style, and confirm. Excel applies the style to values that meet the condition; it does not change those values to 100 or less.

Create a formula-based rule

A formula rule is useful when a built-in rule cannot express the relationship you need, such as checking one column to format another. Select the cells that should receive the formatting, then choose Home > Conditional Formatting > New Rule. In the rule list, choose Use a formula to determine which cells to format, enter a formula that returns TRUE or FALSE, select Format to choose the appearance, and choose OK.

For an overdue-date example, assume due dates are in D2:D100, with the first data row at row 2. Select D2:D100 and use this formula:

=AND($D2<>"",$D2<TODAY())

It returns TRUE when a cell in column D is not blank and its date is earlier than today, so those dates receive the chosen format. The dollar sign fixes the column reference while leaving the row relative: as Excel evaluates each row, D2 becomes D3, D4, and so on. The blank check prevents empty cells from being treated as overdue. This rule is dynamic because TODAY() returns the current date when Excel recalculates.

For a second example, assume task status is in C2:C100 and you want to format the corresponding cells in A2:A100. Select A2:A100, then create a formula rule using:

=$C2="Late"

When the status in column C for a row is exactly Late, the cell in column A on that row gets the selected format. The dollar sign locks column C, but the row remains relative. Formula rules depend on the selection’s top-left cell: if the selection starts on row 2, write the row reference as 2. A mismatch between the starting row and the formula reference is a common cause of highlights appearing one row off.

Choose the rule type that fits the data

Excel groups rules by the kind of comparison or visual summary they provide. Choose a type that makes the pattern understandable, not merely colorful.

  • Highlight Cells Rules: Use these for values greater than, less than, between, equal to, text containing, dates in a period, or duplicate values. They work well for clear, fixed tests.
  • Top/Bottom Rules: Identify the top or bottom number or percentage of values, or values above or below an average. These are useful for ranking, but the highlighted group can change as the data changes.
  • Data Bars: Add a bar within each cell to show relative magnitude. They suit numeric comparisons, but long numbers or negative values may need careful presentation.
  • Color Scales: Map low, middle, and high values to a color gradient. This is helpful for scanning a range, but the colors show relative position in that range, not whether a value meets an absolute target.
  • Icon Sets: Place symbols such as arrows or traffic-light-style icons beside values according to thresholds. Make sure the thresholds are meaningful and that the numbers remain available to readers.

For an absolute pass mark, a rule such as “greater than or equal to 70” communicates the condition more precisely than a color scale. A scale can make the lowest score look alarming even if every score is above the required threshold.

Useful ways to apply rules

Conditional formatting can answer practical questions across many worksheet types. A project tracker can highlight tasks whose status is Late, a budget sheet can call attention to spending above a limit, and an inventory list can flag quantities below a reorder point. A data bar can make differences in sales easier to compare, while a duplicate-value rule can help identify repeated reference numbers.

For deadlines, decide whether the rule should include today. The earlier date formula highlights dates strictly before today; a date equal to today is not highlighted. If the spreadsheet uses times as well as dates, decide whether a deadline later today should count as overdue and choose a condition that matches that policy. Excel stores dates as serial values, so a cell that looks like a date but is actually text may not behave as expected.

Use formatting consistently. For instance, reserve red for a genuine exception, use one color for one meaning, and keep enough contrast between text and fill. If the workbook will be printed in grayscale or viewed by people with color-vision differences, add a label, icon, or other cue rather than relying on color alone.

Manage rule ranges and priority

Rules apply to a range shown as Applies to in the Conditional Formatting Rules Manager. To inspect or change rules, select a relevant cell and choose Home > Conditional Formatting > Manage Rules. In the manager, use the display menu to view rules for the current selection or the worksheet. A rule can exist on the sheet even if the currently selected cell is outside its range, so checking the worksheet view can help when a format seems to have come from nowhere.

When more than one rule applies to a cell, the order in the manager matters if rules set conflicting formatting. Move a rule up or down to adjust its priority. The Stop If True option, available for rules in the manager, can prevent lower-priority rules from being evaluated for a cell once a higher-priority condition is true. Use it only when that stopping behavior is intentional; otherwise, a lower rule may appear to be ignored.

To remove formatting, use Home > Conditional Formatting > Clear Rules and choose whether to clear rules from the selected cells or the entire worksheet. Clearing rules removes conditional formats, not cell contents. Before clearing a large sheet, check the scope so you do not remove rules that other sections rely on.

Common problems and how to fix them

  • The wrong rows are highlighted: Check the formula’s row number against the first row of the selected range. Also check dollar signs: a reference such as $C2 fixes the column but lets the row adjust, while $C$2 fixes both.
  • Some values are missed: Inspect the Applies to range and confirm it includes the intended cells. Check whether numbers are stored as text, whether dates are real Excel dates, and whether text has extra spaces or a different spelling.
  • The format does not update: Check that the value still meets the rule and that calculation is set to update as expected. A formula using TODAY() changes with recalculation; it is not a permanent timestamp. If two rules apply, review their order and formatting.
  • A color scale looks misleading: Remember that a scale compares values across its applied range. Narrow or expand the range intentionally, or use a fixed threshold rule when the business meaning depends on a specific number.
  • The sheet has become hard to read: Too many overlapping rules can make results confusing and may make a workbook slower to work with. Remove obsolete rules, use compact ranges, and prefer a small set of clear visual signals.

To diagnose a problem, open Manage Rules and verify three things: the condition, the Applies to range, and the rule’s position. Changing only the fill color will not correct a misplaced range or a formula that points to the wrong row.

Excel for Mac and Excel for the web

In current Excel for Mac, the main command is also on the Home tab under Conditional Formatting, though dialog layouts and some controls can differ from Windows. Excel for the web also provides conditional formatting from the Home tab, but the available controls and rule-management experience can vary. If a workbook contains a complex formula rule or you cannot find a particular setting in the web interface, open it in the desktop app and inspect the rule manager there.

Conditional formatting is a feature of Excel; it does not require a separate add-in or purchase beyond access to Excel. If you share a workbook, the rules are stored with it, but a recipient’s spreadsheet application may render some formatting differently. For critical reports, check the workbook in the application and version your readers will use.

Frequently Asked Questions

Can I conditionally format an entire row?
Select the rows’ full data range, then create a formula rule that tests the relevant column. For example, select A2:D100 and use the status-column formula shown above; the relative row lets each row respond to its own status. Confirm that the formula’s row matches the first selected row.

Can conditional formatting use a formula?
Yes. Choose Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format. The formula must return TRUE or FALSE, and references should be written with the selected range’s first cell in mind.

Does conditional formatting change the cell’s value?
No. It changes the cell’s appearance only. To change a value, use a worksheet formula, data entry, or another appropriate Excel tool; formatting is not a substitute for editing the underlying data.

How do I remove conditional formatting without deleting data?
Select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. The cells and their contents remain; the conditional formatting rules are removed from that selection.

Why is a date not being highlighted?
Check that Excel recognizes the entry as a date rather than text, that the rule includes the cell, and that its comparison matches the date you expect. For a rule using TODAY(), remember that today itself is not earlier than today, and that the function updates when Excel recalculates.

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.