If you’ve ever struggled to make an Excel sheet that amazes users, this is the post for you.

In this post we will learn how to create a report that allows the user to query a list and return the top salaries for a set number of users along with those user’s Department(s) and Name(s).

What makes this so impressive is that you will allow the user to define how many records are returned, such as the Top 5, Top 10, or any number (within reason) that they choose.  The function will even account for duplicate Top N salaries.

This will be accomplished using the new Dynamic Array FILTER function along with a little help from some other function to make things more interesting, such as multi-level sorting and dynamic numbering.

This post will give you a glimpse into some of the incredible feats of data manipulation prowess to be gained by using the new Dynamic Array functions in Excel.

This will be a high-level overview of the functions and the finished formulas.  The functions used in this post are explained in great detail in the XelPlus online course “Excel Dynamic Arrays: Beginner to Expert”.

You can also find several video posts showcasing other Dynamic Array functions that will surely inspire you to want to learn more about these amazing new functions.

Keep in mind, these Dynamic Array functions are only available to Office 365 subscribers.

The Goal

Our dataset is an official Excel Data Table named “TableSalary” that resides on a sheet named “Top”.  We want to extract a list of Top N salaries along with the associated Department and Name.  The value of N will be supplied by the user.

Generating the Top N List

  1. On the Top sheet, select cell G8 and enter the following formula:

(click on the formula for a larger view)

If we enter a 5 in cell G5 for the Top N value, we are returned a single result.

This is because we are returning the 5th largest item in the dataset.  We need everything that is greater than or equal to the 5th largest item in the dataset.

  1. Select cell G8 and update the existing formula with the following:

(click on the formula for a larger view)

Sorting the Results of the Top N List

Let’s refine the report by adding the SORT function to sort the results in descending order by Yearly Salary.

  1. Select cell G8 and update the existing formula with the following:

(click on the formula for a larger view)

If we wished to sort in descending order by Yearly Salary, but then sort all duplicate Yearly Salaries in ascending order by Department, we would update the formula as follows using array notation for the columns to be sorted and their respective directions.

(click on the formula for a larger view)

Adding a Dynamic Numbered List

Let’s improve the readability of the report by adding rank numbers in column F.  This is a simple matter of adding a SEQUENCE function in the column beside our SORT/FILTER function.

  1. Select cell F8 and enter the following formula:

(click on the formula for a larger view)

To account for times where the user may enter a value for the Top N that is greater than the number of items in the dataset, we will include a check using the COUNT function to determine if the user has selected more entries than the list can provide.  If they do, it will display a friendly message.

  1. Select cell F8 and enter the following formula:

(click on the formula for a larger view)

Replacing Default Error Messages

As you can see from the previous image, if the formula in cell F8 returns a message, the SORT/FILTER formula in cell G8 displays a #NUM! error message.

To ensure a clean report, select cell G8 and update the formula with the following modifications.

(click on the formula for a larger view)

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Excel NEW Dynamic Arrays Course 

BE THE FIRST to master the NEW Excel Formulas

FILTER, SORT, UNIQUE: From Beginner to Expert Level!

Start Learning