Conditional Format Greater Than Or Less Than – Excel & Google Sheets

This tutorial will demonstrate how to highlight cells based on whether they are greater than or less than another cell using Conditional Formatting in Excel and Google Sheets.

 

conditional formatting greater less than master

 

Use a Highlight Cell Rule

To highlight cells where the value is greater than a specified number, you can use one of the built in Highlight Cell rules within the Conditional Formatting menu.

Greater Than

  1. Select the range to apply the formatting (e.g., B4:E12).
  2. In the Ribbon, select Home > Conditional Formatting > Highlight Cells Rules > Greater Than…

 

conditional formatting greater than rule

 

  1. You can either type in the value, or to make the formatting dynamic (i.e., the result will change if you change the values), you can click on the cell that contains the value you require.

 

  1. Select the type of formatting you require from the drop down box and click OK.

 

conditional formatting greater than box

 

The table now indicates which values are greater than that in cell H4 (20) by making them red.

 

conditional formatting greater than formatting

 

  1. Change the value in H4 to obtain a different result.

 

conditional formatting greater than change value

 

Less Than

  1. Select the range to apply the formatting.
  2. In the Ribbon, select Home > Conditional Formatting > Highlight Cells Rules > Less Than…

 

conditional formatting less than rule

 

  1. As before, click on the cell that contains the value you require.
  2. Click OK to format the cells with the desired formatting.

 

conditional formatting less than format

 

The resulting formatting shows numbers less than 70 in yellow.

conditional formatting less than value

 

Highlight Cells With a Custom Function

With Conditional Formatting, you can also highlight cells by using a custom function.

Greater Than AND Less Than

You can highlight cells that have a greater value than one cell, but a smaller value than another by creating a New Rule and selecting Use a formula to determine which cells to format.

  1. Select the range to apply the formatting.
  2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

conditional formatting greater less than menu

 

  1. Select Use a formula to determine which cells to format, and enter this formula that uses the AND Function:
=AND(B4>$H$4, B4<$H$5)

 

  1. You need to lock the reference to cells H4 and H5 by making them absolutes. Do this by using the $ sign around the row and column indicators, or by pressing F4 on the keyboard.
  2. Finally, click on the Format button.

 

conditional formatting greater less than formula

 

  1. Select the formatting you want for numbers between H4 (20) and H5 (70). Click OK.

 

conditional formatting greater less than format

 

The result is similar to the previous examples, just highlighting on a different condition.

conditional formatting greater less than master final

 

Greater Than OR Less Than

To highlight cells that have a greater value than one cell or have a smaller value than another cell (i.e., outside the range of the two cells), follow these steps:

  1. Select the range to apply the formatting.
  2. In the Ribbon, select Home > Conditional Formatting > New Rule
  3. Select Use a formula to determine which cells to format, and enter this formula that uses the OR Function:
=OR(B4>$H$4, B4<$H$5)

 

  1. Once again, lock the reference to cells H4 and H5 by making them absolutes. Use the $ sign around the row and column indicators, or press F4 on the keyboard.
  2. Click on the Format button.

 

google sheets conditional formatting greater less than new rule

 

  1. Select the formatting you want for numbers that are not between H4 (20) and H5 (70). Click OK.

 

conditional formatting less than or final

 

Highlight Cells With a Custom Function in Google Sheets

The process to highlight cells with a greater value than one cell, and a smaller value than another cell in Google Sheets is similar to the process in Excel.

  1. Highlight the cells you wish to format, and then click on Format > Conditional Formatting.
  2. The Apply to Range section will already be filled in.

 

google sheets conditional formatting greater less than range

 

  1. From the Format Rules section, select Custom Formula from the drop-down list and type in the following formula:
=AND(B4>$H$4, B4<$H$5)

 

google sheets conditional formatting greater less than formula

 

Once again, use the absolute signs (dollar signs) to lock in the values in H4 and H5.

  1. Select the fill style for the cells that meet the criteria.

 

google sheets conditional formatting greater less than format

 

  1. Click Done to apply the rule.

 

google sheets conditional formatting greater less than final