When analyzing large amounts of data in Excel, the best approach often is to retrieve top (10) values.
Consider the following example:
Here, we have a table with the invoices from the 29th of January. There are 904 invoices for that day.
Values in the invoice column are unique as those are invoice numbers.
Values in the user column are not unique, as some users have multiple invoices on that day, and values in the team column are not unique as there are only five teams.
Values in the EUR amount column are also not unique as amounts charged are also repeating – there are many invoices in the same amount.
We are obviously interested in our top invoices. With the help of the LARGE, COUNTIFS and SUMIFS functions, we can easily retrieve some information about the list we need to generate:
10th largest invoice amounts to EUR 97,99. However, there are 11 invoices amounting to EUR 97,99 or more, as our 11th largest invoice is of the same amount as our 10th largest invoice. Sum of those 11 largest invoices is EUR 1.084,89.
Top 10 lists in Excel with formulas
We can generate our top 10 list of invoices using the following formulas:
However, this is neither simple nor fast. We need to have two additional columns here (rank and row) in order for our formulas to function, we have to use complex conditions, and we have to write formulas for four (five) additional columns.
Note that, as we here assumed that we will have only 10 invoices in our Top 10 invoices, we missed the 11th largest invoice, which is as large as the 10th largest invoice.
Most of these formulas are based on the already mentioned LARGE function. You will also need to handle conditional statements and INDEX and MATCH functions in order to successfully write them.
If we would want to see our top 10 billed users, things would get even more complicated. As user codes are repeating (some users have multiple invoices), we would have to use array formulas:
As seen, our formulas in the column G are incredibly complex, and you will have to handle array formulas well in order to use them in this way.
They can also be a resource hog, and even with this relatively small data set, this array formula is already forcing noticeable recalculation times and causing performance issues with the file.
Top 10 lists in Excel with filtering
In a second, often used approach, filtering is used. We will use Sort & Filter in our examples here, but the same principles and limitations apply to Excel Tables.
For example, we can sort our EUR amount column by size in order to retrieve the largest invoices:
This is what our “top 10” list looks like:
Excel AutoFilter also contains built in Top 10 feature, which is able to select top or bottom, 10 or some other number, items or percent of items:
This is how our data would look like had we applied Top 10 filter (top 10 items) on the column D, and then additionally sorted our data from largest to smallest:
However, this is also only simple because invoice numbers are unique.
Generating top 10 billed users list is more complicated. Our first order of business would be to copy our user list into a new column and remove duplicates:
Next, we would have to use the SUMIFS function in order to retrieve amounts billed to users, and then sort those values by size in order to generate a list of top billed users:
Top 10 lists in Excel with Pivot Tables
Ideal tool for this kind of analysis in Excel are Pivot Tables.
We start by selecting our data and creating a new pivot table:
As we are interested in top invoices, we drag the invoice filed to Rows, and we drag the EUR amount to Values. There are multiple options available on how to show values here, but for our purposes Sum of EUR amount, the default one, is suitable:
Next, we sort our Row Labels descending, by the Sum of EUR amount:
Then, we filter our Row Labels with Top 10 filter, by Sum of EUR amount:
This is our result:
Note that actually top 11 invoices are on this list, as 11th largest invoice is in the same amount as the 10th largest invoices.
Note also that our invoice numbers are not sorted, and can’t be sorted, by invoice number.
Using the same principles, it is also easy to generate our Top 10 users list:
Pivot Tables also enable us to quickly do more.
For example, if we want to include invoices to our users list, we can show invoices billed to our top users by dragging the invoice filed under the user field in Rows area:
This can also be further expanded, with pivot tables offering wide range of possibilities, such as filtering top 10% of items, filtering items greater, equal to or smaller than certain amount, counting, averaging, and more.
Dig deeper:
2 thoughts on “Top 10 lists in Excel”