How to highlight row if cell contains text/value/blank in Excel?

For example we have a purchase table in Excel, now we want to find out the purchase orders of apple and then highlight the entire rows where the orders of apple are in as the left screenshot shown. We can get it done easily in Excel with Conditional Formatting command or Kutools for Excel's features, please read on to find out how.

Highlight entire row if cell contains specific text/value/blank with Conditional Formatting

Highlight entire row if cell contains specific text/value with Kutools for Excel

Highlight entire row if cell contains one of specific values in another column

Highlight row if cell contains specific text/value/blank with Conditional Formatting 

To highlight entire rows of cells containing the specific text, value or just blank cells with the Conditional Formatting command in Excel, you can do as following:

1. Select the purchase table excluding its column headings.

2. Click "Home" > "Conditional Formatting" > "New Rule". See the screenshot below:

3. In the coming New Formatting Rule dialog box as shown in the screenshot below, please do as follows:
(1) Click to select the "Use a formula to determine which cells to format "in the "Select a Rule Type "box;
(2) In the "Format values where this formula is true" box, enter this formula:

=$B2="Apple"

Notes:
(1) In the formula =$B2="Apple", $B2 is the cell you will check if contains the specific text or value, and "Apple" is the specific text, you can change them as you need. And this formula can only find out cells only containing the specific text or value.
(2) If you want to highlight rows if cells are blank, use this formula: =$B2="".
(3) If you want to highlight rows if cells begin with specific text, you need to enter =LEFT($B2,5)="Apple"; or to highlight rows if cells end with specific text, enter =RIGHT($B2,5)="Apple".

4. Now the Format Cells dialog box is opening. Go to the "Fill" tab, specify a background color, and click the "OK" button.

5. Click "OK "button to close the New Formatting Rule dialog box.

Then all rows containing the specific content cells in the selected range are highlighted.

Highlight row if cell contains specific text/value with Kutools for Excel

With "Kutools for Excel"'s "Select Specific Cells" feature, we can select the rows if cells contain specific text or value, and then highlight these rows easily in Excel.

1. Select the column where you will find out cells if contain specific text or value.

2. Click "Kutools" > "Select " >" Select Specific Cells".

3. In the opening Select Specific Cells dialog box as above screenshot shown, please do as follows:
(1) Check the "Entire row" in the "Selection type" section;
(2) In the "Specific Type" section, click the first box and specify the "Contains" from the drop down list, and then enter the specific text into the following box.
(3) Click the "OK" button.

4. In the second Select Specific Cell dialog box, click the "OK" button. And then all cells with the entire rows which contain the specific text or value are selected.

5. Click the "Fill Color" button on the "Home" tab, and then specify a highlight color from the drop down list.

 Highlight entire row if cell contains one of specific values in another column

Sometimes you may need to highlight the entire row only if its cell contains one of specific values in another column. Kutools for Excel’s "Compare Ranges" utility provides another workaround to highlight entire rows if cells contain one of specific values easily.

1. Click "Kutools" >" Select > Select Same & Different Cells".

2. In the Select Same & Different Cells dialog box as shown in the right screenshot, please do as follows:

(1) In the "Find values in "box, please specify the Column whose cells you will check if containing one of specific values;
(2) In the "According to" box, please specify the column containing the specific values;
(3) In the "Based on" section, please check the "Each row" option;
(4) In the "Find "section please check the" Same Values" option;
(5) In the "Processing of results "dialog box, please check the "Fill backcolor" option and specify the fill color;
(6) Check the "Select entire rows" option.

3. Click the" OK "button to apply this utility. And then another Compare Ranges dialog box comes out and shows us how many rows have been selected. Just click the "OK" button to close it.

And then you will see if a cell contains one of specific values in another column, the entire row of this cell is highlighted and filled with the specific color.

Demo: highlight row if cell contains certain value or one of specified values

Related Articles

How to prevent saving if specific cell is blank in Excel?

How to not calculate (ignore formula) if cell is blank in Excel?

How to use IF function with AND, OR, and NOT in Excel?

How to display warning/alert messages if cells are blank in Excel?

How to enter/display text or message if cells are blank in Excel?

How to delete rows if cells are blank in a long list in Excel?