How to Return the First 10 Rows of an Excel Table with a Formula in WPS Office
Question details
The user needs a formula to return the first 10 rows of an Excel table without applying criteria, and the formula should return fewer rows automatically when the table has fewer than 10 records.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has an Excel table and wants to extract the header row plus the first 10 data rows automatically.
- Observed behavior
- The formula should return the first 10 rows, and automatically return fewer rows when the table contains fewer than 10 records.
Open the workbook in WPS Spreadsheets and confirm that the table is formatted as an Excel Table with a defined name such as Table1. Check that your WPS Office version supports dynamic array functions like TAKE, CHOOSEROWS, SEQUENCE, and TOCOL.
Use TAKE with the Excel Table structured reference
Best for Excel tables because it returns the header row plus the first 10 data rows and automatically handles shorter tables.
TAKE is a dynamic array function that extracts a specified number of contiguous rows or columns from the start or end of an array. When you reference Table1[#All], WPS Spreadsheets includes the header row plus all data rows, so requesting 11 rows returns the header and the first 10 data rows.
Click an empty cell where you want the result to spill, for example D1.
Type =TAKE(Table1[#All],11) and press Enter. Replace Table1 with your actual table name if it is different.
WPS Spreadsheets fills the header row and the first 10 data rows, or fewer rows automatically if the table contains fewer than 10 records.
Use CHOOSEROWS and SEQUENCE for an explicit row count
Use this if you want a more explicit formula that calculates the number of rows dynamically with MIN and ROWS.
Return the first 10 values from a single-column range
Use this only when the source is a normal single-column range rather than a multi-column Excel table.
Return the First 10 Table Rows in WPS Spreadsheets
Use WPS Spreadsheets to open your Excel table and apply dynamic array formulas such as TAKE and CHOOSEROWS. WPS Office supports the same formulas and .xlsx format, so you can extract the first 10 rows directly without converting files.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook that contains the table.
- 2. Click an empty result cell: Select a blank cell with enough space below and to the right for the spilled rows and columns.
- 3. Type the TAKE formula: Enter =TAKE(Table1[#All],11) and press Enter, replacing Table1 with your table name.
- 4. Check the output range: Confirm that WPS Spreadsheets returns the header row and the first 10 data rows, or fewer when the table is shorter.
Frequently Asked Questions
Does TAKE work in WPS Spreadsheets?
Yes, WPS Office Spreadsheets supports dynamic array functions including TAKE. If you do not see the function, update WPS Office to a recent version.
What happens if my Excel table has fewer than 10 rows?
The formula returns only the available header row plus data rows. For example, =TAKE(Table1[#All],11) does not error when the source table has fewer than 10 data rows.
How do I include the header row in the result?
Reference Table1[#All] rather than Table1. The formula =TAKE(Table1[#All],11) returns the header row plus the first 10 data rows.
Can I use a normal range instead of an Excel table?
Yes. For a single-column range, use =TOCOL(TAKE(A1:A100,10),3). For a multi-column range, use =TAKE(A1:C100,11) to include the first row as a header.
Does this work with .xlsx files from Microsoft Excel?
Yes, WPS Spreadsheets opens .xlsx files and supports these formulas while preserving compatibility when you save back to .xlsx.
