How to Highlight Rows That Contain Specific Text in Excel

Three ways to highlight rows that contain specific text in Excel

To highlight rows that contain specific text in Excel, use a conditional formatting rule for a result that stays live, filter and fill color for a quick manual pass, or a free macro that colors every matching row in one click. The macro is the only one that checks every column at once without a formula.

Which fits depends on whether your data keeps changing and how many columns the word might hide in. Here's the short version first.

Method	Best for	Updates live?	One click?
Conditional formatting	A rule that re-checks as data changes	Yes	No, formula setup
Filter + fill color	A quick manual pass on one column	No	No
Free macro	A fast snapshot across every column	No	Yes

TL;DR: Key takeaways

• To highlight rows containing text, pick conditional formatting for a live rule, a filter-and-fill for a manual pass, or the macro for a one-click snapshot.
• The conditional formatting formula =COUNTIF($A2:$F2,"*word*")>0 colors any row where a cell contains the word, and re-checks automatically.
• Lock the column with $ (like $C2) so the whole row highlights, not just the one cell.
• The free macro searches every column in your selection, matches partial text, and colors matching rows in one click.
• Conditional formatting stays live. The macro paints a static color, so rerun it after the data changes.

Method 1: Conditional formatting with a formula

The built-in way to highlight rows containing text is a conditional formatting rule driven by a formula.

1. Select your data range, leaving the header row out.
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. To match a word in one column, enter =ISNUMBER(SEARCH("urgent",$C2)). To match a word in any column of the row, enter =COUNTIF($A2:$F2,"*urgent*")>0.
4. Click Format, choose a fill color, and click OK.

The $ before the column letter locks it, so Excel checks that column for every cell in the row, which is what paints the whole row. Microsoft's guide to conditional formatting walks through the dialog. SEARCH is case-insensitive, so urgent also matches URGENT. The upside is that the rule re-runs whenever the data changes. The downside is that mixed references trip people up, and changing the keyword means editing the rule.

Method 2: Filter, then fill color

For a quick one-time pass, filter the column and color the rows that survive the filter.

1. Select your data and choose Data > Filter.
2. Open the column's filter, pick Text Filters > Contains, and type your keyword.
3. Select the visible rows and apply a fill color from the Home tab.
4. Clear the filter to show every row again.

Microsoft's filter guide covers the Text Filters options. It's fast and needs no formula, but it checks one column at a time, and the color it leaves behind is static.

Method 3: A free one-click macro (searches every column)

When you want every column searched at once and a result in a single click, use a macro. Download Highlight Rows Containing Keyword. It's a free .xlsm file with one macro. No install, no signup, and it runs offline so your data stays on your machine.

First time running one of our macros? See How to run our tools for the 30-second setup.

1. Select the range you want to search, including all the columns.
2. Press Alt + F8, pick HighlightRowsContainingKeyword, and click Run.
3. Type your keyword when prompted. The match is case-insensitive and catches partial words.
4. Every row where any cell contains the keyword turns amber, and the macro reports how many it found.

It only colors rows inside your selection, so nothing else on the sheet changes. To flag a second keyword, run it again. Both passes use the same amber fill, so recolor one set manually if you need to tell them apart.

Example: flagging a product across a sales report

An operations lead at a Chennai electronics distributor has a 500-row monthly sales report. Finance wants every line that mentions "inverter" flagged, but the product name turns up in different columns: the item name on some rows, the SKU description on others, the free-text notes on a few. A single-column filter misses the rows where "inverter" only appears in the notes.

She selects all 500 rows and every column, runs the macro, and types inverter. Every row with the word anywhere in it turns amber, 63 of them. The scan that used to mean filtering three columns one at a time now takes about 5 seconds, and nothing buried in the notes slips through.

When conditional formatting is the better choice

Two honest limits, so you pick the right tool.

The color is static. The macro paints the rows once. If your data keeps changing, or you're still entering rows, a conditional formatting rule is better because it re-checks on its own. Use the macro for a snapshot and conditional formatting for a sheet you edit often.

Partial matches can over-catch. Because the match is partial, cat also highlights category and vacate. Type a more specific term, or add spaces around the word, if you get false hits. The macro also searches for one keyword per run, not a list.

For related jobs, highlight duplicate values flags repeats instead of a keyword, and once rows are colored, count and sum cells by color turns those highlights into totals. All three sit inside the guide to Excel productivity macros.

FAQ

How do I highlight an entire row based on a cell value in Excel?

Use a conditional formatting rule with a formula and a locked column reference: =$C2="Urgent" for an exact value, or =COUNTIF($A2:$F2,"*urgent*")>0 for a partial match. Select the whole data range first so the rule colors every row, not just the one matching cell.

How do I highlight rows that contain specific text, not an exact match?

Use SEARCH inside conditional formatting (=ISNUMBER(SEARCH("word",$C2))), or run the free macro, which matches partial text by default. Both find the word even when it sits inside a longer string, so "inverter" matches "solar inverter 5kW".

Can I highlight rows based on a keyword in any column?

Yes. In conditional formatting, use =COUNTIF($A2:$F2,"*word*")>0 across the row's columns. The macro does this for you: it checks every cell in each selected row and colors the whole row if any cell contains your keyword.

Will the highlight update if my data changes?

Conditional formatting updates automatically when the data changes. The macro applies a fixed color that stays put, so rerun it if you edit the data later. For a sheet you change often, conditional formatting is the better fit.

Does this work on Excel for the web or Mac?

The macro runs on Excel for Windows and Mac, version 2016 and later. It does not run in Excel for the web, which has no VBA. Conditional formatting and filters work everywhere, including the web.

What to do next

Pick by whether the sheet keeps changing. Conditional formatting for a live rule, a filter-and-fill for a quick manual pass, and the macro for a one-click snapshot across every column.

Download the free Highlight Rows Containing Keyword macro and run it on your own data. It's the fastest way to highlight rows that contain specific text in Excel when the word could be hiding in any column.