How to Return Active Rows from an Excel Table in Excel 2016 Using ROW Array Formula
Question details
The user needs to return the active row numbers of an Excel table in Excel 2016, where =ROW(Table1) does not spill automatically because dynamic arrays are unavailable.
- Product
- Microsoft Excel 2016 / WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working in an Excel 2016 workbook with a table named Table1 and wants to output all active table row numbers into a worksheet range.
- Observed behavior
- In Excel 2016, =ROW(Table1) does not spill automatically and may return only the first row or a single value unless it is entered as a legacy array formula over a selected output range.
Before you begin, confirm that the table is named Table1 or update the formula to use your actual table name. Select the output range that matches the number of rows in Table1 before entering the array formula.
Enter =ROW(Table1) as a Legacy Array Formula
Use this method when you want all active table rows returned at once in Excel 2016 or WPS Office Spreadsheets without dynamic array support.
Excel 2016 does not support dynamic array spilling, so the formula must be confirmed as a legacy array formula. Selecting the output range before entering the formula tells Excel where to return all table row numbers.
Click the first cell where the row numbers should appear, then drag to select exactly as many cells as there are rows in Table1.
With the range still selected, type =ROW(Table1) in the formula bar.
Press Ctrl+Shift+Enter instead of Enter to enter the formula as an array formula.
Check that the formula bar now shows {=ROW(Table1)} and that all selected cells display the active row numbers.
Use a Regular ROW Formula with INDEX and Fill Down
Use this method if you prefer a regular formula that can be filled down one row at a time instead of using Ctrl+Shift+Enter.
Use WPS Office Spreadsheets to Enter ROW Table Array Formulas
Open your Excel 2016 workbook in WPS Office Spreadsheets, select the target range, enter =ROW(Table1), and press Ctrl+Shift+Enter to create a legacy array formula that returns all active table rows.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel 2016 workbook that contains Table1.
- 2. Select the output range: Click the first output cell and drag to select the number of cells that matches the table rows.
- 3. Enter the ROW array formula: Type =ROW(Table1) in the formula bar while the output range is selected.
- 4. Confirm as an array formula: Press Ctrl+Shift+Enter to enter it as a legacy array formula; the formula bar shows braces around the formula.
- 5. Verify the row numbers: Check that the selected range now displays all active row numbers from Table1.
Frequently Asked Questions
Why does =ROW(Table1) not spill automatically in Excel 2016?
Excel 2016 lacks dynamic array formulas, so =ROW(Table1) does not spill. Select the output range, type the formula, and press Ctrl+Shift+Enter to enter it as a legacy array formula.
How do I enter =ROW(Table1) as an array formula in Excel 2016 or WPS Office?
Select the cells where the row numbers should appear, type =ROW(Table1) in the formula bar, and press Ctrl+Shift+Enter. The formula bar should then display {=ROW(Table1)}.
What regular formula can I use instead of a legacy array formula?
You can use =ROW(INDEX(Table1,1,1)) in the first output cell, press Enter, and then drag the fill handle down for the required number of rows.
How many cells should I select before entering =ROW(Table1)?
Select exactly as many cells as there are rows in Table1. Selecting too few cells returns only part of the table, and selecting too many may produce extra errors or unwanted results.
Does WPS Office support Excel array formulas?
Yes, WPS Office Spreadsheets supports legacy array formulas with Ctrl+Shift+Enter and works with .xlsx workbooks. Dynamic array behavior may depend on the WPS Office version.
