Updated date:

How to Highlight Entire Rows Based on a Cell Value in Excel

Author:

Conditional Formatting is a feature of excel that allows you to apply formatting to data cells based on specific conditions.

In specific conditions, we might need to apply formatting to entire rows, based on a value in one cell. In this article, I will discuss how to do that in simple steps.

An example of this scenario is shown below. I want to highlight all the rows in red color which have the status “UNBILLED” in column D.

how-to-highlight-entire-rows-based-on-a-cell-value-in-excel

Essentially I want to highlight rows 4, 5, 9 and 12. Now in a small dataset, it is very easy and all you have to do is manually select the row and highlight the cell. But what if the data rows increase, or it is a very large dataset. It will become a very tedious task, and there is a big potential for making a mistake. This is where the conditional formatting comes in handy.

You can download the link below to follow along the tutorial.

Highlighting Rows Steps

Say you have a dataset where you want to highlight the rows which show an “UNBILLED” Status in column D.

Here is how you can do it:

how-to-highlight-entire-rows-based-on-a-cell-value-in-excel

Step 1.

Select the entire data set. For this example select the cells A1 to D13.

how-to-highlight-entire-rows-based-on-a-cell-value-in-excel

Step 2.

On the ‘Home’ Tab, under the ‘Styles’ group, you will find an icon for ‘Conditional Formatting’. Click on that.

how-to-highlight-entire-rows-based-on-a-cell-value-in-excel
how-to-highlight-entire-rows-based-on-a-cell-value-in-excel

Step 3.

On clicking the icon, you will find an option for ‘New Rule’. Click on that.

how-to-highlight-entire-rows-based-on-a-cell-value-in-excel

Step 4.

In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.

how-to-highlight-entire-rows-based-on-a-cell-value-in-excel

Step 5.

In the ‘Formula’ field, enter the following formula: =$D2=”UNBILLED”

how-to-highlight-entire-rows-based-on-a-cell-value-in-excel

Step 6.

After that click on the ‘Format’ button.

Step 7.

Under the ‘Fill’ section in the dialog box, choose the color that you want to use to highlight the rows.

Step 8.

Finally, click on OK.

how-to-highlight-entire-rows-based-on-a-cell-value-in-excel
how-to-highlight-entire-rows-based-on-a-cell-value-in-excel

Doing this will highlight all the rows where the Status of the supermarket is “UNBILLED”’.

Working of the Formula

The feature of Conditional Formatting checks the condition (=$D2=”UNBILLED”) that we have stated for each cell in the dataset.

If the cells in the column D checkers for the word UNBILLED, the row will get highlighted, else it will not. The dollar sign ($) ensures that the column in the formula remains constant. It will check for the condition only in column D.

© 2020 PGupta0919

Related Articles