Conditional Formatting in Excel: Practical Guide

Conditional formatting changes a cell’s appearance when a rule is true. It can flag overdue work, reveal unusually high or low values, identify duplicates, or add compact visual cues without changing the underlying data.

The feature is most useful when each color, icon, or bar answers a specific question. This guide shows how to choose a rule, build formula-based row highlights, manage conflicts, and fix formatting that applies to the wrong cells.

What conditional formatting does

A conditional-formatting rule has three parts: the cells it evaluates, the condition it tests, and the format it displays when the condition is met. Excel recalculates the rule as the worksheet’s values change.

The format is separate from the value. A red fill does not turn a number into text, alter a date, or block an entry. It only changes how the cell looks while the rule evaluates as true. Data validation, formulas, and conditional formatting solve different problems even when they use similar conditions.

Use formatting to direct attention, not to store meaning. Keep a Status, Priority, or Category field in the data so the workbook remains understandable when printed without color or viewed by someone who cannot distinguish the chosen colors.

Choose the right rule type

Build a sample project tracker

The examples use this range in A1:F9:

Enter the due dates as real Excel dates and the progress values as percentages. A date or percentage stored as text may not behave correctly in a numeric or date-based rule.

Create a simple value rule

Start by highlighting progress values below 80%:

1. Select E2:E9.
2. Choose Home > Conditional Formatting > Highlight Cells Rules > Less Than.
3. Enter 80%.
4. Choose a readable fill or create a custom format, then select OK.

A Less Than conditional formatting rule highlights progress values under 80% in the Project Tracker table.

Excel applies the format to each selected cell whose numeric value is less than 80%. If a progress value later changes to 80% or more, the highlight disappears automatically.

The same menu includes rules for Greater Than, Between, Equal To, text that contains a phrase, date periods, and duplicate values. Use these built-in rules when one cell can be judged on its own.

Highlight an entire row with a formula

A formula rule can evaluate one or more fields and format the whole record. To highlight overdue tasks that are not complete:

1. Select the data rows A2:F9, beginning with the upper-left cell A2.
2. Choose Home > Conditional Formatting > New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter the formula below.
5. Select Format, choose a fill or font treatment, and confirm the rule.

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

A conditional formatting formula highlights full rows for overdue tasks while excluding completed tasks and records with blank due dates.

The rule checks the Due date in column D, excludes rows marked Done, and ignores blank due dates. Because the rule applies to A2:F9, every cell in a qualifying row receives the chosen format.

Why the dollar signs matter

Excel interprets the rule from the upper-left cell of the Applies to range and adjusts relative references for the rest of that range. In $D2, the dollar sign locks the test to column D while the row number changes from 2 to 3, 4, and onward. The same pattern applies to $C2.

If a threshold lives in one fixed cell, lock both its column and row. For example, if H2 contains a target progress value, this rule can format rows below that target:

=$E2<$H$2

The $E2 reference follows each record, while $H$2 always points to the same threshold. Microsoft’s explanation of relative, absolute, and mixed references covers the behavior behind these formulas.

Build useful formula rules

Once the Applies to range and reference pattern are correct, formula rules can express more specific business logic.

Tasks due in the next seven days

Apply this formula to A2:F9 to flag unfinished tasks due between today and seven days from today:

=AND($D2>=TODAY(),$D2<=TODAY()+7,$C2<>"Done")

Missing owner on a populated record

This formula highlights rows with a Task ID but no owner:

=AND($A2<>"",$B2="")

Duplicate task IDs

To format the entire row when its Task ID appears more than once, apply this formula to A2:F9:

=COUNTIF($A$2:$A$9,$A2)>1

For highlighting only the duplicate cells in A2:A9, the built-in Duplicate Values rule is quicker. The formula is useful when the format must extend across the full record.

Compare values with data bars

Data bars place a horizontal bar inside each selected numeric cell. A longer bar represents a larger value relative to the other values in the range. They work well for Progress because the reader can compare magnitude without losing the number.

1. Select E2:E9.
2. Choose Home > Conditional Formatting > Data Bars.
3. Select a solid or gradient style.
4. Open Manage Rules and edit the rule if you need fixed minimum and maximum values, such as 0 and 1 for percentages.

Data bars in the Progress column make it easier to compare task completion percentages at a glance.

Fixed endpoints can make repeated reports easier to compare. If Excel calculates the minimum and maximum from each changing data set, the same value can produce a different bar length next month.

Use color scales for distributions

A two- or three-color scale maps low, middle, and high values to a gradient. Select F2:F9, choose Conditional Formatting > Color Scales, and select a scale whose direction matches the meaning of Budget variance.

A three-color scale highlights the distribution of budget variance values, making low and high results easier to compare.

Do not assume green should always represent the largest number. A positive variance may be favorable in one workbook and undesirable in another. Edit the rule’s minimum, midpoint, maximum, and colors so the visual meaning matches the measure.

Microsoft’s guide to data bars, color scales, and icon sets explains how their thresholds control the display.

Classify values with icon sets

Icon sets divide numeric values into three to five bands. To classify Progress:

1. Select E2:E9 and choose Conditional Formatting > Icon Sets.
2. Choose a three-icon set.
3. Open Manage Rules, select the rule, and choose Edit Rule.
4. Change the threshold Type to Number if you want fixed percentage cutoffs.
5. Set the upper threshold to 0.8 and the lower threshold to 0.5, then confirm the rule.

A three-icon conditional formatting rule classifies progress values into high, medium, and low ranges using fixed number thresholds.

With those thresholds, values at or above 80% receive the highest icon, values from 50% up to 80% receive the middle icon, and values below 50% receive the lowest icon. Choose Reverse Icon Order if the set’s direction contradicts the measure, or Show Icon Only when the underlying number does not need to appear.

Threshold trap: in an icon-set rule, Percent and Percentile are not the same as typing a percentage value stored in a cell. Choose Number when your thresholds are fixed values such as 0.8 and 0.5.

Highlight top, bottom, and above-average values

Top/Bottom Rules compare selected cells with each other. They can highlight the top or bottom number of items, a top or bottom percentage, or values above or below the selected range’s average.

An Above Average rule highlights progress values that exceed the average of the selected range.

These rules are relative to the current Applies to range. If that range expands or changes, a different set of cells may qualify even though their values stay the same. Use a direct value rule when the threshold must remain fixed.

Manage rule order and conflicts

Several rules can apply to the same cells. Excel evaluates them from top to bottom in the Conditional Formatting Rules Manager. When formats conflict—for example, two rules assign different fill colors—the rule higher in the list has precedence.

1. Select a cell in the affected area.
2. Choose Home > Conditional Formatting > Manage Rules.
3. Change the scope to the current selection or worksheet so every relevant rule is visible.
4. Inspect each rule, its format, and its Applies to range.
5. Move the most important rule higher, edit overlapping formats, or remove obsolete rules.

The Conditional Formatting Rules Manager shows how rule order controls formatting priority when multiple rules apply to the same cells.

Stop If True prevents lower rules from being evaluated after that rule is true. Use it only when the rules are meant to be mutually exclusive. The setting is not available for rules that use data bars, color scales, or icon sets.

Conditional formatting also takes precedence over conflicting manual formatting while its rule is true. Removing the conditional rule reveals the cell’s remaining manual format. Microsoft’s full conditional-formatting reference documents rule precedence, management, and clearing.

Edit the range or remove a rule

Use the Rules Manager to change the Applies to range when new rows are not formatted or unrelated cells are included. Check that the range begins on the same row the formula was written for.

To remove rules without deleting values:

• Select cells and choose Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
• Choose Clear Rules from Entire Sheet only when every rule on that worksheet should be removed.
• Open Manage Rules to delete one named rule while preserving the others.

Fix common conditional-formatting problems

Frequently asked questions

Make each rule answer one question

A dependable rule begins with a precise question: Which tasks are overdue? Which values fall below target? Which records are missing an owner? Choose the simplest built-in rule that answers it, and use a formula only when the condition spans fields or must format a whole row.

Keep the number of competing colors low, label the underlying categories, and review the Rules Manager when results look surprising. The most useful conditional formatting makes exceptions obvious without making the worksheet harder to read.