Highlight Rows using Conditional Formatting in Excel

1. Highlight a Row Based on a Value (Text)

1. Select the entire table, all the rows and columns.
   
2. Go to Conditional Formatting > New Rule.
   
3. In the new rule dialog box, click “Use a formula to determine which cell to format…”.
   
4. In the “Format values where this formula is true”, enter the formula =$B2=”Sofas”.
   
5. After that, click the Format button to specify the color you want to apply to the rows.
   
6. In the end, click OK, and then again OK to apply the conditional formatting.

When you click OK, it will highlight the rows where the product name is “Sofas”.

The $ before B is what makes the whole row change color: every cell in the row checks column B. And there’s no $ before the 2, so the next row checks B3, then B4, and so on. Make sure the row number in the formula is the first row of your selection.

2. Highlight a Row Based on a Value (Partial Text)

And if you want to write a formula to check for a partial text, use the SEARCH function. In the below example, we need to highlight all the rows where you have “HS-” in the product column.

=ISNUMBER(SEARCH("HS-",$B2))

SEARCH returns the position of “HS-” when it finds it, and ISNUMBER turns that into TRUE. SEARCH isn’t case-sensitive, so “hs-” matches too; use FIND instead of SEARCH if the case matters.

And the moment you click OK, it highlights all the rows where there’s the partial text “HS-“.

3. Highlight a Row Based on a Value (Number)

Now let’s say you want to highlight the rows where the aging days are 25 days or more. In this case, you need to use the same steps we have used above. But the formula needs to be different.

You need to use the formula =$D2>=25. This formula checks if the cell’s value is greater than or equal to 25. And if it is, it highlights the entire row in the table.

4. Highlight Rows Based on Multiple Conditions

You can also create a formula to test multiple conditions. You can use the AND and OR functions in the formula.

=AND($D2>=25,$C2>=15)

When you enter this formula in the new rule dialog box, it highlights rows based on two conditions:

• Days Aging needs to be greater than or equal to 25.
• Quantity needs to be greater than or equal to 15.

That’s why, in the formula, we have used AND, which helps you test two or more conditions simultaneously and returns TRUE only when both conditions are TRUE.

And if you want to highlight the row when any one of the conditions is true, use OR instead:

=OR($D2>=25,$C2>=15)

5. Highlight the Row IF Any Cell Is Blank

Let’s say you want to create a condition that if any of the cells in a row is blank, the entire row gets highlighted with a color you define.

Select the data and then go to the Home Tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format; enter the formula =COUNTIF($A2:$D2,””)>0 and then click on the Format button to specify the formatting.

And once you click OK, it will highlight all the rows where even a single cell is blank.

6. Highlight Rows with a Dynamic Value from a Drop-Down

You can also create a drop-down from which you can select the value, and the conditional formatting will use that value to highlight the rows. So, first, create a drop-down list in cell F1. For this, go to the Data Tab > Data Validation.

In the data validation dialog box, select List from the “Allow” drop-down and enter the following formula in Source:

=OFFSET($B$2, 0, 0, COUNTA($B$2:$B$11), 1)

Finally, click OK for a drop-down list with the product names. If a product appears in more than one row, it also appears more than once in this list.

After that, select the data and open the conditional formatting’s New Rule dialog box. Next, click “Use a formula to determine which cells to format”, and then enter the following formula in “Format values where this formula is true”:

=$B2=$F$1

And make sure to click on the Format button to specify the color you want to use to highlight the row.

In the end, click OK to apply the rule. And when you change the value in the drop-down, it highlights the rows based on that value. So this conditional formatting rule uses the value from the drop-down in cell F1.

Why the Whole Row Is Not Highlighted

• Missing $ before the column: with =B2=”Sofas” (no $), each column checks a different cell, so only some cells change color. Use =$B2.
• $ before the row number: with =$B$2=”Sofas”, every row checks B2, so either all rows or none get highlighted.
• Wrong starting row: if your selection starts at row 2 but the formula uses row 3, the highlight shifts one row. The row in the formula must match the first row you selected.
• Selection is one column only: the rule only formats the cells you selected, so select all the columns of the table.

Frequently Asked Questions