Conditional Formatting Based on Value in Excel: Rules and Examples

In Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home > Conditional Formatting and select a rule such as Highlight Cells Rules > Greater Than. Enter the value and choose a format; Excel will apply it automatically wherever the condition is true. For more specific conditions, create a formula-based rule.

What Conditional Formatting Does

Conditional formatting changes how a cell looks when its contents meet a condition. It can apply a fill color, font color, border, data bar, color scale, or icon. The cell’s stored value does not change, and the formatting is not a replacement for the value itself. If a number changes and no longer meets the rule, Excel removes that conditional format and displays the cell’s other formatting, if any.

This makes conditional formatting useful for spotting patterns without sorting or filtering the data. For example, a sales sheet can flag results below a target, a task list can distinguish overdue items, and a gradebook can draw attention to low scores. Because the display updates as values change, the same rule can keep working as you enter new information.

Conditional formatting is separate from ordinary cell formatting. A manually applied red fill stays red until you change it, whereas a conditional red fill depends on a rule. If several rules apply to the same cell, their order and settings can affect what appears.

Built-In Rules for Numbers, Text, and Dates

Excel’s built-in rules are the quickest option when your condition is straightforward. Select the relevant cells and open Home > Conditional Formatting. Under Highlight Cells Rules, you can choose comparisons such as Greater Than, Less Than, Between, Equal To, or Text That Contains. Each command opens a dialog where you enter a comparison value and choose a format.

For example, to mark values above 100 in a selected range, choose Greater Than, enter 100, and select a format. The rule checks each cell in the selected range. To flag values within a range, choose Between and supply both endpoints. The treatment of the endpoints is part of the rule’s comparison, so check the dialog and preview when boundary values matter.

The same menu includes rules for dates occurring in a period, duplicate or unique values, and text containing a chosen phrase. Top/Bottom Rules can identify values such as the top ten items or values above average. These options are convenient for common comparisons, though some allow you to change the count or threshold. The exact choices depend on the rule type.

For ranges where relative size matters, use Color Scales, Data Bars, or Icon Sets in the Conditional Formatting menu. A color scale assigns colors based on a cell’s value relative to other cells in the selected range. Data bars show relative magnitude within each cell, while icon sets assign symbols to value bands. These visual rules are helpful for scanning trends, but a legend or clear column heading can help readers understand what the colors or icons mean.

Creating a Formula-Based Rule

Use a formula when a built-in comparison cannot express the condition you need, or when the formatting decision depends on another cell. Select the range first, then choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Use a formula to determine which cells to format. Enter a formula that returns TRUE for cells to format, choose Format to set the appearance, and select OK.

For example, with scores in A2:A10, select A2:A10 before creating the rule. The formula =A2<60 highlights scores below 60. Choose the fill or font style in the Format Cells dialog. The reference A2 is relative, so Excel adjusts it as it evaluates the rule for A3, A4, and the rest of the selected cells.

A second example can flag an entire row based on a status column. With task names in A2:A20, status in B2:B20, and days overdue in C2:C20, select A2:C20. Use the formula =AND($B2="Late",$C2>0) to format a row only when its status is Late and its overdue count is greater than zero. The dollar signs keep the test in column B and column C as the rule is evaluated across the selected row, while the row number changes for each row.

Formula rules need a logical result: TRUE means apply the chosen format, and FALSE means do not apply it. Formulas can use regular worksheet functions and comparisons, but they should be written with the top-left cell of the selected range in mind. A formula that is correct for one cell can produce an unexpected pattern if its references are shifted relative to the range.

Relative and Absolute References

Cell references determine how Excel checks the rule across the applied range. Relative references change as the rule moves from cell to cell. Absolute references, marked with dollar signs, hold a row, a column, or both fixed. Mixed references are useful when you want one part to move and another to stay put.

When formatting each cell based on its own value, a relative reference is usually appropriate. For a formula applied to A2:A10, the reference A2 shifts down for each row. When the condition should compare every value with one fixed target, use an absolute reference for the target. With values in A2:A10 and a threshold in D1, select A2:A10 and use =A2>$D$1. Changing D1 then changes which cells qualify without editing the rule.

When the rule applies across multiple columns, decide deliberately which column should stay fixed. In the row-highlighting example, $B2 means the rule always checks column B, but it checks the corresponding row. If you used B2 without the dollar sign, Excel could shift the status check into column C or another column as it formats across the row.

Choosing the Right Visual Format

A format should make the condition easier to interpret, not make the sheet harder to read. A restrained fill color or font color often works well for a simple alert. Use a data bar when comparing magnitudes across many values, and a color scale when relative high-to-low patterns matter. Icon sets can communicate categories quickly, but use them only when the thresholds are meaningful to the people reading the sheet.

Consider whether the color itself carries meaning. Red and green may be difficult for some readers to distinguish, and printed copies may not preserve color well. Pair color with text, symbols, or a clear heading when the distinction is important. Conditional formatting does not add a text label automatically, so an icon or color should not be the only way a critical status is conveyed.

Also consider whether the range can grow. If you format A2:A20 and later add a value in A21, that new cell is outside the existing range unless you extend the rule. Converting a data range to an Excel table can make it easier to work with expanding records, though you should verify that the rule’s Applies to range includes new rows as expected.

Rule Order, Overlaps, and Managing Rules

A cell may meet more than one conditional-formatting rule. To inspect or adjust rules, choose Home > Conditional Formatting > Manage Rules. In the rules manager, use the Show formatting rules for list to choose the current selection or another worksheet scope. The order shown there matters when rules apply conflicting formats. Excel evaluates rules in order, and Stop If True can prevent lower-priority rules from being evaluated for a cell.

For example, a cell might qualify both for a broad “below target” rule and a more specific “urgent” rule. Put the more specific rule first if it should control the appearance, and consider Stop If True where appropriate. The setting’s effect depends on how the rules overlap and which formatting attributes they set. If two rules change different attributes, such as one setting a fill and another setting a font color, both effects may be visible.

The manager is also where you can edit a rule, change its range, reorder it, or delete it. If the expected format does not appear, first check Applies to: the rule may cover the wrong cells or stop before the latest row. Also check that a higher-priority rule is not overriding the format you expected.

Common Problems and How to Fix Them

The wrong cells are highlighted: Check the Applies to range and the references in the formula. A formula rule is evaluated relative to the range’s top-left cell.
Nothing is highlighted: Confirm the condition is true for at least one cell. For formula rules, test the logic in a worksheet cell if needed, and check text for extra spaces or differences in spelling.
The whole range has the same result: A reference may be locked incorrectly. For per-row checks, the row reference often needs to remain relative; for a fixed threshold, lock the threshold cell.
A color remains after a rule is removed: The cells may also have ordinary formatting. Clear the direct formatting separately, or inspect and remove the relevant rule using Manage Rules.
New rows do not get formatted: Expand Applies to or confirm that the range is set up to include added records.

Numbers stored as text can also make comparisons behave unexpectedly. A cell may look like 100 but contain text rather than the number 100. Check the underlying data type, and use Excel’s number conversion tools or correct the source data where appropriate. Similarly, dates are stored as serial numbers in Excel; a date rule may not behave as intended if the entries are text strings rather than actual date values.

Excel for Mac, Excel for the Web, and Shortcuts

The article’s steps describe Excel for Microsoft 365 on Windows. In Excel for Mac, conditional formatting is available from the Home tab and the same general rule types and formula approach apply, though dialog layouts and keyboard access can differ slightly. In Excel for the web, use the Home tab’s Conditional Formatting controls; some advanced options or dialogs may differ from the desktop application, so open the workbook in desktop Excel if a specific rule setting is unavailable.

On Windows, Alt+H, L opens the Conditional Formatting menu through the ribbon KeyTips. On a Mac there is no identical Alt+H, L sequence; use the ribbon controls or the keyboard access methods supported by your macOS and Excel settings. To undo a recent formatting action, use Ctrl+Z on Windows or Command+Z on Mac, but undo is not a substitute for managing a rule that is already part of the workbook.

Frequently Asked Questions

Can conditional formatting use another cell’s value? Yes. Create a formula-based rule that refers to the comparison cell. Use an absolute reference if every cell should compare against one fixed target, and set the Applies to range to the cells you want formatted.

Can I highlight an entire row based on one cell? Yes. Select the full row range you want to format, then create a formula rule that locks the condition’s column while leaving the row reference relative. This lets each row’s format depend on its own status or other identifying value.

Does conditional formatting change the value in a cell? No. It changes the cell’s appearance only. Formulas, sorting, and calculations still use the underlying value, not the color or icon produced by the rule.

Can I apply more than one rule to the same cells? Yes. Multiple rules can apply to the same range. Use Manage Rules to review their order, formatting, Applies to ranges, and Stop If True settings when rules overlap.

How do I copy conditional formatting to another range? You can use Format Painter or copy and paste formats, then inspect Manage Rules to confirm the destination range and formula references are correct. Relative references may shift during copying, which can be useful but should be checked.

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.