Use Excel formulas for less than and greater than to filter data, flag exceptions, and drive decision making. These comparisons power everyday analysis, from budget alerts to performance thresholds.
Mastering logical tests with operators such as < and > helps you build dynamic, responsive spreadsheets. The following sections break down syntax, real patterns, and troubleshooting tips you can apply right away.
| Operator | Meaning | Example | Result if C2 is 15 |
|---|---|---|---|
| < | Less than | C2 < 10 | FALSE |
| > | Greater than | C2 > 10 | TRUE |
| <= | Less than or equal to | C2 <= 15 | TRUE |
| >= | Greater than or equal to | C2 >= 10 | TRUE |
Less than and greater than in basic calculations
Simple logical tests
The most common pattern is a comparison such as =A1>100. This returns TRUE when the value in A1 is greater than 100, and FALSE otherwise. Pair it with IF to choose different results based on the comparison.
Numeric boundaries and thresholds
Use greater than and less than to define bands, such as performance tiers or risk levels. For example, =IF(B2>1000, "High", "Normal") labels values efficiently. These tests update instantly when source data changes.
Combining less than and greater than with IF
Basic IF with one condition
Write =IF(C2>50, "Pass", "Fail") to evaluate a single threshold. The formula checks whether C2 is greater than 50 and returns the corresponding result immediately.
Chained conditions for ranges
Combine multiple checks using AND or nested IFs. For a range between 20 and 80, use =IF(AND(D2>20, D2<80), "In Range", "Out of Range"). This pattern is useful for validation rules and compliance checks.
Using less than and greater than in counting and summing
COUNTIF for condition-based counts
Count how many values meet a criterion with =COUNTIF(E2:E20, ">30"). You can adapt this for different operators to track how many entries exceed a target or fall below a limit.
SUMIF for condition-based sums
Add only the cells that satisfy a condition using =SUMIF(F2:F20, ">=100"). This approach is ideal for aggregating sales, expenses, or measurements that cross specific thresholds.
Refining your approach with best practices
- Use structured references with Excel Tables so formulas adjust automatically when new rows are added.
- Combine comparisons with AND and OR to express multiple conditions clearly and concisely.
- Lock ranges with absolute references in COUNTIF and SUMIF when copying formulas across rows or columns.
- Validate threshold logic by testing boundary values such as exactly equal to, just above, and just below your limits.
FAQ
Reader questions
How do I highlight cells greater than a specific number?
Use Conditional Formatting with a rule like =A1>100 and choose a format. Excel will apply the style automatically to any cell in the selected range that meets the condition.
Can I use less than and greater than with text values?
Yes, Excel compares text alphabetically. The formula =A1>"mike" checks whether a text string comes after "mike" in alphabetical order, based on the active sorting rules.
What happens when I compare a number to an empty cell?
An empty cell is treated as zero in numeric comparisons. For example, if B1 is blank, =B1>0 returns TRUE, because Excel evaluates the blank as 0.
How do I avoid errors when comparing numbers to text?
Wrap risky inputs in NUMBER or use error handling. For example, =IF(ISNUMBER(D2), D2>100, "Invalid") prevents #VALUE! errors when text appears in a number-only range.