I have a table with values in it. I want to be able to immediately spot the cells with the biggest values. Using the color scale tool works nicely as demonstrated below

However, with my real data, there are a few cells that have huge values in it - these then raise the maxpoint so high that the formatting kind of breaks for the other values since in relation to the maxpoint, the other values are very small, making the colors among them almost indistinguishable.

I have obtained the second-highest value by doing =large(A1:C3,2) in an unrelated cell - however I seem to be unable to reference that cell in the Maxpoint setting. Is there a way?

Another idea was to manually set up a color scale that is more logarithmic in its curve but this really isn't a nice option I think. The only option that's left that I can think of is to dynamically color the cells via a script - is there already an easier or existing solution?

CodePudding user response：

try like this:

=LARGE($A$1:$C$3; 2)