How to use conditional formatting in excel to hightlight entire row

Published on
7,944 Points
1 Endorsement
Last Modified:
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 -

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Join & Write a Comment

How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month