Formatting Excel to Bold Top Five Values

[littlepitz]

How do I make excel tell me what the top five cell values are? I would like them to be bold and stand out, without me having to look for them.

[littlepitz]

Please find an answer soon, I know you people are looking at my problem, plase reply if you have ANY tips for me.

[Asala42]

Hey,

You could use Conditional formatting to do this. Select your range, then go to Format/Conditional Formatting/formula:

=RANK($B2,$B$2:$B$20)<=5

(where B2:B20 is your range of data).

Select whatever format you'd like

Hope that helps,
Adam

[littlepitz]

Thanks for your help, but I am still having trouble. I need the formating to go over a bunch of different ranges, I have tried to seperate them with a comma, but it continues to tell me that I have an error. It worked within one range, but just not over a few different ranges.

[Nimrod]

Please give some details on the ranges :
I.E. give some range examples and if you want the top five for each range or the top five for all ranges combined

[littlepitz]

This is what I am plugging into the conditional formatting applet:

=RANK($L$3:$L$10,$L$12:$L$21,$L$23:$L$27,$L$33:$L$40,$L$42:$L$51,$L$57:$L$69,$L$71:$L$86)<=5

[Jack in the UK]

Ranking non-contiguous rows of data

http://www.mrexcel.com/board/viewtop...8797&forum=2&4

[littlepitz]

Thanks, but thats a little to indepth for me. Basically I have a spread sheet that has each of my agents listed with the number of transfers for each agent and I have team totals seperating each total, so I want the top five numbers excluding the team totals.

[Chris Davison]

Hi Littlepitz,

sometimes we *have* to go indepth !

Only because I haven't had time to fully digest the subtelties of Yogi's and Aladin's suggestions on that earlier thread yet, the logic of my suggestion should still hold firm in your case :

1) name your range
2) conditional format using this range

simple, eh ?

so :

1) Name your range :

highlight the following cells - L3:L10,L12:L21,L23:L27,L33:L40,L42:L51,L57:L69,L71:L86

(hold down CNTRL as you click on them, it'll remember where they are non-contiguous)

goto insert, names, define

define a name (in my example I called it "pitz")

the highlighted range should already show up as the "refers to" range

click on add, then OK out

you should now have a named range called "pitz" - double check by going to edit, goto and then type "pitz" in the reference box, or click on "pitz" if it already appears...... when you hit OK it should highlight that range of non-contiguous cells

now select cell L3 and apply your conditional formatting :

format
conditional formatting
change "cell value is" to "formula is"
enter the formula : =RANK(L3,pitz,0)<=5
format
font
font style
bold
ok
ok

you've just told it to bold L3 if it ranks 5th or higher (ie 4th, 2nd etc) within the range called "pitz" (which you defined earlier)

now, because you assigned this formatting to L3 without using any absolute anchoring (those $$ signs), when you paste this formatting to another cell, it will adjust it relatively....

so, we use the format painter icon (the paintbrush) to paste this format in L3 onto the whole column of "L"....

click L3.... click on the paintbrush, click on the column header "L" to apply it to the whole row

Your top five values should now be bolded...