How to create dynamic top 10 or n list in Excel?
In many projects and business processes, it is often necessary to rank individuals, organizations, products, or other entities based on their performance or numerical values. A "top list" serves to highlight the highest-performing entries, such as top students by grade, top salespersons, or departments with the most revenue. For example, you may have a table of student grades and want to dynamically extract the top 10 scorers for awards, analysis, or monitoring educational outcomes, as illustrated in the screenshot below. Creating a dynamic top 10 or top N list in Excel allows you to see updated results automatically as your data changes, saving time and reducing errors from manual ranking. This guide introduces several practical solutions—including formulas, pivot tables, and VBA macros—to help you build a dynamic top 10 or n list to meet a variety of data analysis needs efficiently.
Create a dynamic top 10 list in Excel
Create a dynamic top 10 list in Office 365
Create a dynamic top 10 list in Excel
In Excel 2019 and earlier versions, creating a dynamic top 10 (or top N) list involves combining formulas to simultaneously extract both the top values and their associated names or IDs. This solution is widely used and suitable for situations where you want the list to update automatically as your data changes. The following operations outline how to achieve this using classic Excel formulas. These formulas provide flexibility and do not require special Excel add-ins, but the setup process is a bit more involved compared to some modern dynamic array functions.
Formulas to create a dynamic top 10 list
1. To begin, you need to extract the top 10 values from your values range. Enter the following formula in a blank cell (for example, cell G2). After entering the formula, drag the fill handle down to generate your dynamic top 10 value list. See screenshot:
2. Next, to display the corresponding names (or IDs) associated with those top values, enter the following formula in cell F2. This is an array formula, so after inputting, press Ctrl + Shift + Enter to confirm. This formula finds the names corresponding to the top values you just extracted:
- A2:A20 is the range from which to pull the names;
- B2:B20 is the score or value range;
- G2 is the top value from the formula above;
- B1 is the header of the value list and used for offset in ROW calculations.
This formula dynamically links the highest values to their names. If your value range contains duplicates, COUNTIF ensures each matching name appears just once with its score.
3. After extracting the first result, select the formula in cell F2 and drag the fill handle down to copy the formula to as many rows as needed. This will expand your result to dynamically display the names of all top entries, matching those scores. See screenshot:
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Formulas to create a dynamic top 10 list with criteria
In some analysis tasks, you may need a top list that only displays entries meeting certain criteria—such as limiting top results to a specific group, team, or category. For instance, you may want to identify the top 10 scores for only "Class 1" from a total data sheet containing grades from multiple classes. Here’s how you can use formulas for this scenario:
1. Start by extracting the top 10 values that meet your specified criterion (e.g., "Class 1") from the dataset. Enter this formula in the target cell (for example, J2):
2. After entering the formula, press Ctrl + Shift + Enter to confirm as an array formula and then drag down the fill handle to fill other cells. The formula will return the highest 10 values that match your chosen condition (e.g., all scores from "Class 1").
3. To list the corresponding names for these top values under your criteria, copy and paste the formula below into cell I2 and press Ctrl + Shift + Enter as an array formula. Then, fill down as needed to generate the full list of names.
Make sure to adjust the ranges in the formulas to match your actual data setup. Be mindful that using large data ranges with array formulas can slow down performance. If duplicate values appear in your top 10, the formula will appropriately handle repeated scores and give multiple student names if their grades are equal.
Create a dynamic top 10 list in Office 365
While earlier versions of Excel require combining several functions with array formulas, Office 365 (and Excel 2021) introduces dynamic array functions such as INDEX, SORT, SEQUENCE, and FILTER that greatly simplify the workflow. These functions make it easier to build dynamic top 10 lists, reduce errors, and are especially useful for tables that grow or change frequently. If you operate in an environment with constantly updating data, these functions can streamline your analysis and enable quicker business decisions.
Formula to create a dynamic top 10 list
To extract and display a dynamic top 10 list using Office 365, enter the formula below in your desired output cell. All you need to do is adjust the ranges and numbers based on your needs, and the formula automatically shows the latest top 10 results whenever your data changes.
Simply press the Enter key. The complete top 10 list appears instantly and will remain dynamic, so additional data or modified scores are immediately reflected in your ranking.
SORT function:
=SORT(array, [sort_index], [sort_order], [by_col])
- array: The range you want to sort.
- [sort_index]: Number of the column to sort by. For a typical grade table, this is often the second column.
- [sort_order]: Use 1 for ascending or -1 for descending order. To get top scores, use -1.
- [by_col]: Whether to sort by columns (TRUE) or by rows (FALSE or omitted).
For example: SORT(A2:B20,2,-1) sorts A2:B20 by the second column in descending order.
SEQUENCE function:
=SEQUENCE(rows, [columns], [start], [step])
- rows: Number of rows to return, e.g., 10 for a top 10 list.
- [columns]: (Optional) Number of columns to return.
- [start]: (Optional) Starting value.
- [step]: (Optional) Value to increment by.
SEQUENCE(10) generates the numbers 1 through 10, letting INDEX pick the top 10 sorted results.
Combining these, =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}) gives you a dynamic, two-column top 10 list.
Formula to create a dynamic top 10 list with criteria
If you need to extract the top 10 for a particular group, such as "Class 1", these advanced Office 365 functions can create a top N list while including only those rows that meet your criteria. Place the formula below in your desired location and adjust the ranges and criteria cell as needed:
After entering the formula, simply press the Enter key. The top 10 list filtered and ranked just for the specified criterion displays at once, updating each time you modify your data or criterion.
FILTER function:
=FILTER(array, include, [if_empty])
- array: The cell range to filter.
- include: The condition (e.g., equals to a given class) for inclusion.
- [if_empty]: (Optional) What to display if no results meet the criteria.
=FILTER(A2:C25,B2:B25=F2) returns only those rows where column B matches the value in F2.
Create a dynamic top 10 list with a Pivot Table
Pivot Table: Automatically display top N results interactively
An alternative way to build a dynamic top N list is by using Excel's Pivot Table feature. This method is especially suitable for large datasets, interactive analysis (such as quickly changing the number of top items or applying filters), or when you want to avoid complex formulas. Pivot tables are user-friendly and update automatically when data changes, making them great for dashboards or reports that are shared with others.
To create a dynamic top N list using a pivot table:
- Click anywhere inside your data table, then go to Insert > PivotTable.
- In the PivotTable dialog box, choose where you want the pivot table placed and click OK.
- Drag your "Name" (or similar identifier) field into the Rows area.
- Drag your "Score" (or value column) into the Values area. It will usually default to "Sum of" or "Count of"—for top lists, you generally want "Sum" or "Max". Change the value field calculation if necessary by right-clicking and choosing Summarize Value By.
- Sort the "Score" column in descending order by right-clicking a value and selecting Sort > Sort Largest to Smallest.
- To limit to the top N results, click the drop-down arrow on Row Labels, select Value Filters > Top 10..., set the number (for example, Top 10) and the field to filter by, then click OK.
Your pivot table now shows the dynamic top 10 (or any N you specify). To change the top N, simply revisit the filter settings. If your data changes, refresh the pivot table to update the rankings instantly.
Benefits of this approach include fast setup, easy sorting, and interactive adjustment. However, pivot tables cannot automatically add corresponding rows from other columns unless included in the Row or Values area. Advanced users can further customize reports by grouping, creating slicers, or incorporating the Top N filter into dashboards.
Create a dynamic top 10 list using VBA
VBA Macro: Automatically generate and refresh a top N list
Using a VBA macro is well-suited for users dealing with extensive or frequently updated data where automating the extraction and refreshing of a dynamic top N list is required. Macros are ideal for reducing repetitive tasks and ensuring consistency. You can create a routine that sorts your data and copies only the top N rows to a specific location each time it runs.
To use a VBA macro for creating a dynamic top N list, follow these steps:
- Click Developer > Visual Basic to open the VBA editor. (If you do not see the Developer tab, go to File > Options > Customize Ribbon and enable "Developer".)
- In the VBA window, click Insert > Module to add a new module.
- Paste the following VBA code in the module:
Sub ExtractTopNList()
'Updated by Extendoffice 2025/7/24
Dim DataRange As Range
Dim OutputRange As Range
Dim N As Integer
Dim ws As Worksheet, tempWS As Worksheet
Dim xTitleId As String
Dim LastCol As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set DataRange = Application.InputBox("Select the full data range to analyze (including headers)", xTitleId, ws.UsedRange.Address, Type:=8)
Set OutputRange = Application.InputBox("Select the top-left cell of the output area", xTitleId, "", Type:=8)
N = Application.InputBox("How many top items to extract? (Enter a positive integer)", xTitleId, 10, Type:=1)
If DataRange Is Nothing Or OutputRange Is Nothing Or N < 1 Then Exit Sub
' Create a temporary worksheet to avoid sorting original data
Set tempWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
DataRange.Copy tempWS.Range("A1")
' Determine last column for sorting key
LastCol = DataRange.Columns.Count
' Sort in temporary sheet
tempWS.UsedRange.Sort Key1:=tempWS.Cells(1, LastCol), Order1:=xlDescending, Header:=xlYes
' Copy headers and top N rows to output
tempWS.Rows(1).Copy Destination:=OutputRange
tempWS.Range("A2").Resize(N, LastCol).Copy Destination:=OutputRange.Offset(1, 0)
' Optional: Delete temporary sheet
Application.DisplayAlerts = False
tempWS.Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub4. To execute the macro, make sure your data is properly laid out in a table with headers. Press F5 or click the 
button in the VBA editor. You will be prompted to:
- Select your data range (including headers for proper sorting).
- Select the output cell to paste the results.
- Enter the number N (e.g., 10 for Top 10).
The macro will copy the top N entries (including headers) to the location you specified.
It is advisable to use this in a backup or copy of your workbook when first testing. If errors occur (such as selecting an incorrect range), re-run and ensure your ranges and data layout are correct.
This solution is ideal for automating repetitive reporting tasks, creating dashboards, or quickly updating top N reports without manual formulas or sorting. You may further customize the VBA script for more complex ranking logic, such as sorting by a specific column or exporting results to another workbook.
Troubleshooting: If the macro doesn't work as expected, check that your data table includes proper headers, correct the data types to avoid sorting issues, and make sure cell references are accurately selected in each prompt. Always save your work before running macros to prevent accidental data changes.
In summary, Excel supports various methods to generate and maintain a dynamic top N list—from traditional formulas to powerful Office 365 functions, pivot tables for interactive analysis, and VBA macros for advanced automation. Choose the method that best suits your workflow and data scale. Using formulas is effective for most manual analyses, Office 365 functions provide the greatest simplicity and power, pivot tables are excellent for quick, flexible summaries, and VBA is especially useful automating large, repetitive ranking tasks. Always verify the integrity of your formulas or code, and adapt cell references to match any changes in your data structure as your project evolves.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in