Conditional Formatting in Spreadsheets: How It Works

Conditional formatting in a spreadsheet automatically changes a cell’s appearance when its value meets a condition; in Excel for Microsoft 365 on Windows, select the cells and choose Home > Conditional Formatting to create a rule. It can make overdue dates, unusual figures, duplicates, or progress levels easier to spot without changing the underlying data.

What conditional formatting means

A conditional format is a visual rule attached to one or more cells. When a cell’s value satisfies the rule, Excel applies a format such as a fill color, font color, border, data bar, or icon. If the value changes and no longer meets the condition, Excel updates the display accordingly. The cell still contains its original value or formula: formatting does not replace data, calculate a new result, or remove a row.

For example, a sales table can highlight amounts below a target in red. A task list can shade completed work green, while a date column can flag deadlines that have passed. These cues help readers find patterns in a large sheet faster than scanning every entry individually.

How Excel evaluates formatting rules

Excel checks a rule against its selected range and displays the assigned format wherever the condition is true. A rule can test a fixed threshold, compare a cell with another value, look for duplicate entries, or use a formula. Rules can also assign a visual scale across a range, so higher and lower values receive different colors or bar lengths.

A conditional format is not the same as changing a cell’s number format. Number formats control how values appear—for example, displaying a date as “3 Nov” or a decimal as a percentage. Conditional formatting adds a visual cue based on a condition. The stored value remains available for calculations, sorting, and filtering.

Rules are associated with ranges, and their results may change when data is edited, recalculated, or refreshed. A color in a cell therefore signals that a rule currently applies; it is not a separate data value. If a workbook’s rules are removed or changed, the cell’s original contents remain.

Common types of conditional formatting

Excel’s built-in options cover many everyday uses. Select the relevant cells, then open Home > Conditional Formatting. Depending on the option, Excel offers a dialog where you enter a value, choose a comparison, or select a visual style.

Highlight Cells Rules: highlight values greater than, less than, equal to, or between specified values; identify text that contains a phrase; or flag dates in a selected period.
Top/Bottom Rules: emphasize top or bottom values, a percentage of the range, or values above or below the average.
Data Bars: show a bar inside each cell, with longer bars representing larger values. These can make relative amounts visible while retaining the numbers.
Color Scales: shade cells along a gradient based on their values. A two-color scale compares low and high values; a three-color scale can also distinguish a midpoint.
Icon Sets: display symbols such as arrows or traffic-light icons according to value thresholds. The number remains in the cell, although icons may need explanatory labels to avoid ambiguity.
Duplicate Values: mark repeated or unique entries in the selected range, useful for an initial check of IDs, names, or invoice references.

These choices are starting points rather than guarantees of meaningful analysis. A color scale can make the highest value in a range look important even when the difference is trivial, for instance. Choose a rule that matches what readers need to notice.

Create a rule with a practical example

To highlight overdue tasks in a table, first decide which cells should receive the formatting. If the entire task row should be shaded, select the rows’ data range rather than just the due-date column. On Excel for Microsoft 365 on Windows:

Select the range to format, such as A2:D100.
Choose Home > Conditional Formatting > New Rule.
Select “Use a formula to determine which cells to format.”
Enter the formula below, choose Format to set the desired appearance, and select OK.

Assuming due dates are in D2:D100 and the rule applies to A2:D100, use =AND($D2<>"",$D2<TODAY()). Excel highlights each task row whose due date is earlier than today and ignores empty due-date cells. TODAY() uses the current date when Excel recalculates, so a task can become overdue as time passes without someone editing its date.

The dollar sign before D locks the date column, while the row number remains relative. That lets Excel check column D for each successive row while applying the format across columns A through D. When writing a formula rule, the formula is interpreted relative to the top-left cell of the selected “Applies to” range. A different selection may therefore require adjusting the row reference.

For a simpler rule, suppose task status is in B2:B100 and the formatting should cover A2:D100. Use =$B2="Complete" as the formula. Each row is highlighted when its status cell says Complete. The exact text in the condition must match the values in the sheet; a misspelling or extra space can prevent a match.

Choosing a format that communicates clearly

Use a visual treatment that helps someone interpret the data rather than merely decorating it. A single, consistent fill is often effective for exceptions such as overdue items. Data bars are useful for comparing magnitudes within a range, while color scales can reveal a broad distribution. Icon sets can support quick status checks, but icons should not be the only explanation of what a result means.

Use a short, understandable rule: for example, “below 0” or “date is before today.”
Keep color meanings consistent across related sheets, such as red for attention and green for complete.
Check that text remains readable against the chosen fill and that the distinction does not depend on color alone.
Consider the range carefully. Formatting an entire row can make a status easier to see, but may also create visual clutter in a wide table.
For a color scale, consider whether the minimum, midpoint, and maximum have meaningful reference values or whether Excel should base the scale on the selected data.

Conditional formatting can help readers with color-vision differences only if the workbook also provides other clues, such as clear status text, symbols with labels, or an accompanying legend. A red cell alone may communicate little to someone who cannot distinguish its color from the background.

Rule priority and managing existing rules

A cell can be covered by more than one conditional formatting rule. When multiple rules apply, their order can affect the displayed result, especially when rules set the same kind of format. Excel evaluates rules in the order shown in its rules manager. Some rules also have a “Stop If True” option, which prevents lower-priority rules from being evaluated for a cell when the current rule is true.

To inspect or change rules, select the relevant cells and choose Home > Conditional Formatting > Manage Rules. In the rules manager, use the “Show formatting rules for” menu to view rules for the current selection, the worksheet, or other available scopes. Check the “Applies to” range as well as the condition: a correct rule attached to the wrong cells can produce confusing results.

When deleting formatting, distinguish between clearing a rule and clearing cell contents. Home > Conditional Formatting > Clear Rules offers options to remove rules from selected cells or the entire sheet. It does not mean the values themselves should be deleted. If an unexpected color remains, inspect the rules and the cell’s ordinary formatting; a manually applied fill is separate from conditional formatting.

Common problems and how to diagnose them

If a rule appears not to work, first check the selected range and the rule’s “Applies to” address. Next compare the condition with the actual data. A date that looks like a date may be stored as text, and a number imported as text may not behave like a numeric value. Blank cells, extra spaces, inconsistent spelling, and dates outside the expected range are also common sources of surprises.

Formula-based rules often fail because the reference shifts in an unintended way. For a rule applied across several columns, locking a column with a dollar sign can keep the test focused on one status or date column; leaving the row relative lets the test move down the table. Conversely, locking both the column and row fixes a reference to one cell, which is useful for a single shared threshold but usually wrong for a row-by-row test.

When a rule uses TODAY(), note that Excel must recalculate for the current date to be reflected. Calculation settings and workbook activity can affect when results update. If several rules produce an unexpected appearance, open Manage Rules, inspect their order, and check for a manually applied fill that may be confusing the visual result. For very large ranges, unnecessary rules over whole columns can also make a workbook harder to manage and may affect responsiveness.

Conditional formatting compared with related features

Conditional formatting is for visual emphasis, not for controlling what users are allowed to enter. If a status field should accept only a set of choices, use Data Validation instead. In Excel for Microsoft 365 on Windows, the path is Data > Data Validation. A dropdown can restrict entries to approved values; conditional formatting can then highlight one of those values. The features solve different problems and can be used together.

Filtering and sorting also serve a different purpose. A filter hides rows that do not meet the selected criteria, while conditional formatting leaves the rows visible and marks matching cells. Cell styles apply a chosen appearance directly, without a condition that responds to changing values. A chart can show overall comparisons or trends; formatting can help readers spot individual values within the underlying table.

Excel for Mac has conditional formatting on the Home tab, though dialog details and layout can differ from Windows. Excel for the web also supports conditional formatting, but some editing options and interface details can vary from the desktop application. If a workbook is shared across platforms, verify that rules display as intended in the version other collaborators use.

Frequently Asked Questions

Does conditional formatting change the value in a cell?
No. It changes the cell’s appearance when a rule is true. The underlying number, date, text, or formula result stays in the cell.

Can I highlight an entire row based on one cell?
Yes. Select the rows’ full data range and create a formula rule that refers to the status or date column. Use a dollar sign before the column letter when the test should always read that column, while leaving the row number relative for row-by-row checks.

Can conditional formatting identify duplicates?
Yes. Select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Review the selected range first: Excel checks for repeats within that range, so selecting the wrong cells can produce a result that does not match your intended comparison.

Why does a conditional format not update?
Check whether the rule covers the edited cells, whether the data type and condition match, and whether another rule affects the appearance. Rules using TODAY() also depend on Excel recalculating the workbook to reflect the current date.

Can I copy conditional formatting to other cells?
Yes. You can use Format Painter or copy and paste formatting, then inspect Manage Rules to confirm the “Applies to” range and any relative formula references are correct. Copying a rule does not guarantee that its references suit a differently arranged table.

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.