How to duplicate (not extend) conditional formatting rule?

Question

I have a conditional formatting rule (color scale) applied to a row (e.g. A1:Z1). I want the to duplicate this rule for another row, but when I use Paste Special -> Paste Conditional Formatting Only (or Paint Format tool), it simply makes the color scale rule to apply to the sum of ranges (e.g. A1:Z2). The problem is that it won't process ranges separately, it will just join them into a single range and find the biggest / smallest number over the joint range, rather than in individual ranges.

The same applies if the range is defined in format "A1:Z1,A2:Z2".

What I want is just to avoid defining the same color scale rule for different rows manually.

Note that Google Sheets behaves here differently from MS Excel. In Excel I get the desired behaviour very easily and intuitively: I create a rule for a row, select it, copy, then paste special formatting only. For a scale from smallest red to biggest green, this is the output:

!Excel colour scale example]1

If I do the same steps in Google Sheets, the output is quite different:

It is clear that GS does not duplicate a rule, but simply adds a new range to the computed joint range the original rule applies to.

Is there a way in GS to do the same conditional rule duplication that Excel does, or I just have to re-create it manually?

Answer 1

1. Click any cell covered by the original conditional formatting
2. In the menu, pick format/conditional formatting...; this opens a side panel with all conditional formats that apply to that cell
3. Click the format you want to duplicate
4. Do not edit anything yet
5. Click "Add another rule" on the bottom. This will save any changes you made to your original format ... and open a new clone.
6. Change the range on the newly created clone.
7. Repeat 5 and 6 until you enter the last range
8. Click "Done".

Answer 2

I am not saying I LIKE this answer, but it is the only solution I can come up with:

Apply Custom Formatting using a Custom formula for each color value. In your example, this means 6 entries in the Custom Formatting. For each one, use the below formula. For the Highest value, use:

=A1=LARGE($A1:$G, 1)

For the second highest value, use:

=A1=LARGE($A1:$G, 2)

Increment the last number for each ranking, setting a color for each as well. I hope your range to Z does not mean you are color coordinating that many items. My concern there would be not just having that many conditional format items, but having that many colors be meaningful.

There is also the MAX() and MIN() functions if you just want the highest and lowest.

=A1=MAX($A1:$G)

or

=A1=MIN($A1:$G)

as well as MEDIAN() and SMALL() which is the opposite of LARGE(). In case you want the X largest and Smallest, or Maximum, Minimum, and Median.