Conditional formatting independently for multiple rows

Question

Hello,

I have already googled for the answer but cannot find exactly what I need.

I have an excel spreadsheet with 4 columns with different numbers in each cell.

There are about 450 rows to the table.

For each row, I would like to apply the 2-colour scale conditional format to show which values are highest and which are lowest (and which come in between).

I then want to apply this format to all other rows.

The issue I have had is I can only get it to format a colour scale for the spreadsheet as a whole, rather than independently colour scale for each row.

Does anyone have any suggestions how to independently colour scale each row?

Thanks in advance.

Answer 1

Hi,

You want the largest1, largest2... per Row. So, from your last picture formula must be:

=C13=LARGE($C13:$F13,4)

Same goes for the other rules

=C13=LARGE($C13:$F13,3)

=C13=LARGE($C13:$F13,2)

=C13=LARGE($C13:$F13,1)

Answer 2

I agree that that is a better way to do it.

Answer 3

Thanks for that.

I've given it a go and I got the below result.  (I have worked on a small section of data to test it.)

What have a done wrong?

Answer 4

What have a done wrong?

Probably the formulas but you didn't expose them so not sure...

Answer 5

C13 is the first cell you see there (£495).

I then included the data in that selection.

I then added the respect numbers on the end '1', '2', '3', & '4' as indicated by the rules formula given.  Have a mis-interpreted something?