Excel Conditional Formatting for Values Less Than a Cell

How-to basics · Updated November 18, 2026

To highlight Excel values that are less than a value in another cell, select the cells to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a comparison formula. For example, if the values are in A2:A20 and the limit is in B1, use =A2<$B$1, choose a format, and select OK.

Set up a less-than rule

In Excel for Microsoft 365 on Windows, the formula-based rule is the dependable choice when the comparison value comes from a worksheet cell. Follow these steps:

  1. Select the range whose values should be checked, such as A2:A20.
  2. Choose Home > Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format.
  4. Enter the formula for the comparison, then choose Format to set a fill color, font, border, or number format.
  5. Select OK to save the format and OK again if the rule dialog remains open.

With values in A2:A20 and the limit in B1, the formula is =A2<$B$1. The first reference, A2, identifies the top-left cell of the selected range. The second reference, $B$1, fixes the threshold cell so every value in A2:A20 is tested against B1. If A2 is 12 and B1 is 15, A2 meets the rule and is formatted; if A2 is 18, it is not.

The formatting is conditional: it changes how qualifying cells look, not the values they contain. Changing B1 recalculates the comparison and can move the highlight to different cells. This makes the rule useful for targets, minimum stock levels, budgets, test scores, and other thresholds that may change over time.

How the formula and references work

A conditional-formatting formula must return TRUE or FALSE. Excel evaluates it for each cell in the selected range, then applies the chosen format when the result is TRUE. The less-than operator is <. In a formula, it compares the value on the left with the value on the right; it does not change either value.

Excel adjusts relative references as it evaluates the rule across the range. If the selected range begins at A2, the formula =A2<$B$1 checks A2 against B1, then effectively checks A3 against B1, A4 against B1, and so on. The dollar signs make B1 an absolute reference: both its column and row stay fixed.

Reference style matters. A2 is relative in both directions; $A$2 is fixed in both directions; $A2 fixes the column but lets the row change; and A$2 fixes the row but lets the column change. A formula that starts with the wrong reference can highlight cells unexpectedly, even if the comparison itself looks right.

For instance, if you select B2:B20 but enter a rule beginning with A2, Excel evaluates the value in column A for each corresponding row. That may be intentional when the formatting should depend on a neighboring column. If you meant to evaluate the selected cells themselves, begin with B2 instead.

Compare each row with its own cell

Sometimes each value has a different comparison value on the same row. For example, column A might contain actual results and column B might contain a separate target for each person. Select A2:A20 and use =A2<B2. With A2 equal to 7 and B2 equal to 10, A2 is formatted; the next row compares A3 with B3.

Here, both references are relative because the comparison should move down with the row. If the target is always in column B but the selected range spans several columns, lock the target column. For a selected range starting at A2, a formula such as =A2<$B2 keeps the comparison in column B while allowing its row to change. The best reference pattern depends on the top-left cell and shape of the range selected.

One common mistake is to select an entire range but write a formula as if the first cell were somewhere else. Conditional formatting does not automatically guess which part of a formula should move. Check the first cell in the Applies to range and make sure the formula’s relative reference points to that cell, or to the intended comparison cell on the same row.

Use a fixed number or a cell reference

If the cutoff is a constant that will not change, a built-in rule is quicker. Select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter the cutoff value, choose a preset format or Custom Format, and confirm. This works well for a straightforward rule such as highlighting scores below 60.

Use a formula-based rule when the cutoff is stored in a cell, when the comparison must move by row, or when the condition needs extra logic. A cell reference in a formula is easier to audit and update than a number embedded in a rule. For a fixed threshold in B1, enter the new value in B1 rather than rebuilding the rule whenever the limit changes.

Excel also offers greater-than, equal-to, between, and other built-in Highlight Cells Rules. Those choices are convenient for simple, fixed comparisons. They are not a substitute for formula rules when the condition needs to compare two worksheet cells or combine tests. If a built-in dialog does not behave as intended for a cell-driven threshold, use New Rule and enter the explicit formula instead.

Exclude blanks and handle data types

Blank cells can produce confusing results in comparisons, especially when the threshold is positive. If blank cells should never be highlighted, add a nonblank test. With values in A2:A20 and a fixed threshold in B1, use =AND(A2<>"",A2<$B$1). This rule requires A2 to be nonblank and less than B1. The reference to A2 still adjusts down the selected range.

Check that the values and thresholds are numbers if the rule is meant to compare numbers. A value that looks numeric may actually be text, for example when it was imported with a leading apostrophe or stored with an inconsistent format. Changing the cell’s number format alone does not always convert text to a number. Excel’s built-in error indicators, VALUE conversion, or a careful data import can help correct such data, depending on its source.

Errors in a cell used by a comparison can also prevent the intended result. Inspect the threshold cell and the compared cells if highlights seem inconsistent. Dates are stored as serial numbers in Excel, so date comparisons can work, but both sides should contain real date values rather than date-looking text. Also confirm that the threshold is the right kind of value: comparing a percentage such as 0.1 with a whole-number scale such as 10 gives a valid calculation but may not express the intended comparison.

Edit, review, and prioritize rules

To inspect or change an existing rule, choose Home > Conditional Formatting > Manage Rules. In the rules manager, review the rule’s formula, formatting, and Applies to range. Depending on the selection and options shown, you may need to change the Show formatting rules for control to This Worksheet to find rules elsewhere on the sheet.

The Applies to range is important. A correct formula attached to the wrong range can appear broken, and a rule may cover more cells than expected if it was copied or extended. Update the range when rows are added or removed, and check the formula’s first relative reference against the top-left cell of that range.

When several conditional-formatting rules apply to the same cell, their interaction can affect the appearance. In Manage Rules, Excel displays rule priority, and the Stop If True option can prevent lower-priority rules from being applied when an earlier rule is true. If a cell has an unexpected color or font, check for overlapping rules and their order, rather than assuming the less-than test itself is wrong.

Copying and pasting can also bring conditional formatting along, sometimes adding overlapping rules. If the rule count grows or formatting becomes inconsistent, inspect Manage Rules and remove rules that are no longer needed. Use the Conditional Formatting menu’s Clear Rules commands carefully: clearing rules removes conditional formatting, not the underlying cell values.

Common problems and practical checks

If nothing highlights, verify that at least one value really is less than the threshold, that the formula begins with an equals sign, and that the rule applies to the intended range. Then check that the formula starts with the correct top-left cell reference and that the threshold cell contains the expected value. A very similar-looking absolute or relative reference can change the result across rows.

If every cell highlights, the formula may be comparing against the wrong cell, or the threshold may be larger than all the values. If only the first row behaves correctly, a reference may be locked when it should move, or the selected range may start on a different row from the formula. If blank cells are colored, add the nonblank condition described above.

  • Check the comparison direction: A<B means A is less than B; reversing the references tests a different condition.
  • Check the starting row: A2 is appropriate when the selected range starts at row 2, not row 1.
  • Check the threshold: Confirm that the referenced cell is the intended limit and contains a numeric value or valid date.
  • Check competing formats: Another rule may add a fill, font, or border that obscures the result.
  • Check the range: Confirm that Applies to includes every cell that should be evaluated.

On Excel for Mac, the same general formula approach applies, though the ribbon and rule-management dialogs can look different. In Excel for the web, conditional formatting is available from Home > Conditional Formatting, but the controls may differ from the Windows desktop dialogs. If a workbook’s rule is difficult to edit in the web interface, open it in the desktop app and review it there.

Frequently Asked Questions

Can conditional formatting compare a cell with another cell?
Yes. Select the cells to format, create a formula-based rule, and compare the selected cell with the other cell. For values in A2:A20 compared with B1, use =A2<$B$1.

How do I highlight values below a different limit in each row?
Select the values in A2:A20 and use =A2<B2 when each row’s limit is in column B. Both references move down row by row, so each value is tested against its own limit.

Can I use a cell reference in the built-in Less Than rule?
For a reliable comparison with another cell, use New Rule and a formula rather than relying on the built-in Less Than dialog. The formula makes the cell-to-cell comparison and reference behavior explicit.

Why are blank cells highlighted as less than my limit?
A blank may participate in a comparison in a way that makes it qualify against a positive threshold. Add a nonblank test, such as =AND(A2<>"",A2<$B$1), with A2 as the first cell in the selected range and B1 as the fixed limit.

Does conditional formatting change the cell’s value?
No. It changes the cell’s appearance when the rule is true. Formulas that refer to the cell still use its underlying value, and changing or deleting the formatting rule does not edit that value.

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.