Top n Lists with Excel

I love data! I love collecting it, analyzing it, finding answers inside it, and ultimately presenting it. But even I find it hard at times to sort through and make sense of it all. That’s why I love dashboards and really encourage all business owners, managers, and employees to incorporate them into their daily work lives. Whether it’s having a clear look into the health of the company, figuring out which expenses are out of hand, or understanding how fast (or slow) customers are paying their bills; well prepared and presented dashboards can save decision makers a lot of time and money.

One of the most fundamental things you can achieve with dashboards is the filtering of data, narrowing information into the most pertinent, important, or actionable. Commonly in the form of top n lists, where n is any number of items you care to look at, using MS Excel to organize this type of information is actually fairly simple. Even if you are allergic to dashboards or any structured system for understanding information, there are certainly other applications for it. Maybe you’re keeping a running total of the minutes your basketball players are logging, trying to avoid fatigue. Or you want to continually look at which products are your best sellers. Or maybe you just want to keep track of and reward your top sales people. Again, all of this is easy and made possible with Excel.

The basic method for doing this involves only one function, LARGE(). The more dynamic, less error-prone adds ROW(). That’s it.

So let’s dive in, first with a plain-English explanation of what we’re trying to do. Given a set of data, we want to find and list the first (largest, oldest, most, etc.), then the second, third, etc. The Large function accomplishes that. Its format is pretty simple: LARGE(array,n). For example, if you have a list of 10 random numbers in column A, Large(A:A,5) will return the 5^th largest. Pretty simple. So, if we want to see the Top 5 in that same list, we could create five formulas, each having the same array, but a different value for the n. Large(A:A,5), Large(A:A,4), Large(A:A,3), etc.

The problem with doing this method, though, is that if you want to add additional results to your list (going from top 5, for example, to top 10), you have to copy the formula, then change the n for each new row/cell. Besides the fact that it is not efficient or dynamic, it is dependent upon human interaction, something I try to minimize as much as possible. To avoid this opportunity for human error, we force Excel to do even this math for us. By using the Row function, we can let Excel determine what the ‘n’ should be instead of manually entering it. Let’s see how.

The Row function gives the row for whatever cell you feed it. If no value is fed to the function (e.g. A7, E6, etc.), the assumed value is the current cell. That’s what we want. To have the first value in our list be the top (e.g. largest) value, we want this function to return 1. To do so we simply add 1 to the current row—using Row() + 1—then subtract the first cell in the column, which will remain fixed by using dollar signs ($) to make it an absolute (rather than relative) reference. We can then copy and paste this formula or drag it down for as many rows as we want.

The final formula, then, is:

LARGE($C$12:$C$20,ROW()+1-ROW($M$4))

Where:

1. $C$12:$C$20 is the fixed array of values. This array can be open (e.g. C:C) if you wish
2. $M$4 is the cell that will contain the #1 (e.g. largest or most) item in the list

To help you understand this further, I’ve attached a sample worksheet.  (For an added tip, I’ve randomly created the list of items; you can press F9 to re-randomize at any time.)

Top_n_Lists (2007 version)
Top_n_Lists (2003 version)