FILTER on top n values

To filter a set of data to show the top n values you can use the FILTER function together with the LARGE function. In the example shown, the formula in F5 is: =FILTER(data,score>=LARGE(score,3)) where data (B5:D16) and score (D5:D16) are named ranges.

April 14, 2025 · 1 min · 73 words · Daniel Foster

Table of Contents

TheLARGE functionis a simple way to get the nth largest value in a range.

Only rows where the result is TRUE make it into the final output.

The output from FILTER is dynamic.

Excel formula: Basic filter example

If source data or criteria change, FILTER will return a new set of results.

Values can be sorted by one or more columns.

SORT returns a dynamic array of results.

Excel formula: Filter this or that

How to show top or bottom n results

Excel formula: Filter text contains

Excel formula: Filter exclude blank values

Excel formula: Name of nth largest value

Excel FILTER function

Excel LARGE function

Excel SORT function