Excel Conditional Formatting If a Cell Is Greater Than Another One

Conditional Formatting in Microsoft Excel is a quick way to visualize the data. To compare any data we use conditional formatting so that the data may be more understandable for a user. While working in Excel sometimes we need the comparison between two or more values. In this article, we will demonstrate different approaches to using conditional formatting for greater than another cell.

Download Practice Workbook

You can download the workbook and practice with them.

9 Different Ways to Use Conditional Formatting If a Cell Is Greater Than Another One in Excel

Conditional formatting makes it simple to highlight specific cells and also makes them easy to recognize and this fulfills a set of criteria. Based on a condition, conditional formatting alters the layout of a cell range.

To use conditional formatting if the cell is greater than another cell, we are going to use the following dataset. The dataset contains some Products in column B, and their Brands in column C, also sales in January, February, and March respectively in columns D, E, F.

1. Use Conditional Formatting Command to Format If a Cell Is Greater Than Another

The simplest and easiest way to format as a condition that is greater than, using the conditional formatting command from the ribbon in excel spreadsheets. We just need to follow some quick steps to use the conditional formatting command to format as greater than. Let’s see the steps down.

STEPS:

• Firstly, select the whole data cells where the values that we want to compare.
• Secondly, go to the Home tab from the ribbon.
• Thirdly, click on the Conditional Formatting drop-down menu under the Styles group.
• Further, select the Highlight Cells Rules, from there click on Greater Than.

• This will open the Greater Than dialog.
• Furthermore, select the cell which you want to compare by the condition greater than. So, we select cell F5 and the value of this cell is 100.

• If you want to change the color of the highlighted cells, click on the drop-down menu shown in the picture below and select the color of the format as your preference. So, we select the Green Fill with Dark Green Text.

• Finally, you will be able to see the color of the cells is now changed.
• Then, click the Ok button.

• By using conditional formatting, it will only highlight the cells whose cells value are greater than 100.

Read More: How to Use Conditional Formatting in Excel [Ultimate Guide]

2. Apply Greater Than (‘>’) Operator with Conditional Formatting in Excel

Using the greater than (‘>’) for comparison of two cells returns true if and only if the value on the left is greater than the value on the right, it will return false otherwise. Let’s demonstrate the procedure for using the greater than the operator in conditional formatting in excel.

STEPS:

• First, select the cell where you want to use the conditional formatting. So, we select cell range D5:D12.
• Second, go to the Home tab on the ribbon.
• Third, select New Rule from the Conditional Formatting drop-down menu bar, under the Styles group on the ribbon.

• This will open the New Formatting Rule dialog box.
• Now, select Use a formula to determine which cells to format from the Select a Rule Type selection box.
• Next, type the formula on the Edit the Rule Description.

• Then, click on Format to select the preferable format.

• Another pop-up window will appear, which is the Format Cells dialog box.
• Now, from the Fill menu, pick a color to highlight the cells that you want to format. So, we pick a Light Teal color.
• Then, click OK.

• Clicking ok will take you to the New Formatting Rule dialog box again.
• Further, click OK on this dialog box.

• Finally, you can see that the cells you want to format are now formatted and highlighted by the color that you picked.

• This will highlight the cell range D5:D12 which is greater than the cell range E5:E12. If you change a value from any of those cells, it will automatically change the formatting.

Read More: Excel Conditional Formatting Formula

3. Insert Greater Than Or Equal (‘>=’) Operator in Conditional Formatting

By using the greater than or equal (‘>=’) operator in conditional formatting it will be true if the left argument is greater than or equal to the right operand, the greater than or equal else it will be false. Let’s see the steps to use the greater than or equal conditional operator in excel to format the cells.

STEPS:

• In the beginning, select the range of cells that you want to use the conditional formatting. So, we select cell range E5:E12.
• After that, go to the Home tab from the ribbon.
• Next, select the New Rule option from the Conditional Formatting drop-down menu, which is categorized under the Styles group.

• The New Formatting Rule dialog box appears.
• Next, from the Select a Rule Type selection box, choose Use a formula to determine which cells to format.
• Then, on the Edit the Rule Description, type the formula.

• After that, select the preferred format by clicking on the Format button.

• A pop-up window will open up which is the Format Cells dialog box.
• Now, pick a color your choice from the Fill menu. So, we pick the Light Blue color.
• And, click the OK button to continue.

• Then, again the New Formatting Rule dialog box will pop up and you will be able to see the color on the preview section of the dialog box. Then, click Ok to close the dialog box.

• Finally, all the cells are formatted correctly.

• This will highlight only the cell range from E5:E12 which cells are greater than or equal to the cell range D5:D12.

Read More: How to Do Conditional Formatting with Multiple Criteria (11 Ways)

4. Excel AND Function on Conditional Formatting When a Cell Is Greater

The AND function is categorized under the logical function in excel. It evaluates various conditions, if the criteria fulfill the condition then it will return true, otherwise false. We can use the function for conditional formatting which fulfills the condition. To use this function, we need to follow the procedure below.

STEPS:

• In the first place, choose the cell range that will be formatted after using the formula. So, we choose the cell range F5:F12.
• In the second place, like the previous methods, go to the Home tab > click on Conditional Formatting > select New Rule.

• Similar to earlier methods, a pop-up dialog box will appear which is the New Formatting Rule.
• Now, choose Use a formula to determine which cells to format from Select a Rule Type.
• Further, write the formula which will format the cells on the cell range F5:F12 whose values are greater than the cell range D5:D12 and also greater than the cell range E5:E12.

• After typing the formula click on Format to format the cells.

• This will again appear in the Format Cells dialog box.
• Now, select a color to highlight the formatted cells where we use the condition. So, we select Orange and click OK.

• By clicking OK, you will again go to the New Formatting Rule window.
• Now, click OK on that dialog box.

• In the end, the cells are now formatted.

• Only the cells are formatted which fulfill the conditional criteria.

Read More: Conditional Formatting Based On Another Cell in Excel (6 Methods)

5. OR Function on Conditional Formatting If Value Is Greater Than Another Cell

The OR function in excel is true if any arguments apprise true and are false if all of the arguments are apprise false. So, if only one of the arguments is true this function will return true. We can use the OR function on conditional formatting if values fulfill the condition that is greater than. To demonstrate the procedure, let’s follow the steps down.

STEPS:

• By the same token as earlier methods, first, select the cell range F5:F12.
• Next, go to the Home tab. Then, select the New Rule from the Conditional Formatting drop-down menu.

• This will take you to the New Formatting Rule dialog box.
• From the dialog, select Use a formula to determine which cells to format on the Select a Rule Type selection box.
• Then, put the formula in the Edit the Rule Description box.

• Now, click on Format to choose your chosen format. Then, the Format Cells dialog box will appear in a pop-up window. You will have to pick a color from the Fill menu as your preferred option. After that, click OK.
• Now, we will be able to come to the New Formatting Rule dialog. And you will be able to see the picked color in the Preview section then simply click on the OK button.

• Finally, the cell ranges are now formatted as you want them to format.

• This will format and highlight all the cells that fulfill only one condition.

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

Similar Readings

• Excel Conditional Formatting Text Color (3 Easy Ways)
• Apply Conditional Formatting to Each Row Individually: 3 Tips
• Change Font Color Based on Value of Another Cell in Excel (2 Methods)
• Conditional Formatting on Text that Contains Multiple Words in Excel
• Excel Conditional Formatting Based on Date Range

6. Conditional Formatting with IF Function When a Cell Is Greater Than Another One

The IF function is among Excel logical functions. It will allow logical comparability between a value and a user’s expectation. We can use this function for conditional formatting when a cell is greater than another cell. To use this function we need to follow the bellowed steps.

STEPS:

• As before, select the range of cells where we use conditional formatting. So, we select cell range D5:D12.
• Next, go to the Home tab > click Conditional Formatting > select New Rule.

• The dialog box for creating a New Formatting Rule appears.
• Choose Use a formula to determine which cells to format from the Select a Rule Type drop-down menu.
• After that, type the formula into the Edit the Rule Description box.

• Then, by clicking the Format button, select your preferred format, the Format Cells dialog box appears as a pop-up window. Then, from the Fill menu, select a color and click on OK.
• After clicking OK from the Format Cells dialog box, you will back again to the New Formatting Rule dialog box. Now, click OK.

• And at last, you can see the cells are now formatted as per the condition.

• This will format the cells as the conditional formatting which is greater than another cell.

Read More: Excel Conditional Formatting Formula with IF

7. AVERAGE Function with Conditional Formatting When Cell Is Greater Than Another

The AVERAGE function is calculated by adding a set of integers and then dividing by the total number of those values. We can use the function with conditional formatting when a cell is greater than another cell. For this, we have to follow some steps below.

STEPS:

• First, begin by selecting the range of cells for which you want to utilize conditional formatting. As a result, we choose the cell range D5:D12.
• Second, from the ribbon go to the Home tab.
• Then, from the Conditional Formatting drop-down menu, select New Rule from the Styles category.

• This will open up the New Formatting Rule dialog box.
• Now, select Use a formula to determine which cells to format from the Select a Rule Type selection box.
• Then, type the formula into the Edit the Rule Description box. The formula we used here will average the two cells E5 and F5, then, compare with cell D5, if it is greater than the average of those that are not.

• After that, click on the Format option. Then, choose the format color as your preference from the Fill menu on the Format Cells dialog box. You can see the color in the Preview section.
• Finally, clicking on the Ok button will format the cells.

• You can see now that the cells are now formatted using the condition.

• This method also helps to format the cells with conditional formatting.

Read More: Excel Conditional Formatting Based on Multiple Values of Another Cell

8. Conditional Formatting Using Criteria If Value Is Greater Than Another Cell in Excel

We can use a criterion if a cell value is greater than another cell to the conditional formatting. The criteria are the sales of each month are greater than the average of those three months.  For this, we need to follow the procedure below.

STEPS:

• First, select the cell range D5:D12.
• Second, from the ribbon, navigate to the Home tab.
• Then, from the Conditional Formatting drop-down menu, under the Styles group, select the New Rule option.

• A pop-up window will appear which is for adding a New Formatting Rule.
• Now, from the Select a Rule Type, select Use a formula to determine which cells to format.
• Then, write the formula which uses the criteria for the conditional formatting.

• After that, go to the Format and the procedure for selecting the format as your preference shown in the above methods of the article.
• Finally, click OK.

• Now, you can see all the cells are formatted correctly.

• The format will use the specific criteria and format the cell using this condition.

Read More: How to Use Conditional Formatting in Excel Based on Dates

9. Compare a Cell Greater Than Another One Except for the Empty Cells

To format the cell’s value greater than another cell while skipping the empty cells, we can use the greater than (‘>’) operator in conjunction with the AND function. Let’s follow the procedure for comparing a cell greater than another except for the empty cells with conditional formatting.

STEPS:

• To begin with, choose the range of cells that we want to format. So, we choose cell range E5:E12.
• Then, from the ribbon, select the Home option.
• After that, from the Conditional Formatting drop-down menu, select the New Rule option from the Styles group.

• The dialog box for creating a New Formatting Rule pop-up.
• Now, choose Use a formula to determine which cells to format from the Select a Rule Type selection box.
• Then, write the formula into the Edit the Rule Description box.

• After that, go to the Format option and pick a color from the  Fill menu in the Format Cells dialog box.
• Next, the color format will show up in the Preview menu then, click Ok.

• Finally, you can see the cells are now formatted.

• As a result, the formula will highlight the column cell E which is Sales in Feb, values that are greater than the column cell F which is Sales in Mar, but the values that would be compared to the Blank Cells values will not be formatted because we skipped the Blank Cells using $F5<>”” within the formula.

Read More: How to Compare Two Columns in Excel For Finding Differences

Fix If Conditional Formatting Is Not Working

If the conditional formatting isn’t operating as planned. It is not due to some Excel conditional formatting error, but rather to a little oversight. We can solve the conditional formatting problems by rechecking the followings:

• Correctly using absolute and relative cell addresses.
• When copying the rule, make sure the cell references are correct.
• Check the range that has been applied.
• Fill in the top-left cell’s formula.
• Examine the formula that you put on the conditional formatting.

Conclusion

The above methods will assist you to use conditional formatting greater than another cell in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!

Related Articles

• Excel Alternating Row Color with Conditional Formatting [Video]
• How to Make Negative Numbers Red in Excel (4 Easy Ways)
• 4 Quick Excel Formula to Change Cell Color Based on Date
• Excel Conditional Formatting Dates Older than Today (3 Simple Ways)
• How to Highlight Lowest Value in Excel (11 Ways)
• Excel Formula to Color a Cell If the Value Follows a Condition