Simple Excel Solutions: “Best of the Best” List!

Finance reporting is all about key indicators, trends, the best and the worst performers.

How to find Top 3, the Best and the Worst? Typically we use filters, sorting, pivot or conditional formatting. However, sometimes listing Top 3 without applying sorting or filtering is required.

As always, Excel offers very easy and great solution: LARGE and SMALL function. 

For instance, we need to find top 3 best sellers products in a monthly sales report. To find top 3 products, follow this simple two-step process: first, find highest value, second, find the name of the product with highest value.

Step 1: find Top 3 sales values.

Insert LARGE formula into cell G4= LARGE (sales range, top 1) -=LARGE($C$4:$C$10,ROWS($G$5:$G5)) (for simplicity you can replace ROWS with ranking values 1,2 or 3)

Copy Formula down to cells G5 and G6 to find next two largest sales in the range.

Step 2: find Top 3 product name matching to Top 3 sales amounts. Formula: =INDEX($B$4:$B$10,MATCH(G5,$C$4:$C$10,0),0)

Note:

Formula works well if data range does not have duplicate values. If there are duplicate values among Top list, then index/match function will show wrong results.

The solution is the array formula. In the sample below, there are duplicate sales values in top 5 list; G4 is an "array" formula. This requires Ctrl+Shift+Enter to finalize rather than just Enter. Once entered correctly, it can be copied or filled down until all products have been matched to their sales value. 

Hope you will find this solution useful.

Find the formula sample here: http://financial-reporting.ca/d/Large_Small_sample.xlsx

For more information visit very good MS Help article: