How to use conditional formatting in excel to hightlight entire row

helpfinderIT Consultant
Many times I meet people working with Excel´s conditional formatting but they get stuck for some reason. One kind of problem I see many times is "How can I hightlight entire row based on single cell value"? So I will try to explain this with a short example and hopefully it will help somebody.

Let's look at my example, there is a table with people, department they belong and their sales for certain period of time.
The goal is to highlight the whole table row for person with sales higher than 1000€.
People most often do this - insert conditional formatting which change cell formatting for the cell, but they want row. It looks like this (also with Conditional formatting definition)

So here is the part when your phone is ringing, or your email inbox receives helpdesk notification. And the solution is simple.
To cover user´s needs you have to use this kind of Conditional formatting - select data in table you want to be affected by formatting if criteria you define match

Then on Home tab choose Conditional formatting>New rule>Use a formula to determine which cells to format

Now you have to define the formula and tell excel how to format cells if this formula is true. In this case we are checking values in fourth column (which represents sales), so it is D column (D as fourth letter in alphabet) and mark the table row to green if sale is higher than 1000€. Now, the formula is quite simple, as every excel formula it beginns with the "=" sign, =D2>1000 (because we are looking in D column and looking for sales higher than 1000€). But, oops, the result looks like this, there is a problem somewhere

The problem is we put just D2, we need to apply also $ into formula to fix part of it. Correct formula should looks like =$D2>1000 which fixes D column but leaves row flexible. After this change result looks like this

That´s it. Quite simple task, quite simple solution.

- helpfinder -

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.