How To Quickly Highlight Rows in a Dataset in Excel

Conditional Formatting enables you to apply formatting to a cell or range of cells that will highlight rows based on the values in them. You can highlight only the cells or you can highlight the entire rows based on the values contained in one cell like the one below.

In the sample above, all the rows with the customer name ‘William’ has been highlighted.

In this tutorial, we will learn how to highlight rows based on cell values using Conditional Formatting in Excel. This will done applying different criteria.

This Tutorial will cover the following topics:

How to Highlight Rows based on Text Criteria
How to Highlight Rows based on a Number Criteria
How to Highlight Rows based on a Multiple Criteria using AND/OR
How to Highlight Rows in Different Colours based on Multiple Conditions
How to Highlight Rows with any blank Cell
How to Highlight Rows based on Drop-Down Selection

#1. How to Highlight Rows Based on Text Criteria

To highlight the rows in a dataset based on a text, we will need to input the exact text in the formula. This is the keyword that will be used to search in your dataset.

So let’s say you have a dataset like this and suppose you want to highlight all the rows with the Customer Name ‘William’.

To do this, we need to follow these steps:

Select the dataset range that you wish to apply the Conditional Formatting. In this case, it is A6:I23.
Click on the Home -> Conditional Formatting
Click on ‘New Rule’ in the drop-down menu

4. In ‘New Formatting Rule’ dialog box, select ‘Use a formula to define which cells to format’.

5. And enter this formula: =$C6=“William”

6. Click the ‘Format’ button to select and set the colour you want the rows to be highlighted in.

7. Click Ok.

Now all the rows that have the Customer Name ‘William’ will be highlighted.

How this Works?……….

Conditional Formatting will check that each cell is using the condition that has been specified in the formula, that is =$C6=“William”. When it is analyzing each of the cells in row A6, it also checks if cell C6 has the name ‘William’ or not. So if it has the name, it will highlight the cell. And if it does not have the name, it will not highlight.

The trick here is using the dollar sign to lock the column ($C6) to always be C i.e where the start of the Customer name is. This way, the column is always locked to C, so even when it is checking cell A6 for the formula, it will check C6 and if it is checking A7 for the condition, it will still check C6.

#2. How to Highlight The Rows Based on a Number Criteria

In the example above, we looked at how to search for a particular name and highlight the entire row.

In this section, we will use the same method to check for numeric values and highlight the rows based on this condition.

Using the same dataset as shown in the first example, we want to highlight all rows having quantity greater than 50.

To do this, here are the steps to follow:

1. Select the dataset range that you wish to apply the Conditional Formatting. In this case it is A6:I23.

2. Click on the Home -> Conditional Formatting

3. Click on ‘New Rule’ in the drop-down menu

4. In ‘New Formatting Rule’ dialog box, select ‘Use a formula to define which cells to format’.

5. Then enter this formula in the formula field: =$F6>=50

6. Click the ‘Format’ button to open the dialog box and select and set the colour you want the rows to be highlighted in.

7. Click OK.

Here, all the rows with Quantity greater than or equal to 50 (>=50) has been highlighted.

Similarly, you can apply this formula using a criterion for the ‘Date’. So if for example, you want to highlight rows with date after 08/09/2020, you can use the date formula below:

=$A6>DATE(2020,9,8)

#3. How to Highlight Rows Based on Multiple Criteria

In this section, we want to look at how we can highlight an entire row in a dataset using multiple criteria.

For example, if you want to highlight all the rows where the Customer Name is ‘William’ and Quantity is more than 20 (>20), you will need to follow the steps below.Select the dataset range that you wish to apply the Conditional Formatting. In this case it is A6:I23.

1. Click on the Home -> Conditional Formatting

2. Click on ‘New Rule’ in the drop-down menu

3. In ‘New Formatting Rule’ dialog box, select ‘Use a formula to define which cells to format’.

4. Then enter this formula in the formula field:

=AND($C6=“William”,$F6>20)

6. Click the ‘Format’ button to open the dialog box and select and set the colour you want the rows to be highlighted in.

7. Click OK.

This is what you will get.

Here, you will see that only the rows that meet both conditions are highlighted and this is done using the AND formula.

Similarly, you can use the OR formula where you want to highlight rows that meet either conditions and not both. That is, either the Customer Name is ‘William’ or Quantity is greater than 20 (>20).

To do this, we will use this formula:

=OR($C6=“William”,$F6>20)

#4. How to Highlight Rows in Different Colours Based on Multiple Conditions

So what if you have multiple conditions and you want the rows to be highlighted in different colours according to their conditions?

Yes, this is also possible with Conditional Formatting in Excel.

In this section, we will look at the scenario where we have different conditions and we want to highlight the rows using different colours to differentiate the conditions.

So let’s say you want highlight all rows with Quantity greater than 50 (>=50) in orange and Quantity less than 50 (<50), but more than 40 (i.e >40<50).

To do this, you will need to create two separate conditional formatting rules and set the priority.

To do this, here are the steps to follow:

Select the dataset range that you wish to apply the Conditional Formatting. In this example, it is A6:I23.
Click on the Home -> Conditional Formatting
Click on ‘New Rule’ in the drop-down menu
In ‘New Formatting Rule’ dialog box, select ‘Use a formula to define which cells to format’.
Enter this formula in the formula field:

=$F6>40

6. Click the ‘Format’ button to open the dialog box and select colour orange to set the colour to orange.

7. Click OK.

8. Go to ‘Conditional Formatting Rules Manager’ dialog box, click on ‘New Rule’.

9. In ‘New Formatting Rule’ dialog box, select ‘Use a formula to define which cells to format’.

10. Enter this formula in the formula field:

=$F6>50

11. Click the ‘Format’ button to open the dialog box and set the colour to Green.

12. Click Ok.

13. Click on Apply or Ok.

This will highlight the rows having Quantity more than 50 (>50) in Green and rows less than 50 but greater than 40 (<50>40) in orange.

You can see that only one row is highlighted in orange which means that it is the only row that has quantity less than 50 but greater than 40.

It should be noted that:

When using multiple conditions, make sure you understand the order of the conditions and that it is correct.

In this example, the Green colour condition is higher than the Orange colour condition.

If it was the other way round, all the highlighted rows will be coloured in orange only. This is because a row where quantity is greater than 40 (i.e 45) satisfies both conditions (=$F6<50 and =$F6>40). And since Orange condition is at the top, it gets preference.

The order of the conditions can be changed using the Move Up/Down buttons.

#5. How to Highlight Rows With Any Blank Cell

In this section, we will see how to highlight the blank cells in a dataset.

If you are working with a large dataset, often times you will have some blank cells in the dataset. When you are working with very large dataset, you will need to check to know how many blank cells and where they are located.

You can also check for blank cells in Excel using conditional formatting. With Conditional formatting, you can easily highlight all the rows that have blank cells.

To do this, you will need to follow these steps:

1 . Select the dataset range that you wish to apply the Conditional Formatting. In this example, it is D6:I23.

2. Click on the Home tab -> Conditional Formatting

3. Click on ‘New Rule’ in the drop-down menu

4. In ‘New Formatting Rule’ dialog box, select ‘Use a formula to define which cells to format’.

5. Enter this formula in the formula field:

=COUNTIF($A6:$I6,””)>0

6. Click the ‘Format’ button to open the dialog box and set the colour to Green.

7. Click Ok.

This formula counts the number of blank cells in the dataset and highlights the entire row. If it returns a result that is higher than 0, then this means that there are blank cells in that row.

And if any empty cell is found, it will highlight the entire row.

In the previous examples, we had to specify all the conditions in the conditional formatting dialog box.

In this section, we will see how we can make it more dynamic so that the condition can be entered within a cell and based on what you entered, it will automatically highlight the rows like the one below:

In this example, we have to first create a drop-down box in cell A2 where we can select a name and it will highlight all the rows with the name that was selected.

To do this, we will follow these steps:

1 . Create a drop-down list in cell A2 using the customer names.

2 . Select the entire dataset. In this example, C2:K21