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

Data Analysis

  • How to Create Thermometer Chart in Excel
  • Use Data Form to input, edit and delete records in Excel
  • Randomize/ Shuffle List in Excel
  • Number and Text Filters Examples in Excel
  • How to Use Solver Tool in Excel

References

  • How to use Excel ROW Function
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Merge tables with VLOOKUP in Excel
  • How to use Excel TRANSPOSE Function
  • How to get last row in text data in Excel

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation number multiple 100
  • Excel Data validation must not contain
  • Excel Data validation specific characters only
  • Excel Data validation must contain specific text

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 top values 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 integers only 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:

VLOOKUP without #N/A error in Excel

Next Post:

How to Create Gantt Chart 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

  • OR function: Description, Usage, Syntax, Examples and Explanation
  • IF, AND, OR and NOT Functions Examples in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • How to use Excel XOR Function

Date Time

  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Calculate total hours that fall between two times in Excel
  • Display Date is workday in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Extract time from a date and time in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group times into unequal buckets in Excel
  • Calculate conditional mode with criteria in Excel
  • If cell contains one of many things in Excel
  • Map inputs to arbitrary values in Excel

General

  • How to calculate percentage of total in Excel
  • How to calculate profit margin percentage in Excel
  • With vs Without Array Formula in Excel
  • Spell Check in Excel
  • Freeze and Unfreeze Panes in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning