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 in Office 2021 and Microsoft 365.
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
- 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.
- 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.
- 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.
- 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.
- 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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
