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.


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”).

Excel error table

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.

New Formatting Rule by formula

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.

how to use Format Painter

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

Auto fill options - Formatting Only

Add a comment »One comment to this article

  1. WOW, this is what I had looking for. Simple and easy explanation. Thanks.


Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress