[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


How to use conditional formatting in excel to hightlight entire row

Published on
8,009 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

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Join & Write a Comment

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month