How to Highlight Row Using Conditional Formatting (9 Methods)

When you are working with a large database you may want to highlight some specific rows based on other cells or values to identify them quickly. In such a case, you can highlight rows using conditional formatting. Conditional formatting is a fascinating way to highlight a row and reduce your workloads and it can improve your efficiency. Today in this article, we will demonstrate how to highlight rows using conditional formatting in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


9 Suitable Methods to Highlight Row Using Conditional Formatting

Think of a situation where you are given the Name, Department, Subject, Marks, and Grade of some students. Now you have to highlight some rows based on their names, departments, or grades using conditional formatting. In this section, we will demonstrate 9 different ways to do that.

Highlight Row Based on a Single Text Using Conditional Formatting


1. Highlight Row Based on a Single Text

Highlighting rows based on a single text is one of the basic methods of conditional formatting. Suppose we have to highlight rows that have the Arts department. We will follow the steps below to learn.

Highlight Row Based on a Single Text Using Conditional Formatting

Step 1:

  • Select the entire dataset. In your Home Tab, go to Conditional Formatting in the Style Group. Click on it to open available options and from them click on New Rule.

Home → Conditional Formatting → New Rule

Highlight Row Based on a Single Text Using Conditional Formatting

  • A new window opens. Select Use a Formula to Determine the Cells to Format to continue.

Highlight Row Based on a Single Text Using Conditional Formatting

Step 2:

  • In the formula section, insert this formula.
=$C4="Arts"
  • This formula will compare the dataset cells with the name Arts. When the value will match, it will highlight the row.

Highlight Row Based on a Single Text Using Conditional Formatting

Step 3:

  • We need to format the matched cells. The format section will help you out. We have chosen the color of the text Automatic. The fill cells option will help you to color the rows with a specific color. Choose any color you like to go.

Highlight Row Based on a Single Text Using Conditional Formatting

  • Now that we have completed all the actions, click OK to get the result.

Highlight Row Based on a Single Text Using Conditional Formatting

  • We have highlighted our rows based on a text value in a cell using conditional formatting.

Highlight Row Based on a Single Text Using Conditional Formatting

Read more: How to Change a Row Color Based on a Text Value in a Cell in Excel


2. Highlight Row Using Different Color Based on Multiple Texts

Following the same instructions as discussed in the previous method, we can highlight rows based on multiple conditions. Consider a case where you have to highlight the rows that have the Chemistry subject and Science department in them. Follow the steps below to learn this technique.

Highlight Row Using Different color Based on Multiple Texts Using Conditional Formatting

Step 1:

  • Go to the New Formatting Window following these steps.

Home → Conditional Formatting → New Rule

  • Select Use a Formula to Determine the Cells to Format.
  • Write down the formula to specify the cells that contain the Chemistry The Formula is,
=$D4="Chemistry"

Highlight Row Using Different color Based on Multiple Texts Using Conditional Formatting

  • Select the color format for your matched cells. Click OK to continue

Highlight Row Using Different color Based on Multiple Texts Using Conditional Formatting

  • The conditional formatting features successfully highlight the rows.

Highlight Row Using Different color Based on Multiple Texts Using Conditional Formatting

Step 2:

  • Now we need to highlight the rows that contain the Science department in them. For that, go to

Home → Conditional Formatting → Manage Rules

Highlight Row Using Different color Based on Multiple Texts Using Conditional Formatting

  • The Conditional Formatting Rules Manager window appears. Click New Rule to add another one.

Highlight Row Using Different color Based on Multiple Texts Using Conditional Formatting

Step 3:

  • Set the formula for the second condition. Write down the formula in the formula box.
=$C4="Science"
  • Set the format and you are good to go.

Highlight Row Using Different color Based on Multiple Texts Using Conditional Formatting

  • Finally, click OK to highlight row based on multiple conditions using conditional formatting

Highlight Row Using Different color Based on Multiple Texts Using Conditional Formatting

  • The result is here.

Highlight Row Using Different color Based on Multiple Texts Using Conditional Formatting

Read more: How to Do Conditional Formatting for Multiple Conditions


3. Highlight Row with Conditional Formatting Based on a Number value

We can highlight a row using conditional formatting based on numbers too. In this given situation, we have to highlight the rows with a mark equal to 90.

Highlight Row Based on a Number value Using Conditional Formatting

Step 1:

  • Insert the formula in the formula box of the New Formatting Rule
=$E4=90
  • Specify the formatting and click OK to continue.

Highlight Row Based on a Number value Using Conditional Formatting

  • Our job here is done.

Highlight Row Based on a Number value Using Conditional Formatting

 

Step 2:

  • Using the same procedures, you can also apply the greater than or the less than conditions. For highlighting row having a number greater than 90, the formula is,
=$E4>90
  • Confirm the format and click OK.

Highlight Row Based on a Number value Using Conditional Formatting

  • We have got the result. In the same way, you can find the number less than 90.

Highlight Row Based on a Number value Using Conditional Formatting


4. Apply The OR Function to Highlight Row

You can use The OR Function to apply conditional formatting. We want to highlight the Commerce department or Philosophy subject using the OR function. Insert those texts into your reference table.

Apply The OR Function to Highlight Row Using Conditional Formatting

Step 1:

  • Write down the OR formula in the formula box. The Formula is,
=OR($C4="Commerce",$D4="Philosophy")
  • The OR formula will compare the cell values with Commerce and Philosophy and then it will color the rows that matched the conditions.

Apply The OR Function to Highlight Row Using Conditional Formatting

Step 2:

  • Select a formatting style according to your preferences.
  • Click OK and your job is done.

Apply The OR Function

Read more: Excel Conditional Formatting Formula with IF


5. Insert The AND Function to Highlight Row with Conditional Formatting

The AND Function also helps you to highlight rows using conditional formatting. Here we will apply a new condition. We will change row colors that have both Physics and A+ grades in them.

Insert The AND Function

Step 1:

  • Following the same procedures discussed above, go to the New Formatting Rule window and apply the AND The formula is,
=AND($D$4="Physics",$F4="A+")
  • Set the formatting styles and click OK to format the cells.

Insert The AND Function

  • We have successfully highlighted the rows according to the conditions.

Insert The AND Function to Highlight Row Using Conditional Formatting


Similar Readings:


6. Use The SEARCH Function to Highlight Row

You can Use The SEARCH Function to find and highlight any specific rows in your dataset using conditional formatting. To do this, insert a name that you want to find in the database.

Use The SEARCH Function to Highlight Row Using Conditional Formatting

Step 1:

  • Apply the SEARCH function to find Marketing. The formula is,
=SEARCH($H$4,$D4)>0
  • Click OK to continue.

Use The SEARCH Function to Highlight Row Using Conditional Formatting

  • See, we have highlighted the rows that contain the subject Marketing.

Use The SEARCH Function to Highlight Row Using Conditional Formatting

Read more: Conditional Formatting on Multiple Rows Independently in Excel


7. Highlight Empty and Non-Empty Row with Conditional Formatting

Sometimes you have empty rows in your database that you want to highlight. You can do it easily using conditional formatting.

Empty and Non-Empty Cells

Step 1:

  • Open the New Formatting Rule window and select Format Only the Cells that Contain
  • Select Blank from the options

Empty and Non-Empty Cells

  • Set the Formatting and click OK to continue

Empty and Non-Empty Cells

  • The blank cells are now highlighted with color.

Empty and Non-Empty Cells

Read more: Conditional Formatting for Blank Cells in Excel


8. Conditional Formatting to Highlight Row with Above and Below Average Values

Step 1:

  • In order to find above or below average values from your dataset, apply this formula,
=$E4<AVERAGE($E$4:$E$13)

Above and Below Average Values

  • OK to get the result. That’s how you can find below or above-average values.

Above and Below Average Values

Read more: How to Highlight Highest Value in Excel


9. Highlight Rows with Duplicate Value Using Conditional Formatting

In some cases, you need to highlight rows with duplicate values in them. The COUNTIF Function can help you in this situation. Follow these instructions to learn this method.

Step 1:

  • Apply this formula in the formula box.
=COUNTIF($E$4:$E$13,$E4)>1
  • Here $E$4:$E$13 is the range and $E4 is the criteria. If the function finds a value of more than once, it will highlight the row.

Duplicate Value

  • OK to get the result. This result here is achieved with the first occurrence.

Duplicate Value

Step 2:

  • We can also highlight duplicate rows without the first occurrence. For that, the formula is,
=COUNTIF($E$4:$E4,$E4)>1
  • Set the format and get the result by clicking OK.

Duplicate Value

  • The final result is here.

Duplicate Value


Things to Remember

👉 When you want to find out case sensitive name, you can use the FIND function instead of the SEARCH function

👉 Use the Absolute Cell references ($) to block the cells.


Conclusion

We have discussed nine suitable ways to highlight rows using conditional formatting in excel. You are most welcome to comment if you have any questions or queries. You can also check out our other articles related to the Excel tasks!


Similar Articles to Explore

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo