Highlight Rows Based on Cell Value in Excel
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
To highlight entire rows based on a cell value in Excel or Google Sheets, use conditional formatting with a formula that references the cell in that row (e.g., =$A2="Done") and apply it to the whole row range. This automatically colors every row where the condition is true, updating live as values change.
Why Conditional Formatting Is the Right Tool
Conditional formatting is Excel’s built-in feature for applying formatting (fill color, font, borders) to cells that meet a rule. Unlike manual highlighting, it recalculates whenever data changes, so rows stay correctly colored without redoing the work. It works in Excel for Windows, Mac, and web, and in Google Sheets, with slight differences in menu names. The core idea is the same: a rule with a formula decides which rows get the highlight.
How to Highlight Rows Based on a Cell Value
Here are the steps for Excel (similar in Google Sheets):
- Select the range of rows you want to format—for example,
A2:E100. Do not include the header row unless you want it formatted too. - Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- In the formula box, enter a formula that references the first row of your selection. For instance, if your selection starts at row 2, type
=$A2="Done". The dollar sign locks the column, while the row number is relative so it adjusts for each row. - Click Format, pick a fill color, and click OK.
- Click OK again to apply the rule.
For Google Sheets: select the range, go to Format > Conditional formatting, choose Custom formula is, enter the same formula, set the formatting, and click Done.
Writing the Formula Correctly
The formula is the heart of the rule. It must be a logical test that returns TRUE or FALSE for the first cell in your selected range. The row number in the formula should match the first row of your selection. For example:
- If your data starts in row 2, use
=$A2="High"(not$A$2, which would lock to one row). - For numeric comparisons:
=$B2>100highlights rows where column B exceeds 100. - For multiple conditions (AND):
=AND($A2="Open",$B2<TODAY())highlights open items past due. - For OR logic:
=OR($A2="Urgent",$B2="Critical"). - To highlight entire rows based on a value in another sheet, use a direct reference like
=VLOOKUP($A2,Sheet2!$A:$B,2,FALSE)="Yes"(adjust as needed).
Always use absolute column references ($A2) and relative row numbers ($A2 not $A$2) so the rule applies across rows.
Common Mistakes and How to Avoid Them
Several pitfalls trip up users:
- Wrong row reference: If your selection starts at row 2 but you type
=$A$1, every row will check cell A1, so all rows highlight or none do. Fix: match the first row. - Applying to the wrong range: If you select only column A, only column A gets highlighted, not the whole row. Select all columns you want colored.
- Using text with numbers: If your cell contains a number stored as text,
=B2>100may not work. Use=VALUE(B2)>100or ensure the data is numeric. - Overlapping rules: Multiple rules can conflict. The rule higher in the list takes precedence if “Stop If True” is checked. Reorder rules in the Conditional Formatting Rules Manager.
- Case sensitivity: By default, text comparisons are case-insensitive in Excel. Use
EXACTif you need case-sensitive matching.
Advanced Techniques
Once you master the basics, you can extend the idea:
- Highlight based on a drop-down list: Use a formula that references the cell with the dropdown, e.g.,
=$C2="Yes". - Color scales and data bars: For numeric values, you can use color scales instead of a formula to shade rows based on magnitude—though this applies to individual cells, not whole rows.
- Dynamic ranges: Use a named range or an Excel Table so the formatting automatically extends to new rows. With a table, select a column and the rule applies to the whole column.
- Highlight duplicates in a column: Use
=COUNTIF($A$2:$A$100,$A2)>1to highlight rows with duplicate values in column A. - Highlight based on another cell’s value: For example,
=$A2=$B$1highlights rows where column A equals the value in B1 (a search box).
Performance and Large Datasets
Conditional formatting formulas recalculate every time the sheet changes, so with thousands of rows, complex formulas can slow things down. To keep performance acceptable:
- Apply the rule only to the used range, not entire columns (e.g.,
A2:E1000instead ofA:E). - Avoid volatile functions like
INDIRECTandOFFSETin rules. - Use simpler formulas; a single
ANDorORis fine, but avoid array formulas. - If the sheet is very large, consider using a helper column that computes the condition, then format based on that column’s value.
Frequently Asked Questions
Why is my conditional formatting not working? Check the formula’s row reference matches the first row of your selected range. Also ensure the range includes all columns you want highlighted, and that the rule is not being overridden by another rule higher in the list. Test the formula in a cell first to see if it returns TRUE.
Can I highlight rows based on text that contains a specific word? Yes, use =ISNUMBER(SEARCH("word",$A2)) for partial matches. SEARCH is case-insensitive; use FIND for case-sensitive. Example: =ISNUMBER(SEARCH("urgent",$A2)).
How do I highlight entire rows based on multiple conditions? Combine conditions with AND or OR. For example, =AND($A2="Active",$B2>TODAY()) highlights rows where status is Active and the date is in the future. Use parentheses to group conditions.
Does conditional formatting work in Google Sheets the same way? Yes, the logic is identical, but the menu path differs: Format > Conditional formatting > Custom formula is. The formula syntax is the same, but note that Google Sheets uses ~ for regex, not for standard text.
Can I highlight rows based on a cell value in another sheet? Yes, reference the other sheet directly in the formula, e.g., =VLOOKUP($A2,Sheet2!$A:$B,2,FALSE)="Paid". Ensure the other sheet’s name is correct and the range is absolute.
How do I remove or edit a conditional formatting rule? Go to Home > Conditional Formatting > Manage Rules. Select the rule and edit or delete it. In Google Sheets, open Format > Conditional formatting and click the trash icon next to the rule.
Related
Formula used
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.