How to build a top N parameter in Excel

In BI tools such as Tableau and Power BI, one of the most common milestones toward intermediate learner status creating a Top N parameter. This feature allows users to choose a value for N (e.g., 3, 5, or any other number) and the chart or table adjusts dynamically to reflect that selection.

While this was once a fairly challenging task in Excel, it’s now far simpler thanks to dynamic array functions paired with traditional data validation features. In this blog post, we’ll explore how it all comes together: You can download the exercise file below:

Here, we have a dataset of superhero sales, and our goal is to build a Top N report that displays only the records of the highest-selling superheroes. The first step is to incorporate a user-defined input (or, if you prefer a fancier term, a parameter) into the worksheet:

Next, let’s add Data Validation to this cell. With the cell selected, navigate to Data > Data Tools > Data Validation. In the Data Validation menu, choose “Whole Number” and set a range. I’ll go with 1 to 5:

Since Top N needs to be a discrete value (no one wants a Top 2.5 sellers list), this is an ideal choice. It also establishes some boundaries to guide the user’s selection, adding helpful guardrails. You could further customize this with options like a custom input message or error alert, but we’ll skip those for now. If you’d like to learn more about those features, check out this post from Microsoft Support.

Now, let’s add a formula to retrieve the Top N items based on Gadget Sales ($), sorted from high to low. This is hands-down the trickiest part of setting up the parameter. We’ll break down how all the pieces come together after we get it into the worksheet:

=SORT(FILTER(hero_sales,
        hero_sales[Gadget Sales ($)] >= LARGE(hero_sales[Gadget Sales ($)], H1)), 4, -1)

Here’s how it works step-by-step: The innermost part, LARGE(hero_sales[Gadget Sales ()], H1), finds the nth largest value in the Gadget Sales ($) column, where “N” is a number specified in cell H1—say, 5 for the top 5. This acts as a threshold.

Next, the =FILTER() function takes the entire hero_sales dataset and keeps only the rows where the Gadget Sales ($) value is greater than or equal to that Nth largest value, effectively pulling out the top performers.

Finally, SORT() arranges this filtered list based on column 4, that is the Gadget Sales ($), in descending order (indicated by -1), giving you a neatly ranked list of the top N entries by gadget sales.

Give it a try yourself! Depending on the limits set in your data validation, you’ll either see that number of top sellers returned, or you’ll encounter an error message:

You might notice that the headers aren’t included in the filtered data, which technically makes sense as they don’t match the filter criteria! But you likely want them included too, right? In that case, let’s use the VSTACK() function to vertically combine these two elements:

=VSTACK(hero_sales[#Headers],
  SORT(FILTER(hero_sales,
        hero_sales[Gadget Sales ($)] >= LARGE(hero_sales[Gadget Sales ($)], H1)    ), 4, -1))

We’ve now got the headers included in our Top N analysis.

This exercise nicely blends classic Excel data validation with modern dynamic array functions, enabling us to accomplish tasks that were once much simpler in BI tools like linking your Top N to a chart, as shown here:

What questions do you have about creating a Top N parameter in Excel or integrating data validation with dynamic arrays? Let me know in the comments.