Conditional Formatting Definition and Practical Examples

Conditional formatting is a spreadsheet feature that automatically changes a cell’s appearance when its value meets a condition. In Microsoft Excel for Microsoft 365 on Windows, you can use it to highlight important numbers, show patterns, and flag exceptions without changing the data stored in the cells.

What conditional formatting means

A conditional formatting rule tells Excel when to apply a visual style. The condition might be that a number is below a target, a date has passed, a cell contains particular text, or a value ranks among the highest in a range. When the condition is true, Excel applies the chosen format, such as a fill color, font style, border, data bar, color scale, or icon.

The word conditional is important: the appearance depends on the cell’s current value or, for some rules, the values in other cells. If the value changes and no longer meets the condition, Excel removes that conditional appearance. A cell may still have ordinary formatting—such as a manually chosen font color—underneath the conditional rule.

Conditional formatting is visual rather than corrective. It does not rewrite a value, change a formula result, or prevent someone from entering data. A red fill can draw attention to an overdue item, for example, but it does not make the item overdue or enforce a deadline. Use data validation or other controls when the goal is to restrict what can be entered.

Why spreadsheets use it

In a small table, a person can scan each number and spot an unusual result. That becomes harder as rows accumulate or values change frequently. Conditional formatting makes selected patterns visible as the sheet updates, so readers can focus on exceptions rather than inspect every entry manually.

The feature has roots in the broader practice of using visual signals to interpret tabular data. A spreadsheet rule automates that practice: instead of manually coloring every late task, a user defines what counts as late and lets the program apply the style to matching cells. The rule can keep working as data changes, although its range and logic still need to be set up correctly.

It is useful for dashboards, schedules, budgets, inventory lists, grade books, and quality checks. In each case, the key is to choose a condition that means something to the reader. A bright color without a clear threshold may attract attention but communicate little.

How conditional formatting works

Excel evaluates a rule against the cells in its Applies to range. That range may be one cell, a column of values, a rectangular table, or a larger selection. When a cell meets the condition, Excel displays the rule’s format. If several rules apply to the same cell, their order and settings can affect the final appearance.

Rules can use built-in comparisons, such as greater than, less than, equal to, or text that contains a phrase. Other built-in options identify dates, duplicate values, top or bottom rankings, and values above or below an average. More flexible rules use a formula that returns TRUE or FALSE. Excel evaluates the formula in relation to the selected range, which makes cell references and relative versus absolute references important.

Conditional formatting is dynamic: changing a value, changing a referenced threshold, or recalculating a formula can change which cells are formatted. The displayed style is not a separate data field. For example, a cell can look red because of a rule even though its ordinary Fill Color setting is not red.

Common rule types and examples

Excel’s built-in choices cover many everyday cases. Highlight Cells Rules can emphasize values that meet a comparison or contain specified text. Top/Bottom Rules can identify values by rank or by their relationship to an average. Data Bars add bars inside cells to compare magnitudes, while Color Scales shade cells along a gradient. Icon Sets display symbols, such as arrows or traffic-light icons, according to thresholds.

For example, a project tracker might use a date rule to flag deadlines that have passed. A sales sheet might use data bars to compare monthly totals. A survey summary might apply a color scale to show low and high ratings. A duplicate-value rule can help reveal repeated invoice numbers, but a duplicate is only a clue: it may be valid in some datasets, so inspect the records before deleting anything.

Choose the visual method to fit the task. A single fill color is often clearer for a yes-or-no warning. A color scale can show a range of values, but readers may need a legend or explanation to interpret it. Data bars help compare magnitudes, while icon sets work best when a small number of status categories is meaningful.

How to create a rule in Excel

In Excel for Microsoft 365 on Windows, select the cells to format, then use Home > Conditional Formatting. Choose a built-in rule category, set its condition, select a format, and confirm. To make a formula-based rule, select Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula, choose Format to set the appearance, and confirm the rule.

For a concrete example, suppose B2:B100 contains numeric sales results and E1 contains a target of 500. Select B2:B100 and create a formula rule using the following:

With sales values in B2:B100 and the target in E1, apply this formula to B2:B100: =AND(ISNUMBER(B2),B2<$E$1). If B2 contains 420 and E1 contains 500, the formula returns TRUE, so the chosen format is applied to B2. If B2 contains 620, it returns FALSE. ISNUMBER prevents blank or text entries from being flagged as if they were low numeric results.

The reference B2 is relative, so Excel adjusts it as the rule checks each row. The reference $E$1 is absolute, so every row compares its value with the same target cell. If the condition should format an entire row instead, select the full table range and write the formula using a fixed column and a relative row reference. Check the Applies to range in the rule manager so the rule covers the intended cells.

On Excel for Mac, the Conditional Formatting controls are also available from the Home tab, though the ribbon layout can differ by version and window size. Excel for the web offers conditional formatting from the Home tab as well, but some advanced rule editing and management options may vary from the desktop app.

Managing rule order and overlapping formats

A cell can meet more than one rule. For instance, one rule may color values below a target red while another applies a color scale to the whole range. Excel’s rule order and the formats selected determine what readers see when rules overlap. To inspect or change rules in desktop Excel for Windows, go to Home > Conditional Formatting > Manage Rules. Use the rule-management dialog to review the ranges, edit conditions, and change priority.

The Stop If True option can prevent lower-priority rules from being evaluated for a cell after a higher-priority rule is true. It is useful when rules are designed as ordered categories, but it can also make other expected formatting disappear. Before adding more rules, check whether an existing rule already covers the cells and whether two styles conflict.

Rule management matters especially in workbooks that have been edited repeatedly. Copying and pasting formatted cells, adding rules to different selections, or applying a rule to a larger range can leave overlapping or redundant rules. A quick review of the Applies to ranges and priorities can explain why a cell looks different from its neighbors.

Common problems and how to fix them

The wrong cells change appearance: Open Manage Rules and inspect Applies to. Confirm that the range begins on the row or column assumed by the rule’s formula.
A formula rule highlights the wrong rows: Check which references should move as Excel evaluates each cell. A relative reference such as B2 moves; an absolute reference such as $B$2 stays fixed.
Blank cells are unexpectedly highlighted: A comparison can treat a blank as zero in some formula contexts. Add a test such as ISNUMBER when the rule should apply only to numeric entries.
A rule seems to have no effect: Check that its condition is true for at least one value, that the format is visible, and that a higher-priority rule is not taking precedence.
Colors do not match the values after edits: Review the rule’s range and thresholds, and recalculate if the workbook’s calculation mode or formulas require it.
The sheet is hard to read: Reduce the number of competing colors and icons. Use a consistent meaning for each color and preserve readable text contrast.

When checking a formula-based rule, test the logic on a few representative rows. Include a value that should pass, one that should fail, and, if relevant, a blank or text entry. This helps separate a formula problem from a range or formatting problem.

Conditional formatting versus related tools

Conditional formatting is different from ordinary cell formatting. Ordinary formatting applies a chosen style directly; a conditional rule applies a style only while its condition is met. It is also different from sorting or filtering: the rule changes appearance, while sorting changes row order and filtering hides rows that do not meet a selection.

It is not the same as data validation. Data validation can restrict entries or show an input message or error alert, depending on how it is configured. Conditional formatting can warn that an existing value looks unusual, but it does not stop that value from being entered. If you need both a warning and an entry restriction, the two features may be used together.

Nor is a colored cell a reliable substitute for storing a category in the data. A manually colored cell may not explain why it has that color, and formatting can be removed or changed. If the status matters for filtering, reporting, or later calculations, store it in a dedicated field and use formatting as a visual aid.

Good practices for useful rules

Define the meaning of each condition before choosing colors, and use thresholds that match the task.
Apply rules to the smallest sensible range. This makes the logic easier to review and reduces accidental formatting elsewhere.
Use clear, restrained styles. Keep text readable and avoid relying on color alone when an icon, label, or explanatory note would make the meaning clearer.
Test boundary values. If a threshold is 500, check what should happen at 499, 500, and 501.
Review rules when the layout changes, especially after inserting columns, expanding tables, or copying cells.
Keep important statuses in data as well as appearance so that users can sort, filter, and understand the workbook reliably.

These habits make the sheet easier for other people to interpret. They also make troubleshooting simpler: a small number of clearly named or logically separated rules is easier to audit than a collection of overlapping effects.

Frequently Asked Questions

What is conditional formatting in simple terms?
It is a spreadsheet feature that automatically changes how a cell looks when its value meets a rule, such as being below a target or containing a particular date.

Does conditional formatting change the cell value?
No. It changes the cell’s appearance, not the stored value or the result of its formula. The underlying data remains available for calculations, sorting, and filtering.

Can conditional formatting use a formula?
Yes. In Excel, a formula-based rule can test the selected cell or refer to other cells. The formula should return TRUE or FALSE, and its references must be set up for the range where the rule applies.

Why is conditional formatting not working?
Common causes include an incorrect Applies to range, a formula reference that is locked or moving incorrectly, a condition that is never true, or another rule taking priority. Review the rules under Home > Conditional Formatting > Manage Rules.

Can I use conditional formatting to prevent an incorrect entry?
No. It can highlight a value that appears incorrect, but it does not block entry. Use data validation when you need to limit what can be entered, and use conditional formatting when you want a visual warning.

Will conditional formatting update when the data changes?
Usually, yes. Excel reevaluates the applicable rules as values or referenced cells change. The result depends on the rule, its range, and the workbook’s calculation behavior.

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.