Grab our practice workbook 👉 HERE and follow along:

Learn how to use the FILTER function in Excel to find the top values in your data. This guide will show you step-by-step instructions to make your work easier.

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

💡 These steps are for Office 2021 and Microsoft 365 users.

How to Use the FILTER Function to Get Top Values

We want to extract the top N salaries from the Excel data table “TableSalary” along with the associated Department and Name. The user will specify the value of N.

Excel get Top values with FILTER function

Steps to Generate the Top N List

Enter the Basic FILTER Formula

  • Go to the “Top” sheet.
  • Select cell G8.
  • Enter the formula:
=FILTER(TableSalary, TableSalary[Yearly Salary] = LARGE(TableSalary[Yearly Salary], G5))
  • If you enter 5 in cell G5, this formula returns the 5th largest salary.

Update the Formula for All Top N Values

  • To get all values greater than or equal to the 5th largest salary, update the formula in G8:
=FILTER(TableSalary, TableSalary[Yearly Salary] >= LARGE(TableSalary[Yearly Salary], G5))
Excel get Top values with FILTER function

Sorting the Top N List

Sort by Yearly Salary

  • To sort the results in descending order by Yearly Salary, update the formula in G8:
=SORT(FILTER(TableSalary, TableSalary[Yearly Salary] >= LARGE(TableSalary[Yearly Salary], G5)), 3, -1)

Sort by Yearly Salary and Department

  • To sort by Yearly Salary and then by Department, use:
=SORT(FILTER(TableSalary, TableSalary[Yearly Salary] >= LARGE(TableSalary[Yearly Salary], G5)), {3,1}, {-1,1})

Adding a Dynamic Numbered List

Let’s improve the readability of the report.

Add Rank Numbers

We’ll use the Excel SEQUENCE function for this.

  • In cell F8, enter:
=SEQUENCE(G5)

Check out this article to learn how to create a numbered list with the SEQUENCE function in Excel

Check for Exceeding Values

  • To check if the user entered a value too large, update F8 with:
=IF(G5>COUNT(TableSalary[Yearly Salary]),"Too large",SEQUENCE(G5))

Handling Errors

If the formula in cell F8 returns a message, cell G8 might display a #NUM! error. To replace this default error with a friendly message, update the formula in cell G8:

=IFERROR(SORT(FILTER(TableSalary, TableSalary[Yearly Salary] >= LARGE(TableSalary[Yearly Salary], G5)), 3, -1), "Type a smaller number")

This formula ensures that if there’s an error, a helpful message will be shown instead of the default error code.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the functions to get Top Values in Excel with a hands-on example. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

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.