Yes. In Excel for Microsoft 365, you can use conditional formatting to highlight a cell when its value is greater than another cell. For a comparison against a single threshold, create a formula rule such as =A2>$B$1; for row-by-row comparisons, use a relative reference such as =A2>B2.
What comparing cells means
A greater-than rule checks whether one value exceeds another. The comparison can be between a value in each row, such as actual sales in column A versus targets in column B, or between many values and one shared threshold, such as each score compared with a minimum in B1. Conditional formatting changes how matching cells look; it does not change their values or the results of calculations.
Greater than is a strict comparison: if the two values are equal, the rule is false and the format is not applied. If equality should also count, use a greater-than-or-equal comparison instead. Decide whether you want to format just the values being checked or whole rows of related data; the selected range and formula references determine the result.
Set up a rule against one threshold
For a single cutoff that applies to a range, select the cells to format and create a formula-based rule. In Excel for Microsoft 365 on Windows, the steps are:
- Select the range to format, for example A2:A20.
- Choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Enter the formula, choose Format to set a fill, font, or border, and select OK.
With values in A2:A6 of 12, 8, 15, 20, and 9, and a threshold of 10 in B1, the formula below highlights A2, A4, and A5. The dollar signs keep the threshold fixed as Excel evaluates the rule for each cell in the selected range.
With those values in A2:A6 and the threshold in B1, this formula returns TRUE for 12, 15, and 20 and FALSE for 8 and 9:
=A2>$B$1
Excel adjusts the unanchored A2 reference as it checks each cell in the range. Thus, the first cell is compared with B1, the next with B1, and so on. The threshold cell remains B1 for every comparison.
Compare each row with another cell
When every row has its own comparison value, use relative references for both cells. For instance, you might highlight a sale amount in column A when it exceeds the target on the same row in column B. Select A2:A20 before creating the formula rule so that A2 is the top-left cell of the range being formatted.
With actual values 120, 90, and 150 in A2:A4 and targets 100, 100, and 160 in B2:B4, the formula below highlights A2 only: 120 is greater than 100, while 90 is not greater than 100 and 150 is not greater than 160.
With actuals in A2:A4 and row-specific targets in B2:B4, this formula compares each pair on the same row:
=A2>B2
If you want to highlight the entire row rather than just the actual value, select the rows you want formatted, such as A2:D20, and anchor the comparison columns while leaving the row number relative. With actuals in column A and targets in column B, the formula below highlights each selected row when its actual exceeds its target. For the example above, only row 2 is highlighted.
With A2:D20 selected and actuals in column A and targets in column B, this formula checks each row and keeps the comparison in columns A and B:
=$A2>$B2
Understand relative and absolute references
Conditional-formatting formulas are evaluated relative to the top-left cell in the Applies to range. This is why the starting cell in the formula must match the first cell you selected. If the range starts at A2 but the formula starts by checking A1, Excel applies the logic from the wrong row.
- A relative reference such as A2 shifts across or down as Excel evaluates other cells.
- An absolute reference such as $B$1 stays fixed in both column and row.
- A mixed reference such as $A2 fixes the column but lets the row change; A$2 fixes the row but lets the column change.
These distinctions help when formatting a grid. For example, a rule applied across several columns might compare each cell with a threshold in row 1. Locking the row but not the column lets the threshold move across columns while staying on row 1. Before applying a rule to a large range, test it on a few cells and confirm that the relative references follow the intended pattern.
Choose a built-in rule or a formula
For a simple threshold, the built-in option is quick: select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than. Enter the comparison value and choose a format. Depending on the dialog and the type of reference you enter, a cell reference can also serve as the threshold; check the preview and the rule manager to confirm it behaves as intended.
A formula rule is usually clearer when you are comparing two ranges row by row, formatting entire rows, or combining conditions. It also makes the reference behavior visible in the rule itself. Use the built-in rule when a straightforward threshold is all you need; use a formula when the relationship between cells matters. Both approaches affect display only, so the original data remains available for sorting, calculation, and further analysis.
Check and edit the rule
To inspect a rule in desktop Excel, choose Home > Conditional Formatting > Manage Rules. Use the Show formatting rules for list to view rules for the current selection or worksheet. Check the formula and the Applies to range together: a correct formula applied to the wrong cells can look like a formula error.
If the rule applies to A2:A20, its formula should be written as though it is checking A2 first. If you add rows later, check whether the Applies to range includes them. You can edit the range in the manager rather than rebuilding the rule. When rules overlap, their order and settings can affect which format appears; inspect the rule list if the displayed color does not match your expected result.
Common problems and how to fix them
- The wrong row is highlighted: Confirm that the formula starts with the top-left cell of the Applies to range. A one-row offset is a common cause of surprising results.
- The threshold moves unexpectedly: Use dollar signs to anchor a single threshold cell. For a row-by-row comparison, do not lock the row if the rule needs to move down with the data.
- Nothing is highlighted: Check that the values are numbers, not text that only looks numeric, and verify that the comparison is strictly greater than rather than greater than or equal to. Also check the selected range and format preview.
- Blank cells get highlighted: A blank may behave like zero in a numeric comparison. If the threshold is negative, that can make an empty cell appear greater than the threshold. Add a numeric-value check when blanks or text are possible.
With values to test in A2:A20 and a numeric threshold in B1, the formula below applies the comparison only when both the current cell and threshold are numbers. It prevents blanks and text entries from passing the rule; cells with numeric values above B1 are highlighted.
With A2:A20 as the range and B1 as a numeric threshold, this formula returns TRUE only for numeric values greater than the threshold:
=AND(ISNUMBER(A2),ISNUMBER($B$1),A2>$B$1)
Another frequent issue is entering a formula based on a different starting cell than the selected range. If your range begins at C5, rewrite the relative reference to begin with C5. You can also temporarily use an obvious format, such as a bright fill, while checking the rule, then replace it with the final style.
Excel on Mac and in a browser
In Excel for Mac, conditional-formatting commands are also on the Home tab, though the rule dialog can look different from the Windows version. Select the range, open Conditional Formatting, and choose the option for creating a new rule; look for the formula-based rule type. The same relative and absolute reference principles apply.
Excel for the web supports many conditional-formatting tasks, but available rule controls can differ from desktop Excel. If the formula-rule option or a needed editing control is not available in your current interface, open the workbook in the desktop app. After creating or editing a rule, verify it in the target range, especially if other people will use the workbook in a different Excel version.
Frequently Asked Questions
Can conditional formatting compare two cells?
Yes. Create a formula rule that compares the cells, and select the range you want to format. Use relative references for row-by-row comparisons, or anchor references when one cell should remain the threshold.
Does greater than include values that are equal?
No. Greater than means the first value must be strictly larger. If equal values should also receive the format, use a greater-than-or-equal comparison in the formula rule.
Can I highlight an entire row when one cell is greater than another?
Yes. Select the full row range to format and write the formula to compare the relevant columns. Lock the comparison columns if needed, while leaving the row number relative so each row is checked separately.
Will the formatting change the cell value?
No. Conditional formatting changes the appearance of cells, not their contents. The values remain the same for formulas, sorting, and other workbook operations.
Why does my rule highlight the wrong cells?
Check the Applies to range, the formula’s first reference, and whether dollar signs lock the references as intended. Also verify that the values are numeric and that the comparison is the one you mean.
Can the threshold update automatically?
Yes. A rule that refers to a threshold cell will recalculate when that cell’s value changes. Anchor the threshold reference if one shared cell should control every comparison.
Related
- Excel Print Shortcut: How to Print a Worksheet
- Excel Number Format Shortcut: Quick Keys and Examples
- Excel New Comment Shortcut: Windows, Mac, and Notes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Share Calendar Button Does Nothing? Fixes That WorkIf Outlook’s Share Calendar button does nothing, first check whether a sharing window opened behind Outlook, then try…
- Excel New Blank Workbook: Create, Save, and Set It UpTo create a new blank workbook in Excel for Microsoft 365 on Windows, press Ctrl+N, or select File…
- Excel Data Bars: How to Use Conditional FormattingIn Excel, bar conditional formatting is called Data Bars: it fills each selected cell with a horizontal bar…
- Excel TEXT Percentage: How to Format Values as PercentagesIf A1 contains a decimal percentage such as 0.25, use =TEXT(A1,"0%") to display it as 25%. The TEXT…
- When Was Excel Created? The 1985 Launch ExplainedMicrosoft Excel was created and first released in 1985, initially for Apple Macintosh computers. Its first Windows version…
- How to Remove Bullet Formatting in WordTo remove bullet formatting in Microsoft Word, select the bulleted paragraphs, then go to Home > Paragraph >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.