Go Back

How to Use Conditional Formatting to Highlight Cells Less Than or Greater Than Some Value

Read time: 24 minutes

If you want to highlight cells based on a value as criteria, then you can use conditional formatting by using a built-in rule and a custom formula. This tutorial will show you how with several illustrated examples. 

Use conditional highlighting to highlight cells less than or greater than a value

In the example below, you will set conditional formatting so that a cell:

    • Turns dark Blue if it contains a value greater than 90
    • Turns dark Blue if it contains a value greater than equal to 90.

Using the Built-in Rule

Select the range you want to format. In our case C5:G10 is selected. On the Excel Ribbon’s Home tab, click Conditional Formatting, to format the values greater than a specific one, select Highlight Cells Rules and then choose the option Greater Than.

In the Greater Than window, you have to delete the value that appears in the box, and then select the cell reference, in this example $I$6, or input specific value that is the lowest limit for you.

To drop down the list for formats click Custom Format, click the Fill tab, and click on the blue dark fill color that you want.

To close the Format cells window click Ok, the cells with values greater than 90 would be colored dark blue as you choose the color format. Again press OK

Using customer Formula Rule

You can conditionally highlight the cells which are greater than or equal to set a value using customer-formula rule.

Select the cells to be formatted. In this example, cells C5:G10 is selected, From the Home tab, click the Conditional Formatting selection. Click on “New Rule” and click on “Use a formula to determine….” and enter following formula in Edit Rule Description window. Choose Format > Fill > Dark Blue color to preview and press OK.

=C5>=$I$6

This formula will check all the active cells in the selected range and will test each cell value against the set value in cell $I$6. All those cells will be highlighted which are greater than equal to a set value of 90.

Need some additional help with Conditional Formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Conditional formatting across a column Y6>=$C$2 Format J6 I've tried relative and absolute referencing to the cells and used format painter to copy down column J but it all formats when it shouldn't, same when using colum referencing rther than cell. So checking if a date cell is greater than or equal to a date in cell C2 and if it is to change the format of cell in column J. Should I be doing this a different way?
Solved by F. Q. in 16 mins
Hello I was wondering if theres a way to use conditional formatting on a cell that is controlled by a sum of other cells for example a1+a2 is less than a3 then a3 turns one colour and a1+a2 is greater than a3 then a3 turns another colour. many thanks
Solved by S. U. in 27 mins
Hello I am trying to highlight a cell one of two ways based on condition. So for example Cell C3 is the previous run time, and cell C4 is the most recent run time. I want cell C4 to highlight red if the value of C3 is less than C4, but I want the cell to highlight green if the value of C3 is greater than C4... how do i two this two level conditional formatting?
Solved by I. J. in 16 mins
Hello, I am looking to highlight the cells that have text in them to highlight as red between the 1st through the 15th of every month, and then be green the 16th through last day of the month whether it be the 30th, 31st, or even 28th (depends on the month). I attached a sheet in which I was testing by highlighting all of the cells with text (the 1 of 8, 2 of 4, etc.) then clicking "Conditional Formatting" > "Highlight Cells Rules" > and then either Greater Than or Less Than for both red and green using the following formulas for each month: Less Than (Red): 2017-08-15 2017-09-15 2017-10-15 2017-11-15 2017-12-15 Greater Than (Green): 2017-08-15 2017-09-15 2017-10-15 2017-11-15 2017-12-15 I am not sure what I am doing wrong but when I did the formula and chose the colors for August, it seemed to work fine, being today's date would be within the right date range for the cells to highlight as green. I then removed the August formulas and replaced them with September formulas, it did not seem to work.
Solved by C. W. in 14 mins
Hi, I've got 2 columns in Excel. The first has the quantity of an item in stock, the second has the quantity needed for a job. For the second collum, I have conditional formatting so that if the number required is greater than the stock, the cell turns red. And if it is ok (so between 1 & number in stock) then it turns green. Is there a way to copy the conditional formatting to all the other items? At the moment, (using both copy/paste, and the copy formatting brush) it copies the conditional formatting, but references it to the copied item's stock, rather than applying it to the new item's stock.
Solved by X. W. in 17 mins
How do i represent in a table, using conditional formatting, 5% greater than another cells predefined value.
Solved by O. H. in 27 mins
How to write a conditional formatting formula which will apply formatting to C2 IF: C2 contains a value which appears more than once in column C AND ( G2 does NOT contain the text "0001-01-01" OR F2 value is greater than 7/24/17)
Solved by S. Q. in 28 mins
I am looking at getting a conditional formatting equation set up where it is based off if the cell above is greater than or less than. And I am looking at trying to have this for thousands of cells so I do not have to do it for each individual cell, I could just drag it down or use the format painter.
Solved by O. A. in 17 mins

1
Leave a Comment

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
thanh Recent comment authors
newest oldest
thanh
Guest
thanh

It is helpful! Thanks!