Excel formulas for greater than and less than comparisons help you quickly filter data, flag exceptions, and drive decisions. Understanding how to use > and
These comparison operations work inside IF, COUNTIF, SUMIF, and conditional formatting rules, so they are foundational for both analysis and reporting.
| Operator | Meaning | Example | Use Case |
|---|---|---|---|
| > | Greater than | =A2>100 | Flag sales above a target |
| Less than | =B2 | Identify underperforming regions | |
| >= | Greater than or equal to | =C2>=30 | Include threshold in the check |
| Less than or equal to | =D2 | Cap values at a maximum | |
| Not equal to | =E2<>"Pending" | Exclude specific text or states |
Using Greater Than in Logical Tests and Conditional Formatting
The greater than operator (>) returns TRUE when the left value is larger than the right value, and FALSE otherwise. You commonly use it inside an IF function to assign results like "Yes" or "No" based on a threshold, or within conditional formatting to highlight cells that exceed a benchmark.
For example, =IF(A2>1000, "High", "Low") displays High when sales in A2 are above 1000. In conditional formatting, a rule using =A2>1000 shades cells red so that key outliers stand out instantly on dashboards.
When you combine greater than with AND or OR, you can build multi-condition tests such as =IF(AND(A2>100, B2
Using Less Than for Threshold Alerts and Data Validation
The less than operator (
You can write =IF(B20) to restrict entries between 0 and 100.
Less than is also useful in sorting and filtering, where you may want to show only rows with dates earlier than a chosen cutoff or values smaller than a performance baseline.
Combining Greater Than and Less Than in a Single Condition
To check that a value falls between two boundaries, you nest greater than and less than together using AND, so that both conditions must be TRUE. A formula like =IF(AND(D2>10, D2
This pattern is common in scoring models, where you classify results as Optimal when they lie within a target band, and flag anything outside that band for review.
You can extend the logic with OR to capture multiple valid ranges, such as =IF(OR(AND(E2>5, E220, E2
Key Takeaways for Practical Use of Greater Than and Less Than
- Use > and
- Combine them with AND or OR to build multi-condition logic for flexible decision making.
- Apply these operators in conditional formatting to visually highlight critical outliers.
- Validate data entries and set alerts so teams act when values cross safe boundaries.
- Remember to clean data and verify formats to avoid misleading TRUE or FALSE results.
FAQ
Reader questions
How do I count cells greater than a specific number in a range?
Use COUNTIF with the syntax =COUNTIF(range,">100") to tally how many entries exceed your chosen threshold without needing an extra column.
How can I sum values that are less than a certain amount?
Apply SUMIF as =SUMIF(range,"<50") to add up only the cells that fall below the specified limit, which is helpful for analyzing small transactions or low-performing segments.
Can I use greater than and less than with text or dates
Yes, Excel compares text alphabetically and dates by their serial numbers, so =A2>"2023-06-01" and =B2>"Apple" work as long as the cells contain comparable data types.
Why does my greater than formula return FALSE unexpectedly
Check for extra spaces, inconsistent number formats, or hidden characters, and confirm that the cells involved contain numeric values rather than text that looks numeric.