How to prepare top ten list with amounts in excel?

You can use a combination of VLOOKUP and LARGE(array,k) function, where:

array : column or row of data you want to analyze.

k : the nth order of the item you want.

 

like for example, to get the4th largest number of a column use, large(array,4).

 

You can use k from1 to10 to obtain top ten values, and then use Vlookup to look up these values and correspond its equal from the table.

 OR YOU CAN SELECT ALL DATA TO MAKE ALIST THEN PRESS  CTRL+ SHIFT +L

 

AND SORT COLUMN OF MOUNTS DESCENDING

 

 THE FIRST TOP10 MOUNTS WILL BE THE TOP TEN

please click on the photo and see onther way FQ : 

I guess you can use DATA - FILTER.

If you have your list in column B in column A add the rank for this list use formula (RANK(B4,$B$4:$B$R) replace R by the last raw number in your list – the value in column B shouldn’t have any duplicated value-  then add in column C the serial from1 to10 then use the formula(VLOOKUP(C6,$A$4:$B$20,2,0)) in column D will give you the top10.

There's The Easy Way , Just Sort Them and Just Take The First Ten !

i appreciate all the answers . but all these ways comes only in the same sheet

i mentioned the way to how make this list in another sheet and without messing with the original data

i use this way in the compilicated files

:)

If you have your list in column B in column A add the rank for this list use formula (RANK(B4,$B$4:$B$R) replace R by the last raw number in your list

we can creating a scretch on the left corner after posting at least 3 amount then we can recieve the automatic result on it.

Easiest way is to make a conditional formating but this is in1 sheet. If you want for the full workbook I dont think there is a formula .. I would suggest doing it with VBA.