Find main customers in a list

[jaygee250]

Hello

I have many products and many customers which I would like to display in a report. But I would like to highlight the 3 main customers. maybe even in a different color (like main customer dark green, second best in medium green, and third best in light green)
here is an example of the list:

Customer Value
A 4
B 40
C 20
D 100
E 30
F 8

I know i can do it with sorting or filterin. But I want to keep the list as it is.
so in this case the Customer D would be dark green, Customer B medium und Customer E light green.
How is this possible?!?

Thanks in advance.

[teylyn]

Hi,

select all your numbers, then click Format - Conditional formatting, select Formula is and enter this formula

=RANK(B1,$B$1:$B$6)=1

Change the $B$6 to reflect the last row in you data column. Select a format for the first place and hit OK
then click Add and add the second rule for rank 2

=RANK(B1,$B$1:$B$6)=2

select a format for rank 2

rinse and repeat for rank 3

Upgrade to Excel 2007 if you want to highlight more than three. Sorry

hth

[jaygee250]

thank you for your fast answer.
But somehow its not working.
What is that B1 in that formular ?
=RANK(B1,$B$1:$B$6)=1

[teylyn]

Well, I simply took the data you supplied and put it into an empty sheet, customer in column A, value in column B, starting in row 1, then

=RANK(B1,$B$1:$B$6)=1

is the formula applied to cell B1. Rank finds out which place the number in B1 has, compared to the numbers in B1 to B6. The result is then compared with 1.

You need to adjust the cell addresses to your values, of course. If your values are in Column D, start in row 5 and go through to row 100, select D5 to D100 and for the first conditional format enter

=RANK(D5,$D$5:$D$100)=1

note that the first D5 does not have dollar signs, but the other cell addresses do.

check it out and get back to me. If it does not work, supply a small data sample with your layout ....

servus