0

Is there a way to use one single color scale conditional formatting rule for the whole sheet, that will separately compare only the values from each independent individual row? It seems that this can be accomplished for columns but not rows.

Here is a super scaled down version of what I'm trying to accomplish but with a separate rule for each row... https://docs.google.com/spreadsheets/d/1FxM4YBoYIvsXVMJAQLmWldRG5CL0R-sxAQybOBCtVcY/edit?usp=sharing

Also, is there a way to have the color scale highlight for the lowest value color when there is only one value? Right now, if there's only one value it uses the highest value color. See my crude "scaler" fix in the sheet above.

Thank you for any help!

Improve this question
6
  • 1
    It cannot be done with the CF color scale option, because that option does not allow for custom formulas; it's all or nothing. However, this CAN be done by setting up a simulated color scale using different rules, each of which will apply to the entire range: one for "N/A"; one for MIN; one for MAX; and then one for each of however many degrees you want between (e.g., five colors covering <20%, <40%, <60%, <80%, <100% and should be plenty). There is no need for the "scaler fix"; formulas can handle that if set up correctly. I hope that steers you in the right direction.
    – Erik Tyler
    Commented Oct 7, 2021 at 0:38
  • 1
    Does this answer your question? Conditional formatting with color scale for many rows independently once
    – Tedinoz
    Commented Oct 8, 2021 at 0:48
  • @Tedinoz Thank you. Unfortunately, it looks like that's not a real-time solution without running a script after each change.
    – Computer Candy
    Commented Oct 13, 2021 at 18:58
  • 1
    Great. I'm glad the suggested approach made sense and that you were able to get to goal with it.
    – Erik Tyler
    Commented Oct 13, 2021 at 20:51
  • 1
    @ComputerCandy Congrats on finding a solution. May I encourage you to post an answer to your own question. This will benefit others who have the same problem.
    – Tedinoz
    Commented Oct 14, 2021 at 4:56

1 Answer 1

Reset to default
1

In case anyone is looking to do something similar, to get around having to do one color scaling conditional formatting rule per row, I followed Erik Tyler's advice and used several different rules for the whole sheet - one rule per color variation.

One custom formula rule each for the MIN and MAX values:

=AND( (C3:AA3=MIN($C3:$AA3)), (ISNUMBER($C3:$AA3)) )
=AND( (C3:AA3=MAX($C3:$AA3)), (ISNUMBER($C3:$AA3)) )

And one custom formula rule each for every desired percentage gap of the range:

=AND( (PERCENTRANK($C3:$AA3,$C3:$AA3)<40%), (ISNUMBER($C3:$AA3)) )
=AND( (PERCENTRANK($C3:$AA3,$C3:$AA3)<80%), (ISNUMBER($C3:$AA3)) )

ISNUMBER is used to ignore cells that do not contain numbers.

You can create as many rules as you wish. Outside of the MIN/MAX rules, I created five (two examples above). I'll keep the sample sheet up for a while for anyone that wants to take a closer look.

The added benefit to this solution is that you can add more formatting variations than just background color. For example, I set the lowest values to show in bold text.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.