Top 10 lists in Excel

When analyzing large amounts of data in Excel, the best approach often is to retrieve top (10) values.

Consider the following example:

top 10s in Excel_data

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:

top 10s in Excel_data first look

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:

top 10s in Excel_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:

top 10s in Excel_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

Second often used approach is to use filtering and sorting to achieve these goals. For example, we can sort our EUR amount column by size in order to retrieve the largest invoices:

top 10s in Excel_filtering sort largest to smallest

This is what our “top 10” list looks like:

top 10s in Excel_filtering result

 

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:

top 10s in Excel_AutoFilter

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:

top 10s in Excel_AutoFilter result

 

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:

top 10s in Excel_filtering non unique 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 10s in Excel_filtering non unique sumifs sort

 

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:

top 10s in Excel_PivotTable insert

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:

top 10s in Excel_PivotTable select Rows and Values

Next, we sort our Row Labels descending, by the Sum of EUR amount:

top 10s in Excel_PivotTable sort descending

Then, we filter our Row Labels with Top 10 filter, by Sum of EUR amount:

top 10s in Excel_PivotTable filter top ten by Sum

This is our result:

top 10s in Excel_PivotTable unique 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:

top 10s in Excel_PivotTable non unique result

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:

top 10s in Excel_PivotTable multiple rows fields result

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:

Sort & Filter in Excel

One thought on “Top 10 lists in Excel”

Leave a Reply

Your email address will not be published. Required fields are marked *