Function Problems

How to Return the Top 20 Free Agents by Fantasy Points in Excel

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to extract and display the top 20 free agents based on their fantasy point totals from a dataset.

Product: Excel
Device & OS: not provided
Scenario: Filtering and ranking sports or fantasy league data to find the highest-scoring players who hold a specific status (such as 'FA' for Free Agent).
Observed behavior: A dynamic list or table needs to be generated that specifically filters for 'FA' status, sorts by the score in descending order, and restricts the output to exactly 20 rows.

Before you start

Ensure your dataset is organized in contiguous columns without blank rows in the middle, and identify the exact column letters that contain the 'FA' status and the fantasy points to adjust your formulas accordingly.

Solution 1Recommended

Use TAKE, SORT, and FILTER Dynamic Array Functions

This is the most efficient and direct method for modern Excel versions, extracting and sorting the top values dynamically without the need for helper columns.

By combining modern dynamic array functions, you can filter for specific criteria, sort the results, and limit the output array in a single formula. Note that this requires Microsoft 365 or newer versions of Excel that support dynamic arrays.

Select destination cell

Click on the cell where you want the upper-left corner of your new Top 20 table to begin (for example, M2).

Enter the nested formula

Type the formula =TAKE(SORT(FILTER(A2:K29,F2:F29="FA"),9,-1),20) into the formula bar. In this example, A2:K29 is your data range, column F contains the 'FA' status, and the 9th column contains the fantasy points.

Apply and let it spill

Press Enter. The formula will automatically spill the resulting top 20 rows into the adjacent cells, sorted from highest to lowest fantasy points.

Array Spilling: Ensure there is enough empty space below and to the right of your formula cell, otherwise you will encounter a #SPILL! error.

Solution 2

Rank and Filter using COUNTIFS

A highly compatible method for returning top values when dynamic array functions are not available in older versions of Excel.

Efficient Data Management

Filter and Rank Your Fantasy Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis tools, including dynamic array functions and advanced filtering, making it effortless to rank and extract your top fantasy players without complex workarounds.

1. Open your dataset: Launch WPS Spreadsheet and open your fantasy player dataset.
2. Select your output range: Click on the cell where you want the top 20 table to generate.
3. Apply the array formula: Enter the dynamic formula =TAKE(SORT(FILTER(A2:K29,F2:F29="FA"),9,-1),20) and press Enter.
4. Alternative method: Alternatively, use the 'Data' tab to add filters and rank using the COUNTIFS helper column method for backward compatibility.

Fully compatible with Microsoft Excel formulas like COUNTIFS and dynamic arrays.Built-in advanced filtering and sorting tools for quick data manipulation.Lightweight and runs smoothly even with large fantasy sports datasets.Free to use with an intuitive, familiar tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the formula to return the top 10 instead of 20?

Yes, simply change the final number in the TAKE function from 20 to 10. For example, your formula would end with ...,10) instead of ...,20).

What if my Excel version doesn't support the TAKE or FILTER functions?

If you are using an older version of Excel (2019 or earlier), you should use the helper column method with the COUNTIFS formula, as dynamic arrays are only available in Microsoft 365 and Excel 2021 or newer.

How do I filter for a status other than 'FA'?

In the FILTER function part of the formula (=FILTER(A2:K29, F2:F29="FA")), simply replace "FA" with your desired status text, such as "Active" or "Injured".

Why is my COUNTIFS formula returning the same rank for tied players?

The COUNTIFS formula works by counting how many players have strictly greater points. For players with identical points, it will assign them the same rank. If tie-breaking is necessary, you might need to add secondary criteria to your formula.

Excel & Data Problems