Apply color-scale conditional formatting so that each row is formatted independently

Question

I struggle to apply my desired formatting to a spread sheet. A small example can be found here.

I have many rows of numbers, and I want to conditionally format each row by the same format, but each row independently. But when I fill down the first row, the conditional format's range gets expanded to the whole range, and the colors are not row-based anymore.

How can I row-based conditionally format my rows and upscale this to a few hundred rows?

Manually entering is not an option.

(Paste special-> conditional formatting only will result in the same as the fill down.)

I don't think there is an easy way, if you are willing to discretize the color range you could make range based formulae using one relative row and all columns in absolute mode. — Robin Gertenbach

pnuts pnuts · Accepted Answer · 2018-01-28T03:37:29

With Manually entering is not an option. something like GAS seems your only choice, despite Q not being tagged that way (and [conditional-formatting] does not apply to Google Sheets).

For others with a similar requirement but less restrictive for choice, the result can be achieved with selecting the desired range (F4 to J28 in the example sheet) and a simple rule for each of five colours, of the style:

=F4=large($F4:$J4,1)

but with the second parameter increased by 1 for each successive rule.

At the limits of each row even simpler:

=F4=min($F4:$J4)

and

=F4=max($F4:$J4)

Apply color-scale conditional formatting so that each row is formatted independently

1 Answers