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

Data Analysis

  • Get column index in Excel Table
  • Conditional Formatting Icon Sets Examples in Excel
  • How to calculate average last N values in a table in Excel
  • How to sum a total in multiple Excel tables
  • How to Use Solver Tool in Excel

References

  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Merge tables with VLOOKUP in Excel
  • How to get first column number in range in Excel
  • Count rows that contain specific values in Excel
  • Excel Advanced Lookup using Index and Match Functions

Data Validations

  • Excel Data validation only dates between
  • Excel Data validation allow weekday only
  • Data validation must not exist in list
  • Excel Data validation must contain specific text
  • Excel Data validation specific characters only

Highlight entire rows in Excel

by

This tutorial shows how to Highlight entire rows in Excel using the example below;

Formula

=($A1=criteria)

Explanation

To highlight entire rows with conditional formatting when a value meets specific criteria, use a formula with a mixed reference that locks the column. In the example shown, all rows where the owner is “bob” are highlighted with the following formula applied to B5:E12:

 =$D5="Bob"

Note: CF formulas are entered relative to the “active cell” in the selection, B5 in this case.

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. In this case, the address of the active cell (B5) is used for the row (5) and entered as a mixed address, with column D locked and the row left relative. When the rule is evaluated for each of the 40 cells in B5:E12, the row will change, but the column will not.

Worked Example:   Highlight cells that contain one of many in Excel

Effectively, this causes the rule to ignore values in columns B, C, and E and only test values in column D. When the value in column D for in a given row is “Bob”, the rule will return TRUE for all cells in that row and formatting will be applied to the entire row.

Worked Example:   Managing Conditional Formatting Rules in Excel

Using other cells as inputs

Note that you don’t have to hard-code any values that might change into the rule. Instead you can use another cell as an “input” cell to hold the value so that you can easily change it later. For example, in this case, you could put “Bob” into cell D2 and then rewrite the formula like so:

=$D5=$D$2

You can then change D2 to any priority 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.

Worked Example:   Find duplicate values in two columns in Excel

Named ranges for a cleaner syntax

Another way to lock references is is to use named ranges, since named ranges are automatically absolute. For example, if you name D2 “owner”, you can rewrite the formula with a cleaner syntax as follows:

=$D5=owner

This makes the formula easier to read and understand.

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

  • Not Equal To ‘<>‘ operator in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • IFS function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • Get days between dates ignoring years in Excel
  • Convert date to Julian format in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Calculate conditional mode with criteria in Excel
  • Map inputs to arbitrary values in Excel
  • Group numbers at uneven intervals in Excel
  • Group arbitrary text values in Excel
  • Running count group by n size in Excel

General

  • How to get random value from list or table in Excel
  • Check if range contains a value not in another range in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • How to count total number of cells in a rectangular range in Excel
  • 3D SUMIF for multiple worksheets in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning