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 -