Skip to main content

How to highlight cells if value greater or less than a number in Excel?

Let’s say you have a table with large amounts of data, to find the cells with values that are greater or less than a specific value, it will be tiresome and time-consuming. In this tutorial, I will talk about the ways to quickly highlight the cells that contain values greater or less than a specified number in Excel.


Highlight cells if value greater or less than a number with Conditional Formatting

1. Select the data range where you want to highlight the cells that contain values greater or less than a specified value.

2. On the Home tab, click Conditional Formatting > Highlight Cells Rules, and then select Greater Than, Less Than or other options as you need.

3. (Here I selected Greater Than as an example.) In the pop-up Greater Than dialog box, enter the value in the input box at left, and select the format option in the drop-down list at right as shown below.

Note: If you want to use a custom format, choose Custom Format, and then customize your format in the pop-up Format Cells dialog box.

4. The cells that meet the condition you set are highlighted at once.

Note: If you need to edit the rule, such as to change the value (450), the relationship (greater than to less than for example), or the range where to apply the rule, click Conditional Formatting > Manage Rules to bring up the Conditional Formatting Rules Manager.


Highlight cells if value greater or less than a number with Select Specific Cells feature

Kutools for Excel offers the Select Specific Cells feature to help you find multiple values that meet one or two conditions you set at once, please read on to find out how to quickly highlight cells that contain values greater or less than a number.

1. On the Kutools tab, in the Editing group, click Select > Select Specific Cells.

2. In the pop-up Select Specific Cells dialog box, please do as follows:
  • In the Select cells in this range box, click the range-selecting button at right to specify the range from where to highlight the cells. Note: To search across the whole sheet, click on the sheet tab.
  • In the Selection type section, select the Cell option.
  • In the Specific type section, set one or two conditions:
    • Click on the drop-down arrow at left side to select a relationship you need, such as Greater than, Less than, Greater than or equal to, etc.
    • Type the value in the corresponding box according to your needs.
    • Specify the relationship between the two conditions (if there are): And or Or.

3. Click Ok to select the cells that meet the condition(s) you set. A dialog box pops up telling you how many cells were found and selected.

4. Click OK. Now, you can highlight the selected cells at once by giving them a different font color, fill color or other formats.

Note: To use the Select Specific Cells feature, you should have Kutools for Excel installed in your computer. If you do not have Kutools installed, click here to download and install. The professional Excel add-in offers a 30-day free trial with no limitations.


Related articles

How To Highlight Cells Above/Below Average In Excel?

In some special cases, you may need to highlight or conditional formatting the cells which are above/below the average value in a data range in Excel. Here I will tell you how to solve this problem quickly in Excel.

How To Highlight Cells Based On Length Of Text In Excel?

Supposing you are working with a worksheet which has list of text strings, and now, you want to highlight all the cells that the length of the text is greater than 15. This artical, I will talk about some methods for solving this task in Excel.

How To Highlight Cells Between Two Dates In Excel?

If you have a list of dates in your worksheet, and now, you want to highlight the cells or rows which are in between two defined dates, the Conditional Formatting of Excel can do you a favor. This article will talk about how to apply the Conditional Formatting to highlight the cells or rows between two dates.

How To Highlight Cells With Same/Duplicate Values In Excel?

Do you know how to quickly highlight duplicate values in one column in Excel? And what if quickly highlighting all same values between two columns in Excel? This article will discuss several methods about them.

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations