CODEWITHSUNDEEP
excelconditional-formatting
Woody
Woody

Reputation: 145

Excel Highlight Entire Row by Column Vaule

I am trying to color an entire row in excel based on a column value, and have it conform to the conditional formatting similar to the one set out in this example. Coloring the individual column seems to be easily done, but I cannot figure out how to color the entire row based on an individual column. I have seen some posts that can do a singular color, but I haven't seen one based on the gradient.

enter image description here

Upvotes: 1

Views: 554

Answers (1)

h0r53
h0r53

Reputation: 3229

I could not find a way to color an entire row based on a dynamic gradient value. However, using a custom defined gradient something similar can be achieved.

We could hard-code thresholds to define when a row should become a certain color, but that isn't ideal for obvious reasons. Alternatively, we can use percentiles to help define when a row should change colors. The percentiles will be based on the Average column in this example (which is the D column).

Let's first discuss a custom gradient with the following percentile properties:

  • 0-20% Green
  • 20-40% Light Green
  • 40-60% Yellow
  • 60-80% Orange
  • 80-100% Read

We can now create rules to conditionally color rows with Averages within these percentiles.

To start with, I created a simplified version of the table you provided, including columns Threads through Median and rows 1 through 6.

Next, I created new rules under Excel's Conditional Formatting section. Make sure that the rules apply to all non-header cells in your sheet. Also make sure that Stop if true is checked so rules don't override one another.

For the rules themselves, you'll want to use the Classic style along with Use a formula to determine which cells to format options. Set Format with to Custom Format... and choose the appropriate color for each rule. We will be making one rule per color, so 5 total in this example.

For each rule, you can match with the following formulae:

Green: =$D2<PERCENTILE($D$2:$D$6,0.20)

Light Green: =$D2<PERCENTILE($D$2:$D$6,0.40)

Yellow: =$D2<PERCENTILE($D$2:$D$6,0.60)

Orange: =$D2<PERCENTILE($D$2:$D$6,0.80)

Red: =$D2<=PERCENTILE($D$2:$D$6,1.00)

Note that based on the above definitions all rows will clearly match red, which is why it is important to use the Stop if true rule option. Also important is that $D2 is a relative reference to the value of the Average column in each row, while $D$2:$D$6 is an absolute reference to all D rows 2 through 6. If you have more than 6 rows, you should adjust this accordingly so that the percentile is adjusted appropriately. From my testing it doesn't appear that including ranges beyond the actual amount of rows negatively affects the percentile generation, so there may be a more generic way to include all rows.

When you're finished, you should have something like the following:

Custom Conditional Formatting

If applied to all data cells in your spreadsheet, you should see the rules take effect:

Color All Rows

This is a solution that can almost certainly be optimized, but it should get you on the right track if you want to conditionally color entire rows based on the value of a column. It is also worth noting that technically percentiles are based on median values (in other words, the 50 percentile is the same as the median), so it may be more appropriate to reference the Median column than the Average column.

Upvotes: 2

Related Questions