Conditional Formatting If Between Two Numbers – Excel & Google Sheets

This tutorial will demonstrate how to highlight cells that contain a value between two other specified values in Excel and Google Sheets.

 

conditional formatting between two numbers master

 

Conditional Formatting if Between Two Numbers

To highlight cells where the value is between a set minimum and maximum (inclusive), you can use one of the built-in Highlight Cell Rules within the Conditional Formatting menu.

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

 

conditional formatting between two numbers between rule menu

 

  1. You can either type in the bottom and top values, or to make the formatting dynamic (i.e., the result will change if you change the cells), click on the cells that contain the bottom and top values. Click OK.

 

conditional formatting between two numbers between rule

 

Values between 20 and 70 (inclusive) are highlighted.

 

if between result

 

  1. When you change the two values in Cells H4 and H5, you obtain a different result.

 

conditional formatting between two numbers between rule change result

 

Alternate Method – Custom Formula

You can also highlight cells between two specified numbers by creating a New Rule and selecting Use a formula to determine which cells to format.

  1. Select New Rule from the Conditional Formatting menu.

 

conditional formatting between two numbers between rule menu new rule

 

  1. Select Use a formula to determine which cells to format, and enter the formula (using the AND Function):
=AND(B4>=$H$4, B4<=$H$5)
  1. The reference to cells H3 and H5 need to be locked by making them absolute. You can do this by using the $ sign around the row and column indicators, or by pressing F4 on the keyboard.
    To learn more about how symbols are used in Excel, see How to Insert Signs and Symbols.
  2. Click on the Format button.

 

conditional formatting between two numbers formula

 

  1. Choose a fill color for the highlighted cells.

 

conditional format between two numbers format

 

  1. Click OK, then OK again to return to the Conditional Formatting Rules Manager.

 

conditional formatting between two numbers rule manager

 

  1. Click Apply to apply the format to your worksheet.

 

conditional formatting between two numbers final

 

Highlight Cells Between Two Numbers in Google Sheets

Highlighting cells between two numbers in Google Sheets is similar.

  1. Highlight the cells you wish to format, and then click on Format > Conditional Formatting.

 

google sheets conditional formatting between two numbers menu

 

  1. 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 Is Between from the drop-down list and set the minimum and maximum values.

 

conditional formatting between two numbers format rules

 

  1. Once again, you need to use the absolute signs (dollar signs) to lock in the values in Cells H4 and H5.
  2. 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-sheet conditional formatting between two numbers between rule result

 

Custom Formula in Google Sheets

  1. To use a custom formula rather than a built-in rule, select Custom formula is under Format Rules, and type the formula.
=AND(B4>=$H$4, B4<=$H$5)

 

google sheets conditional formatting between two numbers custom formula

 

Remember to use $s or the F4 key to make Cells H4 and H5 absolute.

  1. Select your formatting and click Done.