Conditional Formatting Greater Than: Excel Rules Explained

In Excel for Microsoft 365 on Windows, use Home > Conditional Formatting > Highlight Cells Rules > Greater Than, enter the comparison value, choose a format, and select OK. The rule highlights cells whose values are strictly greater than the threshold; a value equal to the threshold is not highlighted.

What “greater than” means in Excel

Conditional formatting changes a cell’s appearance when its contents meet a condition. A Greater Than rule compares each applicable cell with a threshold and applies the chosen format only when the cell’s value is above that threshold. It changes the display, not the underlying value, so the cell can still be used in calculations, copied, or edited normally.

For example, if a sales column contains 85, 100, and 125 and the threshold is 100, only 125 meets a Greater Than rule. The value 100 is equal, not greater. This distinction matters when you mean “at least 100”: for that condition, use a formula-based rule with a greater-than-or-equal comparison, rather than the built-in Greater Than option.

Excel compares the stored value, not necessarily the text shown on screen. A cell displaying $125.00 may contain the numeric value 125, and a percentage displayed as 15% is stored as 0.15. A date is stored as a serial number, so a date threshold can also be used. Formatting the number differently does not change the comparison.

Create a Greater Than rule

On Windows, select the cells to check, then open Home > Conditional Formatting > Highlight Cells Rules > Greater Than. Enter a number in the dialog’s comparison box, choose one of the suggested formats or select Custom Format, and click OK. The selected range receives the rule. If you selected the wrong cells, close the dialog and select the intended range before creating the rule.

For instance, to highlight scores above 80, select the score cells, enter 80, choose a fill or font style, and confirm. The rule will update when values in the selected cells change. You do not have to apply the formatting one cell at a time.

Select only the data cells if the header should stay unformatted.
Choose a color with enough contrast to make highlighted values easy to read.
Check a value equal to the threshold to confirm that the rule behaves as intended.

On Excel for Mac, the Conditional Formatting controls are also on the Home tab, and the Greater Than command is under Conditional Formatting and Highlight Cells Rules. The dialog may look different from the Windows version, but the comparison is the same. Excel for the web also has conditional formatting controls, though some advanced rule-management options may be arranged differently from desktop Excel.

Choose a fixed threshold or a changing one

A fixed number is the simplest threshold: it is easy to set up and suits a rule that should always use the same cutoff. But when the cutoff needs to change—perhaps a manager updates a target each month—put the threshold in a worksheet cell and create a formula-based rule that refers to it. Changing that cell then changes the condition without rebuilding the rule.

For example, keep a sales target in E1 and the sales values in B2:B10. Select B2:B10, choose Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, and enter the formula below. Set the desired format and confirm. With E1 containing 100, a value of 125 in B2 is highlighted, while 100 is not.

Assuming sales are in B2:B10 and the threshold is in E1, apply the rule to B2:B10 with this formula: =B2>$E$1.

The reference B2 is relative, so Excel checks B3 against E1 in the next row, then B4 against E1, and so on. The dollar signs in $E$1 lock the threshold reference so it does not move as the rule is evaluated down the range. If you instead refer to E1 without dollar signs, the reference may shift and compare each row with a different cell.

Use formulas for exact conditions

The built-in Greater Than rule is convenient, but a formula gives more control. It can express “greater than or equal to,” exclude blanks, or highlight an entire row based on a value in one column. Formula rules are created through Home > Conditional Formatting > New Rule, using the option to determine which cells to format with a formula. Enter a formula that evaluates to TRUE or FALSE, then choose a format.

To highlight values at or above 100, use a greater-than-or-equal comparison. Assuming values are in B2:B10 and the formula rule applies to B2:B10, enter: =B2>=100. A cell containing 100 qualifies under this formula, unlike the built-in Greater Than rule.

Blank cells can be an issue when the cutoff is negative: Excel may treat a blank as zero in a numeric comparison, and zero is greater than a negative number. To require a nonblank cell as well, use a condition that checks both facts. Assuming values are in B2:B10 and the rule applies to that range, enter: =AND(B2<>"",B2>$E$1). This highlights a cell only when it is not blank and its value exceeds the threshold in E1.

To color a whole row according to one column, select the full table rows and make the column reference absolute while leaving the row number relative. Assuming the table occupies A2:D20, the values to test are in column C, and the target is in F1, apply the formula rule to A2:D20 with: =$C2>$F$1. The dollar sign before C keeps the test in column C, while the row number adjusts for each row.

Dates, percentages, and other values

Greater Than works with numeric values, including dates and percentages, as long as Excel recognizes them as numbers. To flag dates after a deadline, use a date threshold; to flag percentages above 10%, use 10% rather than the number 10. The display format can make the underlying value less obvious, so check the formula bar or temporarily use a number format if a comparison seems wrong.

Text is not a substitute for numeric data. If a number was imported or entered with an apostrophe, Excel may store it as text, and a numeric Greater Than rule may not treat it as expected. Convert the data to numbers before relying on numerical comparisons. Also watch for units: comparing a column of dollar amounts with a threshold entered in a different currency or scale produces a technically valid comparison but a misleading result.

For dates, confirm that both the cells and the threshold are real dates rather than text that merely looks like a date. Regional date formats can affect how typed dates are interpreted. If the rule seems inconsistent, test a cell with a simple arithmetic comparison or inspect the value in the formula bar.

How the rule applies across a range

A rule has an “Applies to” range, which determines which cells can receive the formatting. Selecting a range before creating the rule usually sets this scope automatically. To inspect or change it, open Home > Conditional Formatting > Manage Rules. In the rules manager, choose the relevant worksheet or current selection in the display control, select the rule, and review its range.

Be careful when extending a table. A rule applied to B2:B20 does not necessarily cover new data entered in B21. Expand the Applies to range or create the rule for the expected data area. In a structured Excel table, formatting behavior can depend on how the range and table are configured, so check the rule manager after adding rows if new entries are not highlighted.

Formula references depend on the top-left cell of the Applies to range. If a formula is written for B2 but the range begins at B3, the rule may test the wrong row. When copying a rule or changing its range, review both the formula and the range instead of assuming Excel adjusted them the way you intended.

Rule order and overlapping formats

More than one conditional formatting rule can apply to a cell. For example, one rule might turn values above 100 green while another turns values above 150 bold. The Conditional Formatting Rules Manager lets you review rules, change their order, edit them, or delete them. When multiple rules set the same formatting property, their order can affect the result; the Stop If True option can also prevent lower-priority rules from being evaluated when a higher rule is true.

If a cell looks different from the format you expected, do not immediately recreate the rule. Open Home > Conditional Formatting > Manage Rules and inspect every rule that applies to that cell. A higher-priority rule, a different Applies to range, or a manually applied cell format may explain what you see. Rule management is especially useful in workbooks that have been edited repeatedly by several people.

Common problems and how to fix them

Nothing is highlighted: Confirm that the values really exceed the threshold, that the Applies to range includes them, and that numeric entries are not stored as text.
Equal values are highlighted or missed: The built-in Greater Than rule excludes equality. Use a formula with >= if the threshold itself should qualify.
The wrong rows are highlighted: Check the formula’s relative and absolute references and verify that its starting row matches the top-left cell of the Applies to range.
Blank cells are formatted: If the threshold is below zero, add a nonblank test such as the AND formula shown above.
The color does not appear as expected: Inspect overlapping rules and their order, and consider whether the chosen font or fill is hard to see against the worksheet.

Another common misconception is that conditional formatting changes a value or makes a calculation treat it as larger. It does neither: it applies visual formatting based on the condition. If a chart, formula, or sort needs to respond to the same criterion, configure that calculation or action separately.

When to use another conditional formatting rule

Greater Than is best when one upper cutoff answers the question. For a lower cutoff, Excel offers a Less Than rule; for a value that must fall between two limits, use Home > Conditional Formatting > Highlight Cells Rules > Between. A color scale can show relative position across a group, but it answers a different question: it emphasizes how values compare with one another rather than whether each one passes a fixed cutoff.

Use a formula rule when you need a changing cell reference, a whole-row highlight, a nonblank check, or an inclusive comparison. Use the built-in rule when you want a straightforward fixed threshold and a fast setup. For large or frequently changing workbooks, consistent range design and a clearly labeled threshold cell make rules easier to understand and maintain.

Frequently Asked Questions

Does Greater Than include the value entered as the threshold?
No. It highlights values strictly above the threshold. To include an equal value, create a formula rule using the greater-than-or-equal operator.

Can I compare each cell with a value in another cell?
Yes. Create a formula-based rule and use a relative reference for the cells being tested and an absolute reference for the threshold cell. For example, a rule applied to B2:B10 can compare each B cell with $E$1.

Can conditional formatting highlight an entire row?
Yes. Select the rows or table area to format, then use a formula rule that tests the relevant column. Lock the column in the formula, but leave the row number relative so Excel evaluates the correct record on each row.

Why are values that look like numbers not highlighted?
They may be stored as text, or the rule may cover a different range than expected. Convert text numbers to numeric values and check the rule’s Applies to range and threshold.

Does the rule work with dates and percentages?
Yes, if the data and threshold are stored as actual dates or numeric percentages. A percentage displayed as 15% is stored as 0.15, so enter the cutoff using the same scale and units as the data.

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.