How to Find the Top N Stocks in WPS Office Spreadsheets Without Copying Data
Question details
The user needs a dynamic way to return the top stocks, ETFs, BDCs, and mutual funds by projected annual dividend in a spreadsheet without copying or manually sorting data.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user maintains an investment watchlist and wants a separate top N dividend-payer list that updates automatically when the source data or category changes.
- Observed behavior
- The goal is for the top N rows to appear automatically in a separate area whenever the source data or selected category changes, without manual copying.
Confirm that your dataset has clear column headers, a category column such as Stock, ETF, BDC, or Mutual Fund, and a numeric projected annual dividend column. Also make sure your WPS Office version supports dynamic array functions if you use the formula method.
Return the Top N with FILTER, SORT, and TAKE in WPS Spreadsheets
Best for WPS Office versions and Microsoft 365-style spreadsheets that support dynamic array functions. Results spill automatically and update when the source data or input cells change.
Dynamic array functions eliminate helper columns and manual copy-paste. FILTER isolates the selected category, SORT orders the filtered rows by projected annual dividend, and TAKE returns only the first N rows.
Launch WPS Office, open the workbook containing your stock, ETF, BDC, and mutual fund data, and confirm the data is in a fixed range such as A2:E500.
Select two empty cells, type a category such as Stock in cell G1 and the number 10 in cell G2, then use these cells as adjustable inputs so you can change the filter and row count without editing the formula.
Click the cell where the result list should start and enter: =TAKE(SORT(FILTER(A2:E500,C2:C500=G1),5,-1),G2). Adjust the ranges to match your data; column 5 must be the projected annual dividend column.
Press Enter. WPS Spreadsheets spills the filtered, sorted top N rows into adjacent cells. Change the category in G1 or the number in G2 and confirm that the list updates automatically.
Use a PivotTable or Power Query for Excel 2019 and Older WPS Versions
Use this when dynamic array functions are not available. It returns a top N dividend-payer view that refreshes without manual copying.
Find Top N Dividend Payers in WPS Spreadsheets Without Copying Data
You can build the same dynamic FILTER, SORT, and TAKE formula in WPS Office Spreadsheets. Open your Excel workbook, apply the formula, and let WPS return the top stocks, ETFs, BDCs, or mutual funds automatically whenever the data or category changes.
- 1. Download and install WPS Office: Go to the official WPS Office download page, download WPS Office, and install it on your computer.
- 2. Open the Excel workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook containing your stock, ETF, BDC, and mutual fund data.
- 3. Create input cells and enter the dynamic formula: In a blank area, type the category and top N count, then enter the formula =TAKE(SORT(FILTER(A2:E500,C2:C500=G1),5,-1),G2) in the output cell and press Enter.
- 4. Change category or N and refresh results: Change the category or top N input and confirm that WPS Office automatically spills the refreshed top dividend payers without copying the original data.
Frequently Asked Questions
Which Excel versions support FILTER, SORTBY, and TAKE for top N lists?
Microsoft 365 and current WPS Office builds that support dynamic arrays support these functions. Excel 2019 does not support them, so use a PivotTable, Power Query, or helper formulas instead.
Can I change the category from stocks to ETFs without rewriting the formula?
Yes. Link the FILTER condition to a category input cell. Change that input cell to ETF, BDC, or Mutual Fund, and the formula recalculates automatically.
What if my WPS Office version does not have the TAKE function?
Use a PivotTable with a Top 10 value filter, or use Power Query or helper formulas to replicate the same dynamic top N result.
Does the dynamic formula copy the original data?
No. The dynamic array formula spills results into a separate range. The original data remains in place and the result range updates automatically.
How do I make the top N number adjustable?
Put the number 10 in a separate cell, such as G2, and use that cell reference as the second argument of TAKE. Change G2 to 5, 20, or another value to adjust the returned rows.
