Highlight Rows using Conditional Formatting in Excel

In Excel, by using conditional formatting, you can use highlight the entire row. When a condition is true, the row should highlight with the specified color. For example, below, we have a table with the stock data.

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 column 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”.

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.

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 higher than 25 days. 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 and equal to 25. And if it is, then highlight 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-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.

5. Highlight the Row IF any of the 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 should highlight 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 a Rows with a Dynamic Value with Drop Down

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

In the data validation dialog box, select the list from the “Allow” drop down and enter the following formula:

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

Finally, click OK for a drop-down list with the product’s name.

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

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

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