How do I achieve a conditional scale with with 4+ colours without VBA?
This is what I am trying to achieve:
The only way I know to achieve the above is by using two separate scales which is very awkward, inflexible, doesn't scale, etc etc. I am more than happy to use helper columns and/or a different "Rule Type" if it helps.
I need it because three colours are not enough for some of my data: the distribution/spread is such that only big differences are clearly highlighted. As a result, the colours applied to a pair of values are nearly the same, whereas I'd like to be able to see the difference between them more clearly. Please let me know if this is unclear and I will make a screenshot with an example.
I really cannot see why at least five colours are not natively supported, so I assume there is a simple logical rule for this. Or maybe there is a reason why perhaps it is just bad practice to use more than three colours in the first place? (Any such information would probably not be allowed as a standalone answer but hopefully it would in a comment? I'm still sometimes finding stack exchange rules tricky...)
I imagine the idea would be quite simple to implement. The default operation of a three colours scale is that excel applies the middle colour to the 50th percentile. Conceptually all I am asking is that two dropdowns are added:
- "1/4 point" between Minimum and Midpoint - this could then be set to the 25th percentile
- "3/4 point" between Midpoint and Maximum - this could then be set to the 75th percentile
Maybe there is a way to achieve this by applying some preliminary condition? - in my example at the top, only apply the red-yellow-green scale to numbers <2, then only apply the second scale to number >=2? But how do I achieve that?
Clearly in some cases I can come up with a "comparison indicator/parameter" which behaves in a specific manner (perhaps oscillates between two values?) and shows differences sufficiently with just three colours. But there must be a better way to do this.
A similar question was asked here but:
- it was a long seven years ago
- it didn't exclude VBA which is a substantial difference
Finally, this question was originally attempted as a "double" question (How to ... AND why ...) including a disclaimer that I had reservations about that format but felt that the two elements are closely related. It received a comment which supported those reservations so I edited the question substantially. Please feel free to view the edit history, perhaps if it helps put the question in context.
