How to Apply Conditional Formatting if Between Two Values

By /

Conditional formatting can be used to format a cell automatically based on certain conditions. For example, if you want to apply a certain formatting to cells that are between two values, you can select the cells of interest and then select the “Format Cells” option from the Home tab of the ribbon. Then, in the “Conditional Formatting” menu, select the “New Rule” option and select “Format only cells that contain” option. In the “Format only cells with” section, select “Cell Value Between” option and then enter the two values between which the cell values should be. Finally, select the desired formatting and click “OK” to apply it.

Often you may want to apply conditional formatting to cells whose values fall between two specific values in Excel.

The following example shows exactly how to do so.

Example: Conditional Formatting if Between Two Values

Suppose we have the following dataset that contains information about various basketball teams:

Suppose we would like to highlight each value in the points column where the value is between 10 and 20.

To do so, highlight the values in the range B2:B14, then click the Conditional Formatting icon on the Home tab, then click Highlight Cells Rules, then Between:

In the new window that appears, choose 10 as the lower value and 20 as the upper value:

One you click OK, the values in the points column that fall between 10 and 20 will automatically be formatted with a light red background and dark red text:

Note that you can also choose a different format for the cells.

For example, you could choose to format the cells using a green background with dark green text:

conditional formatting between two values in Excel

You can also choose Custom Format to specify a custom format style for the cells:

How to Apply Conditional Formatting if Cell Contains Text in Excel

x

Related terms:

  1. Excel: Apply Conditional Formatting to Yes/No Values
  2. Student asks: How do I apply conditional formatting to duplicate values?
  3. Excel: Apply Conditional Formatting to Second Highest Value
  4. Excel: Apply Conditional Formatting to Dates Older Than 1 Year
  5. How to Apply Conditional Formatting to Dates within 6 Months in Excel
  6. How to Apply Conditional Formatting Based on Adjacent Cell in Excel?
  7. How to Apply Conditional Formatting to Blank Cells in Excel?
  8. How to apply conditional formatting to a cell based on the value in another cell?
  9. How to apply conditional formatting if two cells are not equal
  10. What is Excel: Apply Conditional Formatting if Cell Contains Text