Excel Top N Formula: Find the Highest Values and Rows

Formulas & functions · Updated November 19, 2026

To return the top N numbers in Excel for Microsoft 365, use =LARGE(B2:B20,SEQUENCE(3)) to spill the three largest values from B2:B20. To return the matching rows as well, use =TAKE(SORTBY(A2:B20,B2:B20,-1),3). The first formula ranks numbers; the second sorts records by the value column and returns three rows.

What “top N” means in Excel

Top N means selecting a chosen number of highest values or records from a larger range. N is the number you want back: for example, top 5 sales, top 10 test scores, or the three largest monthly totals. Excel has no single function called TOPN; the right formula depends on whether you need just numbers, the related labels or records, or every result tied at the cutoff.

The key distinction is whether you want exactly N results or all records that qualify for the top N positions. If the third-highest value occurs twice, a formula returning exactly three numbers includes only one of those two tied entries. A formula that includes every record tied with the third-highest value can return four records instead. Decide which result you mean before choosing a formula.

Example data and the basic LARGE formula

Suppose A2:A8 contains seller names and B2:B8 contains sales amounts. The amounts, in row order, are 5100, 5100, 4600, 4600, 3900, 3400, and 2750. LARGE returns the kth largest number, where k is 1 for the largest, 2 for the second largest, and so on. For this example, with sales in B2:B8, enter this formula in D2 to return the three largest values as a dynamic array:

=LARGE(B2:B8,SEQUENCE(3))

The result is 5100, 5100, and 4600, in that order. The repeated 5100 is intentional: LARGE counts duplicate values as separate entries. SEQUENCE(3) supplies the rank numbers 1, 2, and 3 to LARGE, so the results spill into the cells below D2. SEQUENCE is available in Microsoft 365 and Excel 2021 or later.

If N is stored in a cell, you can use that value instead of entering 3 in the formula. With sales in B2:B8 and the requested number of results in D1, enter:

=LARGE(B2:B8,SEQUENCE(D1))

If D1 contains 5, Excel returns five values. Make sure the cells where the results need to spill are empty; otherwise Excel can show a #SPILL! error. The formula requires D1 to specify a valid positive count no greater than the number of numeric entries in the range.

Return the records behind the highest values

A list of amounts is not enough when you need to know which seller, product, or date each amount belongs to. In Microsoft 365, SORTBY can sort a record range using a corresponding value range, and TAKE can return only the first N rows. With seller names in A2:A8, sales in B2:B8, and the requested number of rows set to 3, enter:

=TAKE(SORTBY(A2:B8,B2:B8,-1),3)

This returns three rows, each containing a seller name and its sales amount. The -1 argument sorts sales in descending order, from highest to lowest. In the example, the results include the two rows with 5100 and one row with 4600. Because there are two 4600 values, the formula returns only one of them to keep the output at exactly three rows. If you want a predictable order between equal sales, add a secondary sort key. With names in A2:A8 and amounts in B2:B8, this sorts sales high to low and names A to Z:

=TAKE(SORTBY(A2:B8,B2:B8,-1,A2:A8,1),3)

SORTBY and TAKE are available in Microsoft 365; TAKE is also available in Excel 2024. If your version does not support TAKE, you can sort the full range with the available sort tools, or use a formula designed for that older version rather than expecting this formula to work.

Choose between exactly N results and ties

LARGE returns a fixed number of positions, so ties can make its output contain repeated values. That is usually appropriate when a report must show exactly N entries, such as a top-five list. To return all records whose value is at least the Nth-highest value, first find the cutoff with LARGE, then filter the records against it. With names in A2:A8, sales in B2:B8, and a top-three cutoff, use:

=FILTER(A2:B8,B2:B8>=LARGE(B2:B8,3))

In the example, this returns four rows: both sellers with 5100 and both with 4600, because the third-highest value is 4600 and two records share that cutoff. FILTER is available in Microsoft 365 and Excel 2021 or later. If fewer than three numeric sales are available, LARGE cannot calculate the requested cutoff and returns an error; check the data or adjust the requested N.

Another common meaning of top N is the N highest distinct values, where a repeated value is listed only once. With numeric sales in B2:B8, Microsoft 365 or Excel 2024 can return the three highest distinct values with:

=TAKE(SORT(UNIQUE(FILTER(B2:B8,ISNUMBER(B2:B8))),1,-1),3)

FILTER keeps numeric entries, UNIQUE removes repeats, SORT orders them descending, and TAKE returns three values. For the sample data, the result is 5100, 4600, and 3900. This is different from the top three entries, which are 5100, 5100, and 4600. If the data contains fewer than three distinct numbers, TAKE returns the distinct values available rather than creating extra ones.

Top N within a category

You may need the top three sales for one region, rather than for the entire sheet. A practical dynamic-array approach is to filter the sales first, then rank the remaining values. With regions in A2:A20, sales in B2:B20, and the region to examine in G1, use this formula for the top three sales in that region:

=LARGE(FILTER(B2:B20,A2:A20=G1),SEQUENCE(3))

FILTER supplies only the sales whose region matches G1; LARGE then returns the first three ranks. This formula needs Microsoft 365 or Excel 2021 or later. It returns the values, not the matching names or full records. For a category-specific record list, filter the full rows using the same condition and then sort those rows by their sales. Check that the selected category has at least three numeric amounts, or the requested rank may not exist.

Older Excel versions and copy-down formulas

If SEQUENCE is unavailable, LARGE can still return top values one at a time. With sales in B2:B8, enter this in D2 and copy it down through D4:

=LARGE($B$2:$B$8,ROWS($D$2:D2))

ROWS returns 1 in D2, 2 in D3, and 3 in D4, so LARGE returns the first, second, and third positions. The dollar signs keep the sales range fixed as you copy the formula. This method works in older Excel versions that support LARGE and ROWS, but it does not automatically spill results. Duplicate values still occupy separate positions, and copying down beyond the number of numeric values produces an error.

When you need labels alongside results in an older version, a formula built around LARGE and a lookup can be used, but duplicates require care: a basic MATCH lookup can find the first occurrence repeatedly. That can show the same label more than once even when different records have the same value. For duplicate-heavy data, sorting the full table or using a Microsoft 365 dynamic-array formula is often easier to audit than building a complex legacy lookup.

Common errors and practical checks

Before relying on a top-N result, check the range and the way the values are stored. LARGE works with numbers; values that look numeric but are stored as text may not behave as expected. Confirm that the formula covers all intended rows and does not include a header. If new records are regularly added, an Excel Table can make a structured data range easier to expand and maintain.

  • #NUM! can mean k is zero, negative, or larger than the count of numeric values in the range. Check the requested N and the data.
  • #SPILL! means a dynamic-array result cannot occupy the cells it needs. Clear obstructing cells, including cells that appear blank but contain content.
  • Unexpected duplicates usually mean the formula is returning top positions, not distinct values. Use a unique-values approach if you want each amount once.
  • Missing tied records can happen when a formula returns exactly N rows. Use a cutoff-based filter if every record tied at the boundary must appear.

On Microsoft 365 for Windows, these formulas use commas between arguments as shown. Some regional settings use semicolons instead. Dynamic-array formulas also work in current Excel for Mac and Excel for the web, although the interface and available features can differ by version. If a function name is not recognized, check that your Excel edition supports it and that the formula uses your regional argument separator.

Which formula should you use?

For a quick list of the top numbers, LARGE with SEQUENCE is concise and makes duplicate handling clear. For top records that need their names or other columns, SORTBY with TAKE is easier to read and keeps each row’s details together. For every record meeting the cutoff, FILTER with a LARGE threshold expresses that rule directly. For distinct amounts, combine UNIQUE with sorting and take the requested number.

Consider whether the output should update when the source changes, whether ties should expand the result beyond N, and whether your Excel version supports dynamic arrays. If the result is for a recurring report, put N in a cell and refer to it in the formula rather than editing the formula each time. For a one-off task, sorting the data through Excel’s interface may be simpler, but a formula is useful when the result needs to recalculate as source values change.

Frequently Asked Questions

What is the Excel formula for the top 10 values?
With numbers in B2:B100, use =LARGE(B2:B100,SEQUENCE(10)) in Microsoft 365 or Excel 2021 and later. It spills the ten highest entries, including repeated values when the source has ties.

How do I return the top value in Excel?
With numbers in B2:B20, use =LARGE(B2:B20,1). You can also use MAX for the single greatest numeric value, but LARGE is convenient when you may later change the rank from 1 to another position.

How can I get the top five names with their values?
With names in A2:A100 and values in B2:B100, Microsoft 365 users can use =TAKE(SORTBY(A2:B100,B2:B100,-1),5). It returns five rows sorted by value from highest to lowest; tied values may mean other tied rows fall outside those five.

Does LARGE include duplicates?
Yes. LARGE ranks each numeric entry, not each unique amount. If 900 appears three times, those entries can occupy three top positions. Use UNIQUE when the goal is a list of distinct values.

How do I include everyone tied for the last top-N place?
Find the Nth-largest value with LARGE and use FILTER to return rows whose values are greater than or equal to that cutoff. This can return more than N records when values are tied at the boundary.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.