Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)

To compare any particular product price, availability, etc. in different times or seasons the greater than operator helps us. In this article, I’m going to explain how to highlight cell if value greater than another cell in Excel.

The image depicted below is an overview image and represents highlight cell when the cell value is greater than another cell in Excel.

Highlight Cell If Value Greater Than Another Cell in Excel: 6 Ways

Let’s assume a dataset, namely “Fruit Price in New Mexico State”. This dataset contains 4 columns that represent fruit prices in different seasons. These are Product Name, Spring Price, Summer Price, and Winter Price. Here, I will show you in which season the price is greater than from other seasons. However, you can use any dataset suitable for you.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

1. Utilizing Greater Than Feature to Highlight Cell If Value Greater Than Another Cell

The simplest way to Highlight a cell if the value is greater than another cell is by using the Greater Than command ribbon.

📌 Steps:

First, select the cell or cell range to Highlight a value Greater Than another cell. Here, I selected the cell range D5:D10.

Then, Go to the Home tab >> Conditional Formatting >> Highlight Cells Rules >> select Greater Than.

A dialog box will pop up.

In Format cells that are GREATER THAN, select the other cell with which you want to compare. Let’s select the E5 cell.
Then select any format to colour where values are Greater Than. Here, I selected Light Red Fill with Dark Red Text.

Finally, click OK. Thus, you will see all the values Greater Than from the selected cell will be highlighted with the selected format.

Here, the drawback is the selected value remains constant, it compares all selected range values with a constant value only.

2. Using Greater Than (>) Operator to Highlight Cell If Value Greater Than Another Cell

You can use the Greater Than (>) operator to Highlight the cell if the value is greater than another cell. To demonstrate the procedure, I’ll use the Spring Price and Summer Price columns.

📌 Steps:

To begin with, select the cell or cell range to Highlight a value Greater Than another cell. Here, i selected the cell range C5:C10.

Now, open the Home tab >> Conditional Formatting >> select New Rule.

Subsequently, a dialog box will pop up.

From Select a Rule Type, select Use a formula to determine which cells to format.
In Format values where this formula is true type the following formula.

=C5>D5

Now, click on Format to select the format of your choice to highlight the cell.

Again, a dialog box will pop up.

Select any color to Highlight your cell. I selected the Gold Accent 4, Lighter 60% color to fill. Press OK afterwards.

Then, click OK.

Therefore, It will Highlight the cell values of the Spring Price column where it is Greater Than the Summer Price column.

3. Applying Greater Than Equal (>=) Operator to Highlight Cell If Value Greater Than Another Cell

You also can use the Greater Than (>=) operator to Highlight the cell if the value is greater than equal to another cell. To demonstrate the procedure, I’ll use the Spring Price and Winter Price columns.

📌 Steps:

To begin with, select the cell or cell range to Highlight a value Greater Than Equal to another cell. I selected the cell range C5:C10.

Now, open the Home tab >> from Conditional Formatting >> select New Rule

Subsequently, a dialog box will pop up.

From Select a Rule Type, select Use a formula to determine which cells to format.
In Format values where this formula is true type the following formula.

=C5>=E5

From Format, select the format of your choice to Highlight the cell where the value is Greater Than Equal.

To choose the Format follow the explained steps from Method-2.

In the end, click OK. Therefore, It will Highlight the cell values of the Spring Price column where it is Greater Than Equal to the Winter Price column.

4. Employing Greater Than Equal (>=) with Blank Cell to Highlight Cell If Value Greater Than Another Cell

You can use the Greater Than (>) operator with the AND function to Highlight a cell value greater than another cell while skipping Blank Cell. Here, in my existing dataset, I’ve taken some Blank Cells in the Winter Price column.

📌 Steps:

First, select the cell or cell range to Highlight a value Greater Than another cell except for Blank Cell. I selected the cell range D5:D10

Now, open the Home tab >> from Conditional Formatting >> select New Rule

Subsequently, a dialog box will pop up.

From Select a Rule Type, select Use a formula to determine which cells to format.
In Format values where this formula is true type the following formula.

=AND(D5>E5, $E5<>"")

Formula Breakdown

=AND(D5>E5, $E5<>””)→ checks whether the cell D5 is greater than E5 then it will also check E5 is not equal blank. If both conditions are fulfilled, then it will Highlight the cell.

From Format, select the format of your choice to Highlight the cell.

To choose the Format follow the explained steps from Method-2.

Finally, click OK. Hence, It will Highlight the cell values of the Summer Price column where it is Greater Than the Winter Price column, but you will see that the values that would be compared to the Blank Cells values are not Highlighted as we skipped the Blank Cells using $E4<>”” within the formula.

5. Incorporating IF Function to Highlight Cell If Value Greater Than Another Cell

You can use the IF function to highlight cell if the value is greater than another cell. To demonstrate the procedure, I’ll use the Spring Price and Winter Price columns.

📌 Steps:

To begin with, select the cell or cell range to Highlight a value Greater Than another cell. I selected the cell range C5:C10

Now, open the Home tab >> from Conditional Formatting >> select New Rule.

Subsequently, a dialog box will pop up.

From Select a Rule Type, select Use a formula to determine which cells to format.
In Format values where this formula is true type the following formula.

=IF(C5>E5,C5,"")

Formula Breakdown

=IF(C5>E5,C5,””) →check that cell C5 is greater than E5. If the selected cell is greater than E5 then it will highlight the C5 cell otherwise won’t highlight.

From Format, select the format of your choice to Highlight the cell.

To choose the Format follow the explained steps from Method-2.

In the end, click OK. Therefore, It will Highlight the cell values of the Spring Price column where it is Greater Than the Winter Price column.

6. Utilizing Average Function to Highlight Cell If Value Greater Than Another Cell

You can compare the average of the values while Highlighting a cell. To ease the task you can use the Average function.

To demonstrate the procedure, I’ll calculate the Average of the Summer Price, and the Winter Price column and will check whether the Spring Price column is Greater Than the calculated Average or not.

📌 Steps:

To begin with, select the cell or cell range to Highlight a value Greater Than another cell. I selected the cell range C5:C10.

Now, open the Home tab >> from Conditional Formatting >> select New Rule

Subsequently, a dialog box will pop up.

From Select a Rule Type, select Use a formula to determine which cells to format.
In Format values where this formula is true type the following formula.

=C4>AVERAGE(D4,E4)

Formula Breakdown

=C4>AVERAGE(D4,E4)→ calculates the average of the value from D5 and E5, and then we are checking whether the C5 value is greater than the derived value or not.

From Format, select the format of your choice to Highlight the cell.

To choose the Format follow the explained steps from Method-2.

Finally, click OK. As a result, It will Highlight the cell values of the Spring Price column where it is Greater Than the Average of the Spring Price and Winter Price columns.

Practice Section

I’ve provided a practice sheet to practice the explained methods.

Download to Practice

Highlighting Cell If Value Greater Than Another Cell.xlsx

Conclusion

In this article, I’ve explained 6 easy and quick ways of Excel to highlight the cell if value is greater than another cell in Excel. These different ways will help you to highlight a cell compared with another cell if the value is greater than. Last but not least if you have any kind of suggestions, ideas, and feedback please feel free to comment down below.

Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)

Highlight Cell If Value Greater Than Another Cell in Excel: 6 Ways

1. Utilizing Greater Than Feature to Highlight Cell If Value Greater Than Another Cell

2. Using Greater Than (>) Operator to Highlight Cell If Value Greater Than Another Cell

3. Applying Greater Than Equal (>=) Operator to Highlight Cell If Value Greater Than Another Cell

4. Employing Greater Than Equal (>=) with Blank Cell to Highlight Cell If Value Greater Than Another Cell

5. Incorporating IF Function to Highlight Cell If Value Greater Than Another Cell

6. Utilizing Average Function to Highlight Cell If Value Greater Than Another Cell

Conclusion

Further Readings

Leave a reply Cancel reply