How to highlight whole row using Conditional Formatting

Conditional formatting is very useful tool for highlighting cells in MS Excel. But there is the problem with coloring whole row or whole column of table. This example shows how to do that.

Example

In this table, there is a list of inspections. The task is to color red the rows that contains errors (in the last column is “yes”).

Step 1 / New Formatting Rule

Select the first row of the table. Go to the Home >> Conditional Formatting >> New Rule… >> Select the choice Use a formula to determine which cells to format >> write the formula.

Formula for formatting row is =$D2=”yes”. The dollar sign ($) in the formula has to be only before the column (letter D). Reason is simple: We will copy the formatting and we want to vary the row number in the formula. So, there is no dollar sign ($) before the row number.

Step 2 / Copy formatting

You have two choices how to copy conditional formatting from the first row.

Use Format Painter

Select the first row >> go to the Home >> check the Format Painter >> select the rest of the table.

Use Auto fill

Select the first row >> Use the right bottom corner of the selection to copy values down. Don’t be afraid that values change, we fix it now. >> Click on the Auto Fill Options >> select Fill Formatting Only