Find Greater Than in Excel: Formulas, Filters, and Rules

Formulas & functions · Updated November 20, 2026

To find values greater than a number in Excel, use the greater-than operator > in a formula, or choose a greater-than condition in a filter or conditional-formatting rule. For example, a formula such as =A2>100 returns TRUE when A2 is above 100. The steps below show how to identify, count, filter, and highlight values that exceed a threshold.

What You’ll Need

  • An Excel workbook with a column or range of numeric values to check.
  • A threshold number to compare those values with, such as 100, a date, or a quantity.
  • Optional: a spare column for a formula result, or a cell to hold a changeable threshold.

These instructions are for Excel for Microsoft 365 on Windows. The main ribbon commands are also available in Excel for Mac and Excel for the web, although their placement or dialog appearance can vary slightly.

Step-by-Step

  1. Choose what you mean by “find greater than.” Excel can answer this in several useful ways. A comparison formula tests a particular cell and returns TRUE or FALSE; a counting formula tells you how many values pass; a filter shows only matching records; and conditional formatting marks the matching cells visually. Decide whether you need to inspect results, count them, or work with only the qualifying rows. The comparison itself is strict: greater than 100 means above 100, not equal to 100.
  2. Check a single cell with the greater-than operator. Suppose the numbers are in A2:A6, with 45 in A2, 120 in A3, 100 in A4, 175 in A5, and 88 in A6. Select B2 and enter =A2>100. Press Enter. Since A2 contains 45, B2 displays FALSE. The formula asks whether the value in A2 is greater than 100; it does not change the original value.
  3. Test every row in a helper column. With the same values in A2:A6, enter =A2>100 in B2, then copy or fill the formula down through B6. Excel adjusts the reference for each row. The results are FALSE, TRUE, FALSE, TRUE, and FALSE, so A3 and A5 pass the test. To fill down, select B2 and drag the small square at the lower-right corner of its selection to B6, or copy B2 and paste into B3:B6. You can then filter column B for TRUE if you want to see the passing rows.
  4. Count how many values exceed a threshold. With the numbers still in A2:A6, enter =COUNTIF(A2:A6,">100") in an empty cell. The answer is 2: 120 and 175 are greater than 100, while 100 itself does not qualify. COUNTIF uses a criterion written in quotation marks, including the greater-than sign and number. If you need “greater than or equal to,” use the two-character operator >= in the criterion instead.
  5. Use a cell for a threshold that may change. If the threshold is in D1 and contains 100, enter =COUNTIF(A2:A6,">"&D1) in another cell. This also returns 2 for the example. The ampersand joins the greater-than text to the value in D1, so you can change D1 to a different number without rewriting the formula. For a row-by-row comparison against D1, with each value in A2:A6 and the threshold in D1, enter =A2>$D$1 in B2 and fill down. The dollar signs keep the threshold reference fixed as the formula is copied.
  6. Filter a list to show only values above a number. Click a cell inside your data range, then select Data > Filter. Open the drop-down arrow in the column containing the numbers, choose Number Filters > Greater Than, enter the threshold, and confirm with OK. For a threshold of 100, the visible rows contain numbers above 100; rows equal to 100 or below are temporarily hidden. To see all records again, clear the column filter or use Data > Clear. Keep related columns within the same data range so each row stays together when filtered.
  7. Highlight matching values without hiding rows. Select the numeric cells you want checked, such as A2:A6. Choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than. Enter 100 in the box, choose a formatting style, and select OK. Excel highlights values above 100 while leaving the rest of the sheet visible. If you select a whole column, check that the rule applies to the intended cells; an overly broad selection can make the sheet harder to read.
  8. Use an IF formula when you want labels instead of TRUE or FALSE. With each value in A2:A6, enter =IF(A2>100,"Over limit","OK") in B2 and fill down. For the sample values, B2 shows OK, B3 shows Over limit, B4 shows OK, B5 shows Over limit, and B6 shows OK. This is useful for status columns, but the words are labels—not numeric values you can directly add or average.

Common Mistakes

  • Using the wrong comparison sign: >100 excludes 100. Use >=100 if 100 should also qualify. In a formula, the symbols go together as >=; putting them in the opposite order is not the intended operator.
  • Typing a criterion incorrectly: COUNTIF needs the operator and number as a text criterion, as in ">100". When the threshold is in another cell, join the operator and cell reference with &. Do not put the entire joined expression in quotation marks.
  • Comparing numbers stored as text: Imported values may look numeric but be stored as text, so comparisons or number filters may give unexpected results. Check alignment and cell formatting, and try converting the values to numbers before relying on the results.
  • Including a heading in the tested range: If a column heading is part of the range, it can interfere with formulas, filters, and conditional formatting. Start at the first row of data, not the header.
  • Expecting Find to apply a numeric condition: Home > Find & Select > Find searches for matching content; it is not a “greater than” filter. Use Number Filters or a formula to compare values against a threshold.

Tips

  • Use a helper column when you want a clear TRUE/FALSE answer for every row. It makes the test easy to check and lets you filter for TRUE without hiding data based on a complicated formula.
  • Keep a changeable threshold in its own cell, label it clearly, and refer to it in your formulas. This reduces edits and makes it obvious what value the comparison uses.
  • For dates, Excel compares actual date values in the same way it compares numbers. Make sure the cells contain real Excel dates, not date-like text, and set the threshold cell to a date.
  • For a rule that should update as rows are added, consider turning the data range into an Excel table with Insert > Table. Check the conditional-formatting range and formula references after adding data.
  • Remember that blank cells are not meaningful numeric values for this task. If blanks appear in a range, verify the output rather than assuming every row represents a number.

Troubleshooting

The formula shows an error: Check that the formula begins with =, that the cell references exist, and that quotation marks and parentheses are paired. In some regional settings Excel expects semicolons instead of commas between function arguments; if a formula with commas is rejected, use the separator configured for your Excel installation. The formulas here use commas as requested.

A number that looks greater is not counted or highlighted: Select the source cells and inspect the values. Imported data can contain leading apostrophes, spaces, or text formatting. You can use Excel’s available convert-to-number prompt, or clean the data and convert it before repeating the comparison. Also check that the displayed value is not rounded: a cell showing 100 may contain 100.4, or a cell showing 101 may contain 100.6, depending on its number format.

The filter does not show the rows you expect: Confirm that the filter is on the correct column and that the column contains numbers rather than text. Reopen the drop-down and check the entered threshold. Clear other active filters too, because a filter on a different column may be hiding otherwise qualifying rows.

Conditional formatting highlights the wrong cells: Select Home > Conditional Formatting > Manage Rules and review the rule’s threshold and “Applies to” range. If the selection began on a different row than the rule’s formula assumes, the formatting can appear shifted. Edit the range or recreate the rule with the intended cells selected first.

Frequently Asked Questions

What symbol means greater than in Excel?
The symbol is >. In a formula, =A2>100 checks whether A2 is strictly above 100 and returns TRUE or FALSE.

How do I find values greater than or equal to a number?
Use >=. For example, with a value in A2 and a threshold of 100, =A2>=100 returns TRUE for 100 and any larger number. In a COUNTIF criterion, use ">=100".

Can I count values greater than a number in Excel?
Yes. If the values are in A2:A6, =COUNTIF(A2:A6,">100") counts those above 100. It returns 2 for the example values 45, 120, 100, 175, and 88.

How can I find greater-than values in one column and keep the full row?
Apply Data > Filter to the complete data range, then choose Number Filters > Greater Than from the relevant column’s drop-down. Filtering the range keeps the other columns in each matching row visible.

Why does Excel show TRUE or FALSE instead of a number?
A direct comparison such as =A2>100 is a logical test, so TRUE or FALSE is the expected result. Use COUNTIF to return a count, or IF to display a custom text label.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.