Excel Tips and Tricks: Tip 4 (Format a Row Based on Condition in a Single Cell)

Before reading this article, please go through the following articles:

1. Excel Tips and Tricks

2. Excel Tips and Tricks 2

3. Excel Tips and Tricks Tip 3 (Quickly Create Many Range Names)

In this tip I'll explain how to format a row instead of a cell based on a condition in a single cell using conditional formatting. Suppose you have a data table or simply say a collection of records and you want the records to be highlighted based on a condition in a particular cell. See Picture 1. You've a table in D3:G11. You need to highlight the record if it has "Dog" in the third data column.



Picture 1

And you want it to look as in Picture 2.



Picture 2

Use the following procedure to do that.

STEP 1

Select the target cell where the condition is to be checked. Here in the picture above our target column is the "Have Pet" column and you're free to select any cell with data in it. Let's start with Cell "F4", this is the very first target cell.

STEP 2

Select "Home" => "Conditional Formatting" => "New Rule". (Refer to Picture 3 below.)



Picture 3

STEP 3

In the "New Formatting Rule" dialog box, select the last option "Use a formula to determine which cells to format":.

Under "Format values where this formula is true" you must specify the condition. Since our target cell is "F4" and we must check for the value "Dog", the formula would be like this:

$F$4="Dog"



Picture 4

STEP 4

Click on "Format" in the "New Formatting Rule" dialog box and set the format that you want. I did it like this: Green background with white text in bold.



Picture 5

Now the trick here to make it happen is, make the cell reference relative to the rows, in other words:

"=$F4="Dog"

Did you guess the change? Yes, a dollar sign is removed after F to make the row reference relative. Click OK and see what happens. Oops… Only one cell is highlighted, not the row? Don't worry. Use the following procedure.



Picture 6

STEP 5

Select the cell having the condition i.e.: "F4", go to Home => "Conditional Formatting" => "Manage Rules".



Picture 7

This will open the Conditional Formatting Rules Manager dialog box. You'll see the defined condition in the list. Click on "Applies to" handle and select the entire table:



Picture 8



Picture 9

After you've selected the table, click on the selection handle again to get back to the Conditional Formatting Rules manager dialog box.

Click on "Apply" => OK and voila! You're done. You see all the rows having "Dog" in column "Have Pet" are formatted as per the given format.



Picture 10

You may want to change the values in the cell and observe the change in format.

Sample sheet is attached with this article, please see for any clarification.

Hope you enjoy reading this tip. Follow for more!!