This guide shows you how to highlight entire rows in Excel based on a text search. You type a word or phrase, and Excel automatically highlights every row where that text appears in a specific column. This makes it much easier to spot relevant records in large spreadsheets. You’ll need a basic understanding of Excel’s ribbon and a dataset with a column you want to search.
What You’ll Need
- Microsoft Excel (2010 or later, including Microsoft 365).
- A spreadsheet with data organized in rows and columns.
- A specific column you want to search for text (for example, “Product Name” or “Status”).
Step-by-Step
- Select the data range. Click and drag to select all the cells that contain your data, including the header row. For example, if your data is in A1:C50, select that range. If your data is large, click the top-left cell and press Ctrl+Shift+End to select the entire block.
- Open Conditional Formatting. On the Home tab, in the Styles group, click Conditional Formatting. From the dropdown, choose New Rule.
- Choose a rule type. In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
- Enter the formula. In the formula box, type a formula that checks the column you want to search. For example, if your search column is column B (the second column), and your selected range starts at row 1, enter:
=$B1="keyword". Replacekeywordwith the exact text you want to match. Use the dollar sign before the column letter to keep the column fixed. - Set the format. Click the Format button, go to the Fill tab, choose a highlight color (like yellow), and click OK.
- Apply the rule. Click OK again to close the New Formatting Rule dialog. The rows containing the keyword in column B will now be highlighted.
- Make it searchable (optional). To change the keyword without editing the formula each time, put the search text in a separate cell, say
E1. Then use a formula like=$B1=$E$1for exact match, or=ISNUMBER(SEARCH($E$1,$B1))for partial match. Now you can type a word in E1 and the highlighting updates instantly.
Common Mistakes
- Forgetting the dollar sign. If you use
B1instead of$B1, the rule will check different columns for different rows, causing incorrect highlighting. - Using the wrong row reference. The row number in the formula should match the first row of your selected range. If you select A2:C50, the formula should reference row 2 (e.g.,
$B2). - Applying the rule to a single column instead of the whole range. If you only select column B before creating the rule, only column B cells will be highlighted, not entire rows.
- Using exact match when you need partial. The formula
=$B1="apple"only matches cells that contain exactly “apple” (not “pineapple” or “Apple”). UseSEARCHfor case-insensitive partial matches.
Tips
- Use wildcards for partial matches. In the formula, you can use
*and?with theCOUNTIFfunction. For example,=COUNTIF($B1,"*"&$E$1&"*")>0works similarly to SEARCH. - Highlight multiple columns. To search in more than one column, use the OR function:
=OR($B1=$E$1,$C1=$E$1). - Make the search case-insensitive. The SEARCH function is case-insensitive, while FIND is case-sensitive. Use SEARCH for most text searches.
- Clear formatting easily. To remove the highlighting, go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
- Use a table. If you convert your data to a table (Ctrl+T), conditional formatting rules will automatically apply to new rows added to the table.
Troubleshooting
- Nothing gets highlighted. Check the formula for typos. Ensure the column letter in the formula matches your search column. Verify that the search text in the cell (if used) is spelled exactly as it appears in the data, including spaces.
- Only some rows highlight. This often happens when the row reference in the formula doesn’t match the selected range’s first row. For example, if you selected A2:A100 but used
$B1, the rule will be off by one row. Adjust the row number accordingly. - Highlighting applies to the wrong column. If you selected only a single column before creating the rule, the formatting only applies to that column. Re-select the entire data range and re-create the rule.
- Partial matches are not working. If you used
=$B1="text", it only matches exact, case-sensitive text. Use=ISNUMBER(SEARCH("text",$B1))for partial, case-insensitive matches. - The rule disappears. If you copy and paste cells, conditional formatting may be overridden. Re-apply the rule after major edits, or use a table to preserve formatting.
Frequently Asked Questions
Can I highlight rows based on text in any column?
Yes. Adjust the column letter in the formula to match the column you want to search. For example, =$C1="Pending" highlights rows where column C contains “Pending”.
How do I highlight rows that contain a word anywhere in the row?
Use a formula that checks all columns, like =COUNTIF($A1:$C1,"*"&$E$1&"*")>0. This searches across columns A through C for the text in E1.
Can I use the search box to highlight rows without typing a formula?
No, the regular search box (Ctrl+F) only finds cells; it doesn’t highlight rows. Conditional formatting is the standard way to achieve this.
Will the highlighting update if I change the search text?
Yes, if you use a reference to a cell (like $E$1) in your formula, the highlighting updates automatically when you change the text in that cell.
How do I highlight rows that do NOT match the search text?
Use the NOT function or the <> operator. For example, =$B1<>$E$1 highlights rows where column B is different from the text in E1.
Can I highlight rows based on multiple search terms?
Yes, use the OR function. For example, =OR($B1="apple",$B1="orange") highlights rows with either fruit.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Disable Autocorrect on iPhone or iPad: Quick Guide
- Custom Keyboard Shortcuts on Windows 11 & macOS
- Restore Windows 11 Display Scaling to Recommended
- Semiconductor Cycle: How Investors Track It0.0
- Microsoft Store Error 0x80073d12: Fixes That Work
- View Notification Count on iPhone: Full Guide