How to Keep an Excel Table Reference Fixed in WPS Spreadsheets When Columns Are Inserted
Question details
The user needs to keep an Excel table column reference fixed so a COUNTA formula continues counting the first column of the table after new columns are inserted before it or the column is renamed.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A table in WPS Spreadsheets uses structured references, and the user wants a formula to always count the first column of Table2 regardless of column insertions or renames.
- Observed behavior
- Using =COUNTA(Table2[Column3]) can shift or break when columns are inserted before the referenced column, because the structured reference follows the named column rather than the table's first column.
Open the workbook in WPS Spreadsheets and verify the table name, such as Table2, from the Table Design tab. Identify the cell containing the COUNTA formula and the first table column you want to count.
Use INDEX with the table reference to count the first column
Use this formula when you need the COUNTA result to keep pointing to the first column of the table even after columns are inserted or the original column name changes.
The INDEX function can return an entire column from a table when you use 0 for the row number and a column number. Because the column index is numeric, the formula does not follow a column name and remains fixed on the first table column.
Click the cell where you currently have =COUNTA(Table2[Column3]) or where you want the fixed result to appear.
Type =COUNTA(INDEX(Table2,0,1)) in the Formula Bar or directly in the cell, replacing the structured column reference with the INDEX expression.
Press Enter, then insert a new column before the table and confirm that the formula still counts the original first column.
Keep Excel Table References Fixed with WPS Spreadsheets
WPS Spreadsheets supports the same INDEX and structured table formulas as Excel, so you can make a COUNTA formula always count the first table column without switching software.
- 1. Open the workbook: Open WPS Office Spreadsheets and load the Excel workbook that contains Table2.
- 2. Select the target cell: Click the cell where the formula result should appear, then click the Formula Bar.
- 3. Enter the fixed formula: Type =COUNTA(INDEX(Table2,0,1)) and press Enter.
- 4. Verify the reference: Insert a new column before the table to verify that the formula still counts the original first column.
Frequently Asked Questions
Why does =COUNTA(Table2[Column3]) shift when I insert a column in WPS Spreadsheets?
The structured reference follows the column name. When a new column is inserted before it, the named column moves to a new position, so the formula references a different physical column. Use =COUNTA(INDEX(Table2,0,1)) to lock the reference to the first column instead of a column name.
Can I use INDEX with a different table name and column position?
Yes. Replace Table2 with your actual table name. The formula =INDEX(TableName,0,1) returns the entire first column; change 1 to another number to return a different column position.
Does INDEX(Table2,0,1) include hidden or filtered rows?
INDEX returns the entire first table column, including rows hidden by filtering. If you need to count only visible rows, wrap the result in SUBTOTAL or AGGREGATE.
Where can I find or change the table name in WPS Spreadsheets?
Click any cell inside the table, then open the Table Design tab. The Table Name box shows the current name and lets you change it before using it in INDEX.
