In Google Sheets, when you use conditional formatting on a column with numeric values, is there a way to place a colored scale that is non-linear, but exponential, for example? I know you could manually format it, but that is quite a nuisance if you do that a lot.

In the below example (with German UI) there are some extreme values in the "I" column that make the scale go up very high. The average, however, is way below that and most values range from 0 to 200. Because of the extreme values, it is hard to differentiate between the smaller ones. An exponential colored scale would make much more sense here. But there doesn't seem to be an option to change the colored scale from being linear to exponential.

enter image description here

1 Answer 1


The first idea - if you code - it to write (or find/reuse/adapt) GAS script, for example (credits to https://stackoverflow.com/a/28513593/3359687)

The second idea - is to make another (helper) matrix (array) https://www.youtube.com/watch?v=mqf4WnTXm5g with recalculated non-linear values. Recalculate with formulas, custom functions or script.

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.