Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Remove Duplicates Example in Excel
  • Excel Pie Chart
  • How to Create One and Two Variable Data Tables in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Conditional Formatting Color Scales Examples in Excel

References

  • Create hyperlink with VLOOKUP in Excel
  • How to get last row in text data in Excel
  • How to use Excel FORMULATEXT function
  • Multi-criteria lookup and transpose in Excel
  • Extract data with helper column in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation allow uppercase only
  • Data validation must not exist in list
  • Excel Data validation require unique number
  • Excel Data validation exists in list

Highlight values greater than in Excel

by

This tutorial shows how to Highlight values greater than in Excel using the example below;

Note: Excel contains built-in “presets” for highlighting values above / below / equal to certain values, but if you want more flexibility you can apply conditional formatting with your own formula as explained in this article.

Formula

=A1>X

Explanation

If you want to highlight cells that are “greater than X” with conditional formatting, you can use a simple formula that returns TRUE when a cell value is greater than X. For example, if you have numbers in the cells B4:G11, and want to highlight cells with a numeric value over 100, you select B4:G11 and create a conditional formatting rule that uses this formula:

=B4>100

It’s important that the formula be entered relative to the “active cell” in the selection.

Worked Example:   Highlight entire rows in Excel

To highlight cellsĀ less thanĀ 100 with a conditional formatting formula, use:

=B4<100

How this formula works

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. So, in this case the formula =B4>100 is evaluated for each of the 40 cells in B4:G11. Because B4 is entered as a relative address, the address will be updated each time the formula is applied. The net effect is that each cell in B4:G11 will be compared to 100 and the formula will return TRUE if the value in the cell is greater than 100. When a conditional formatting rule returns TRUE, the formatting is triggered.

Worked Example:   Highlight rows that contain in Excel

Using a another cell as an input

Note that there is no need to hard-code the number 100 into the rule. To make a more flexible, interactive conditional formatting rule, you can use another cell like a variable in the formula. For example, if you want to use cell G2 as an input cell, you can use this formula:

=B4>$G$2

You can then change the value in cell G2 to anything you like and the conditional formatting rule will respond instantly. Just make sure you use an absolute address to keep the input cell address from changing. Another way to lock the reference is to use a named range, since named ranges are automatically absolute. Just name cell G2 “input” then write the conditional formatting formula like so:

=B4>input

Post navigation

Previous Post:

Highlight values between in Excel

Next Post:

Highlight values not between X and Y in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Not Equal To ‘<>‘ operator in Excel
  • Extract multiple matches into separate rows in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Add workdays no weekends in Excel
  • Convert decimal seconds to Excel time
  • Check If Two Dates are same month in Excel
  • Generate series of dates by weekends in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel
  • Calculate conditional mode with criteria in Excel
  • If cell contains one of many things in Excel
  • Categorize text with keywords in Excel

General

  • How to calculate percent change in Excel
  • How to choose page/paper size in Excel before Printing
  • Zoom Worksheet in Excel
  • Flash Fill in Excel
  • 3D SUMIF for multiple worksheets in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning