How to Highlight Entire Row with Conditional Formatting in Excel

In this article, we will learn to highlight an entire row with conditional formatting in Excel. In Excel, Conditional Formatting helps users to highlight columns, rows, or cells based on different conditions. Today, we will demonstrate 7 examples. Using these examples, you can easily highlight an entire row with conditional formatting in Excel. So, without further delay, let’s start the discussion.


Download Practice Workbook

You can download the practice book from here.


7 Ideal Examples to Highlight Entire Row with Conditional Formatting in Excel

To explain the examples, we will use a dataset that contains information about the Sales Amount, and Performance of some Sellers. We will use Conditional Formatting based on different criteria and highlight an entire row inside the dataset. You can apply Conditional Formatting based on text, number, multiple criteria, cell value, and many more things. In the following examples, we will show the steps and describe the formula you need to apply Conditional Formatting correctly.

how to highlight entire row in excel with conditional formatting


1. Highlight Entire Row Based on Text Criteria with Conditional Formatting

In the first example, we will show the process of highlighting an entire row based on text criteria with conditional formatting. The steps of this example are simple. Here, we will highlight the entire corresponding row that contains the text Average in Column D. Let’s follow the steps below to learn more about the example.

STEPS:

  • In the first place, select the whole dataset without the headers. Here, we have selected the range B5:D11.

Highlight Entire Row Based on Text Criteria with Conditional Formatting

  • Secondly, go to the Home tab and select Conditional Formatting. A drop-down menu will appear.
  • Select New Rule from there. It will open the New Formatting Rule window.

Highlight Entire Row Based on Text Criteria with Conditional Formatting

  • Thirdly, select ‘Use a formula to determine which cells to format’ from the Select a Rule Type section in the New Formatting Rule box.
  • After that, click inside the ‘Format Values where this formula is true’ box and type the formula below:
=$D5="Average"
  • Now, click on the Format It will open the Format Cells window.

Highlight Entire Row Based on Text Criteria with Conditional Formatting

This formula checks each cell in the range B5:D11 if it contains the text Average. We have used a dollar ($) sign to lock Column D. But the rows are not locked. That’s why if a cell inside Column D contains the word Average, then Excel will highlight the entire corresponding row.

  • In the Format Cells window, click on the Fill tab and choose a color from the Background Color section.
  • Then, click OK to proceed.

  • Finally, you will see Excel has highlighted the rows that contain the word Average in Column D.

Highlight Entire Row Based on Text Criteria with Conditional Formatting

Read More: Highlight Row If Cell Contains Any Text


2. Use Number Criteria in Excel Conditional Formatting to Highlight Entire Row

In the second example, we will number criteria to highlight an entire row in Excel with conditional formatting. Here, we will highlight the corresponding entire row if a cell contains a sales amount greater than $3000. We will use the previous dataset and the same steps as the previous example. Let’s pay attention to the steps below to learn how we can highlight an entire row based on the number criteria.

STEPS:

  • Firstly, select the range B5:D11.

Use Number Criteria in Excel Conditional Formatting to Highlight Entire Row

  • In the second step, select Home >> Conditional Formatting >> New Rule.
  • After that, select ‘Use a formula to determine which cells to format’ and type the formula below:
=$C5>=3000
  • After typing the formula, click on the Format option.

Use Number Criteria in Excel Conditional Formatting to Highlight Entire Row

  • In the Format Cells window, select a color and click OK to proceed.

Use Number Criteria in Excel Conditional Formatting to Highlight Entire Row

  • As a result, the rows with a sales amount greater than $3000 are highlighted.

Use Number Criteria in Excel Conditional Formatting to Highlight Entire Row

Read More: How to Highlight Row Using Conditional Formatting (9 Methods)


3. Insert Multiple Criteria to Highlight Entire Row in Excel with Conditional Formatting

In the previous examples, we used a single criterion in each example. We can also insert multiple criteria to highlight an entire row with conditional formatting. For that purpose, we can use the AND / OR function. Here, we will highlight an entire row if the sales amount is greater than $1200 and the performance is Poor. In order to apply Conditional Formatting, both conditions must be satisfied. Let’s follow the steps below to see how we can use multiple conditions.

STEPS:

  • In the first place, select the range B5:D11.

Insert Multiple Criteria to Highlight Entire Row in Excel with Conditional Formatting

  • After that, go to the Home tab and select Conditional Formatting >> New Rule.
  • The New Formatting Rule window will appear.
  • In the New Formatting Rule box, select ‘Use a formula’ option and type the formula below:
=AND($C5>1200,$D5="Poor")
  • After typing the formula, select Format.

Insert Multiple Criteria to Highlight Entire Row in Excel with Conditional Formatting

  • Now, go to the Fill tab in the Format Cells box and choose a color for highlighting the row.
  • Click OK to proceed.

Insert Multiple Criteria to Highlight Entire Row in Excel with Conditional Formatting

  • Finally, you will see results like the picture below.

Insert Multiple Criteria to Highlight Entire Row in Excel with Conditional Formatting

Read More: How to Highlight Every Other Row in Excel


4. Apply Conditional Formatting to Highlight Multiple Rows with Different Colors in Excel

Conditional Formatting has many features and users can perform different operations easily using it. In the previous example, we highlighted the rows with the same color. But in this example, we will use different colors to highlight multiple rows. Again, we will use the same dataset. If the performance of a seller is Good, then the color of the entire row will be green. If the performance is Average, then the entire row color will be yellow. Otherwise, it will be red.

STEPS:

  • Firstly, select the range B5:D11.
  • Secondly, select Home >> Conditional Formatting >> New Rule.
  • In the New Formatting Rule box, select ‘Use a formula’ and type the formula below:
=$D5="Good"
  • Now, click on the Format option.

Apply Conditional Formatting to Highlight Multiple Rows with Different Colors in Excel

  • After that, go to the Fill tab in the Format Cells box and select the Green color.
  • Click OK to move forward.

Apply Conditional Formatting to Highlight Multiple Rows with Different Colors in Excel

  • Repeat the same steps again and type the formula below in the New Formatting Rule box:
=$D5="Average"
  • Now, click on the Format option.

  • In the Format Cell box, navigate to the Fill tab and select Yellow color this time.
  • Click OK to proceed.

  • After clicking OK, repeat the steps again.
  • In the New Formatting Rule box, type the formula below this time:
=$D5="Poor"
  • Then, select Format.

  • At this moment, select the Fill tab in the Format Cells box and choose Red color.
  • Click OK to move forward.

  • After completing all the steps successfully, you will see conditional formatting with different colors like the picture below.

Apply Conditional Formatting to Highlight Multiple Rows with Different Colors in Excel

Read More: How to Highlight a Row in Excel (2 Effective Ways)


5. Highlight Entire Row If Cell Is Blank with Excel Conditional Formatting

We can also use Conditional Formatting to highlight an entire row if it contains any blank cells. To do so, we need to use the COUNTIF function in the New Formatting Rule box. Let’s observe the steps below to see how we can implement a formula to highlight an entire row if it stores any blank cells.

STEPS:

  • In the beginning, select the range B5:D11.

Highlight Entire Row If Cell Is Blank with Excel Conditional Formatting

  • Secondly, select Home >> Conditional Formatting >> New Rule.
  • In the New Formatting Rule box, click on the ‘Use a formula’ option and type the formula below:
=COUNTIF($B2:$D2)>0
  • After typing the formula, click on the Format option.

Highlight Entire Row If Cell Is Blank with Excel Conditional Formatting

  • In the Format Cells window, go to the Fill tab, select a color and then click OK to proceed.

  • As a result, you will see Excel has highlighted the corresponding rows with blank cells.

Highlight Entire Row If Cell Is Blank with Excel Conditional Formatting

Read More: How to Highlight Row If Cell Is Not Blank (4 Methods)


6. Use Conditional Formatting to Highlight Entire Row Based on Drop-Down Values

In this example, we will use conditional formatting to highlight an entire row based on the drop-down values. You can see the drop-down values in Cell F5 in the dataset below. Here, if we change the value of Cell F5 using the drop-down list, then, Excel will dynamically highlight the corresponding row. To learn how to create a drop-down list, you can visit this link.

Use Conditional Formatting to Highlight Entire Row Based on Drop-Down Values

Let’s pay attention to the steps below to see how we can highlight an entire row based on the drop-down values.

STEPS:

  • Firstly, select the range B5:D11.

  • In the second step, click on the Home tab.
  • Then, select Conditional Formatting. A drop-down menu will appear.
  • Select New Rule from there. It will open the New Formatting Rule window.
  • In the New Formatting Rule box, click on the ‘Use a formula’ option and type the formula below:
=$B5=$F$5
  • After typing the formula, click on the Format option.

Use Conditional Formatting to Highlight Entire Row Based on Drop-Down Values

  • At this moment, select the Fill tab in the Format Cells box and choose a color to highlight an entire row.
  • Click OK to move forward.

  • As a result, you will see Excel has highlighted the corresponding row.

  • Now, if you change the value of Cell F5 from the drop-down list, the corresponding entire row will be highlighted.

Use Conditional Formatting to Highlight Entire Row Based on Drop-Down Values


7. Highlight Entire Rows Based on Separate Cell Value

This example is very much similar to the previous one. Here, we will highlight an entire row based on separate cell values. Suppose, we have a dataset like the picture below. In the dataset, Cell F13 will contain a text or value. Excel will highlight the corresponding row based on that value. In the previous example, we did the same thing from a drop-down list. But, we will change the values manually this time.

Highlight Entire Rows Based on Separate Cell Value with Conditional Formatting

Let’s follow the steps below to see how we can implement this example.

STEPS:

  • First of all, select the range B5:D11.

  • Secondly, select Home >> Conditional Formatting >> New Rule.
  • Thirdly, click on the ‘Use a formula’ option in the New Formatting Rule box and type the formula below:
=$D5=$C$13
  • After typing the formula, select Format.

Highlight Entire Rows Based on Separate Cell Value with Conditional Formatting

  • In the Format Cell box, navigate to the Fill tab and select a color from the Background Color section.
  • Click OK to proceed.

  • In the following step, type Good in Cell C13 and press Enter.
  • Instantly, Excel will highlight the rows with the word “Good”.

  • Finally, if you type another value in Cell C13, then the corresponding entire row will be highlighted.

Highlight Entire Rows Based on Separate Cell Value with Conditional Formatting


Conclusion

In this article, we have 7 ideal examples of Excel Conditional Formatting to Highlight Entire Row. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo