MVP #42: How To Pick The Top 3 or Top Whatever From A List Using Formulas in Excel

As much as possible I try to limit my use of VBA/Macros. Macros require the user enabling the macro to work, doesn't work on tablet and phone versions of Excel (even on Windows RT) and can be broken when run on a Mac version of Excel. 

I find that it takes some fun creativity to do complex tasks purely with Excel formulas than building a macro. And there is the added advantage that formulas auto-calculate, out-of-the-box, and gracefully while macros have to be programmed to run whenever particular cells are updated.

I have made lots of dynamic dashboards using purely formulas, but often a weird mix of IFERROR, INDEX, MATCH, INDIRECT, AND, OR, IF, TEXT, SUMIFS, COUNTIFS, LOOKUP, ROW, COLUMN and RANK.EQ.

Today I will be sharing with you the combination I use in sorting and picking the top records from a list somewhere else. And it always monitors that list and updates to consistently give the top records always. The combination is very simple, uses RANK.EQ, INDIRECT and MATCH.

So lets see how it works. 

I have made a sample table (downloadable) for illustration.

And I would like to show in another sheet or entirely new worksheet the top 3 sales by sales amount. Ordinarily, I would have to sort the list by sales amount in descending order and copy out the top 3 records. It would work but it won't be dynamic. When the records change, my top 3 list will still be showing for the old list. Showing non-current values.

We will now attempt to do this entirely by formulas -- from the sorting to the picking of the top 3 records and it will always show the top 3 records even when the list records are changed.

Step 1:
Do a ranking of the records by the sales amount in descending order using RANK.EQ. As shown below, for the first record, BBQ Chicken, the formula entered is =RANK.EQ(C3,$C$3:$C$18,0)

Step 2:
On the sheet we intend to display the top 3 records, we type out the fields.

Step 3:
We'll use INDIRECT and MATCH to pick the values with ranks 1, 2 and 3, as they represent the top 3 records.

Let's examine the formula used to pick the first record and it's Pizza Type field: =INDIRECT("'All Sales'!A"&MATCH(1,'All Sales'!$D$1:$D$18,0))

INDIRECT("'All Sales'!A" tells Excel to reference a Sheet named 'All Sales'! and the column A. This is actually the sheet with all the sales records and the column A is the Pizza Type field. Next is to figure out the row to pick, the row that corresponds to the highest sales amount record. Rank 1 record.

MATCH(1,'All Sales'!$D$1:$D$18,0) tells Excel to give us the row position of the record that has 1 in column D. Column D holds the ranking. So we are asking Excel for the row of the record with rank 1. For this particular list the row is 7 (Chicken Feast).

 =INDIRECT("'All Sales'!A"&MATCH(1,'All Sales'!$D$1:$D$18,0)) evaluates to  =INDIRECT("'All Sales'!A"&7) and finally, ='All Sales'!A7 (cell A7 in sheet 'All Sales')

Now you should be able to figure out how the formulas in the other cells work.

And that's how you pick the top records from any list. And it's dynamic.

Let's change the sales amount for BBQ Chicken, from N620,000 to N1,620,000 and go check if our Top 3 table has updated to reflect the change we just made.

Yes! Our Top 3 table is now showing BBQ Chicken as the topmost record.

Don't forget to download the practice file here. 

Now you can do one more useful task with Excel. Congrats!