How to Perform Greater than and Less than in Excel (5 Methods)

Operators specify the type of calculation that you want to perform on the elements of a formula. There are four different types of calculation operators in Excel: arithmetic, comparison, text concatenation, and reference. Two of the conditional operators in Excel are: Greater than (>) and Less than (<). Today, in this tutorial I will explain to you different ways to use these two conditional operators. 


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


5 Easy Methods to Use Greater than and Less than in Excel

Let’s assume we have an Excel worksheet that contains the information of various outlets of a chain restaurant all across the United States. The Excel worksheet contains sales and expenditure amounts for each of these restaurants. We will use comparison operators i.e., greater than (>) and less than (<) to find out if an outlet earned profit or incurred loss. We will also use these greater than and less than excel operators to count and sum all the sales amounts above or below a certain amount, determine the tax rate based on the sales amount, for conditional formatting, and, apply them to text values. The image below depicts the Excel worksheet we are going to work with. 

Greater than and Less than in Excel


1. Greater than and Less than in Excel to Compare Cell Values

We can use the greater than and less than conditional operators between two cell values to compare them. Most of the time, conditional operators are used to compare numbers. The greater than operator (>) returns TRUE if the first value is greater than the second value. The less than operator (<) returns TRUE if the first value is less than the second value.

Steps:

  • For example, take a look at the formula in cell E5 below. We are comparing the sales amount in cell C5 with the expenditure amount in cell D5
  • We have written the below formula to compare these values. 
=C5>D5

Write the Formula in Cell E5

  • Upon pressing ENTER, we will get the Boolean value TRUE in cell E5. Sales amount (C5) is greater than expenditure amount (D5).  Hence, the cell is showing TRUE

Formula Showing Boolean Value TRUE

  • We will do the same for the rest of the cells in this column by dragging down the fill handle. 

Drag the Fill Handle to Apply the Formula

  • After releasing the fill handle, all the cells in the Status column will show boolean values after comparing the corresponding Sales and Expenditure values in respective rows. 

Greater than and Less than Excel

  • We can also use both the conditional operators together to compare if any two values are equal or not. Write down the below formula in E5. This formula will return TRUE if the two values are not equal. It will return FALSE if the two values are equal. 
=C5<>D5

Use Comparison Operators to Find Out If Two Values are Equal

  • As the Sales (C5) and Expenditure (D5) values are not equal, the conditional operators will return TRUE. If we drag the fill handle to autofill the rest of the cells, it will return boolean values for every cell comparing the corresponding Sales and Expenditure values in the respective rows. 

Greater than and Less than Excel

  • We will see that it returns for cells E10 and E12 as the Sales and Expenditure values in corresponding cells are equal. 

2. Greater Than and Less Than Excel Comparison Operators in Arguments of Excel Functions

The main use of comparison operators is in different Excel functions like IF, SUMIF, COUNTIF, Etc. Let’s see how we can use them with different Excel functions to find out if an outlet is gaining profit or incurring a loss, count and sum up all the sales amount above $1000.  

2.1 Comparison Operators with the IF Function

Steps:

  • First, we will find out if an outlet is earning profit or incurring a loss. We will use the IF function and Greater than (>) comparison operator for that. We will write the below formula in cell E5,
=IF(C5>D5, "Profit", "Loss")

Use Comparison Operators in IF Function

  • IF function will compare the Sales with Expenditure. If the Sales is greater than Expenditure, then it will return the value “True”. If Sales is less than Expenditure, then it will return “Loss”. Upon pressing Enter, we will get the value True for the first outlet Nashville. 

IF Function With Conditional Operators Shows Profit for Nashville Outlet

  • We will drag the fill handle downward to fill the other cells in this column with the same formula. 

IF Function in Excel Shows Profit or Loss Using Conditional Operators

2.2 Comparison Operators with the COUNTIF Function

Steps:

  • We will now use the COUNTIF function to count all the sales amounts that are greater than $1000. Write down the below formula in cell F16.
=COUNTIF(C5:C14, ">1000")

Greater than and Less than Excel

This function will count all the values in the Sales (C5:C14) column that is greater than 1000. 

  • Upon entering the formula, we get the value 7. We have 7 sales amounts that are greater than 1000. 

Comparison Operators in COUNTIF Function Counts All the Sales Values Above 1000

2.3 Comparison Operators with the SUMIF Function

Steps:

  • We can also use the conditional operators with the SUMIF function, to sum up, all the sales amounts that are greater than $1000. Write down the below formula in cell F16.
=SUMIF(C5:C14, ">1000")

SUMIF Function with Greater Than Sign Sums All the Sales Values Above 1000

This function will sum up all the values in the Sales (C5:C14) column that is greater than 1000. 

  • Upon entering the formula, we get the value 13500. The sum of all sales values above 1000 is 13500. 

SUMIF Function with Greater Than Sign Sums All the Sales Values Above 1000


Similar Readings:


3. Comparison Operators in Excel Mathematical Operations

Sometimes conditional operators can be used as a substitution of Excel formulas. Using conditional operators will reduce the formula significantly. We will substitute the IF function below with conditional operators. 

Steps:

  • We will use the below IF function to calculate the tax to be paid by each outlet based on their sales amount. 
=IF(C5>1500, C5*0.2, C5*0.1)

This IF function will determine 20% as the tax rate for the sales amounts that are greater than $1500 and multiply the tax rate with the sales amount. It will assume 10% as the tax rate for the rest of the sales amount that is less than $1500.

  • We can replace this IF formula with a formula constructed using only conditional operators.
 =(C5>1500)*(C5*0.2)+(C5<=1500)*(C5*0.1)

If a value in cell C5 is greater than 1500, then C5>1500 will be TRUE, and hence will be equal to 1. On the contrary, C5<=1500 will be FALSE and return 0. As in this example C5>1500, our formula can be interpreted like below:

1*(C5*0.2)+0*(C5*0)

Comparison Operators in Excel Mathematical Operations

  • If we drag the fill handle downward, we will get the tax amounts for the rest of the sales values. 

Comparison Operators in Excel Mathematical Operations


4. Comparison Operators in Excel Conditional Formatting

We can use comparison operators for conditional formatting. For this example, we will use conditional formatting with a greater than (>) conditional operator to find out the tax values greater than $300. 

Steps 1:

  • First, we will go to Conditional Formatting located in the Styles section under the Home ribbon. Then, we will select New Rule from the drop-down list. 

Comparison Operators in Excel Conditional Formatting

  • We will select Use a formula to determine which cells to format from the Select a Rule Type list. 
  • Then, we will enter =F5>300 as the rule. 
  • We will click on the Format button and select a color to highlight our cells. For this example, we have selected Red color. 
  • Lastly, click on the OK button. 

Greater than and Less than Excel

Step 2:

  • A new dialogue box titled Conditional Formatting Rules Manager will appear. We will only just click OK. 

Conditional Formatting Rules Manager Dialogue Box

  • Upon clicking OK, cell F5 will turn red as mentioned in the rule of conditional formatting as the value is greater than 300. We drag the fill handle to apply the conditional formatting to the rest of the cells in the Tax column. 

Comparison Operators in Excel Conditional Formatting


5. Graeter Than and Less Than Excel Comparison Operators with Text Values

We can also use conditional operators to compare the text values. For example, we can compare the outlet names for this example. When comparing text values, Microsoft Excel ignores their case and compares the values symbol by symbol, “a” being considered the lowest text value and “z” – the highest text value.

Steps:

  • We will compare the name of the first outlet (Nashville) with the rest of the outlets. Write down the formula below in cell C6 under the Status column. 
 =$B$5>B6

We have added two $ signs for cell B5.  We are comparing the name of the first outlet with the rest of the outlets. So, when using the fill handle to autofill the rest of the cells, we do not want this cell reference to be changed. So, we are using $ signs to make the cell reference absolute. 

Comparison Operators with Text Values

  • Upon entering the formula we will get the Boolean value TRUE. We will drag the fill handle to apply the formula to the rest of the cells. 

Comparison Operators with Text Values


Things to Remember

  • If we do not insert any number as the second argument for the RIGHT function, it will just extract the last character of the string.
  • If the second argument exceeds the total length or character of the string, then it will return the whole string or text. 

Conclusion

In this article, we have learned to use greater and less than Excel conditional operators. I hope from now on you would find it very easy to use greater than and less than Excel conditional operators. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!! 


Further Readings

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo