Finding Top 5 Performers in Excel

I recently came across with a problem asked by one of my friend.  He wanted me to create a function in Excel to know the Top 5 sales performer in his team.  I suggested him a easy way of sorting the data in descending order based on sales figures but he wanted to keep the data intact and know the result.

See the image below to visualize what he was looking for:

To provide him with an answer I used three Excel function namely:

• INDEX(array, row_number, column_number)
• MATCH(value, arrray, match_type)
• LARGE(array, nth_position)

Using these three powerful fucntions in conjunction, I came out with a solution and want to share it with you as well. The formula goes like this

E2: =INDEX($A$2:$A$12,MATCH(LARGE($B$2:$B$12,D2),$B$2:$B$12,0),1)

I dragged the formula till E6 and I got the result required, my Top 5 Sales Performers !

Now let me explain the formula's used here, basically LARGE function helps us in finding k-th largest value in our sales data.  Say fourth or fifth largest sales figure.

Using the value given by LARGE function, we are using MATCH function to know the relative position of the value in our sales figures.

Lastly INDEX function is giving us the name of the Sales Person. That's it !!

I hope you'll get benefitted by using this formula. "HAPPY LEARNING"

SAMPLE FILE

Filter Asterisk Character ("*")

Hello friends I hope you are keeping track of our Excel tips and learning new things everyday. Now it's time to get ready for another Excel tips. Today's post will be short and but very unique. Today we are going to learn, how to filter the asterisk character ("*"). Suppose you have a data which contains few asterisk character ("*") and you want to filter rows containing asterisk character ("*").

Now if you have been new to this situation you will simply say it's very easy. But mind it, It's not! There is a special technique to this and I'll take you step to step through it.

1. Apply filter to your complete data which contains asterisk ("*")

2. Click on filter drop down, Select Text Filters and than Contains...

3. It will open Custom AutoFilter pop-up box, here against the drop down with contains is another blank drop down. In this use Tilde ("~") before Asterisk ("*") and hit OK.

With this I wrap today's post, practise it and share it with everyone! "Happy Learning"