How to Add Excel Table Names to Data Combined with VSTACK in WPS Spreadsheets
Question details
The user wants to add a source table name column to every row when combining multiple same-structure Excel tables with VSTACK.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has several tables with identical structure and is using VSTACK to combine them; each combined row needs a label showing which source table it came from.
- Observed behavior
- VSTACK alone stacks rows but does not include a column that identifies the source table for each row.
Confirm that all source tables have the same number of columns and are formatted as named Excel tables. Also verify that your WPS Spreadsheets version supports the VSTACK and EXPAND dynamic array functions.
Use EXPAND to Add a Source Name Column Before VSTACK
Use this solution when the source table list is fixed and you want a straightforward formula that labels each row with a hard-coded table name.
The EXPAND function adds an extra column to each table before VSTACK combines them. The added column contains the same source table name in every row, so the final stacked range remains traceable.
Click the cell where you want the combined stacked table to start, such as Sheet1!A1.
Type =VSTACK(EXPAND(t_apples,,4,"t_apples"),EXPAND(t_plums,,4,"t_plums"),EXPAND(t_pears,,4,"t_pears")) and press Enter.
Replace 4 with the total number of columns in your source tables, and replace t_apples, t_plums, and t_pears with the actual table names in your workbook.
Create a Dynamic Source Name Column Without Hard-Coding Table Names
Use this solution when table names may change or when you want the source labels to update automatically from helper cells instead of being typed into the formula.
Combine Excel Tables with VSTACK and Label Source Names in WPS Spreadsheets
Use WPS Spreadsheets' VSTACK and EXPAND functions to combine multiple same-structure Excel tables and automatically add a source table name column, keeping every row identifiable without manual tagging.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the Excel tables you want to combine.
- 2. Confirm table names and column counts: Click each table and check the Table Name shown in the Table Tools tab, then note the number of columns to use in the EXPAND function.
- 3. Enter the VSTACK and EXPAND formula: Select a blank cell and type =VSTACK(EXPAND(t_apples,,4,"t_apples"),EXPAND(t_plums,,4,"t_plums"),EXPAND(t_pears,,4,"t_pears")) and press Enter.
- 4. Verify the source name column: Check the combined range to confirm that the final column lists the correct source table name for each row.
Frequently Asked Questions
What does EXPAND do in a VSTACK formula?
EXPAND increases the number of columns in each source table before stacking. For example, =EXPAND(t_apples,,4,"t_apples") creates a four-column array where every row in the fourth column is labeled t_apples.
Can I use this method if my tables have different column counts?
No. VSTACK requires all source arrays to have the same number of columns. Standardize the tables to the same column count first, then use that same number in each EXPAND function.
How do I make the source name column dynamic instead of hard-coded?
Store each table name on its own sheet using a helper formula, extract it with FORMULATEXT and FIND, and reference the helper cell inside EXPAND and VSTACK. Press F9 to recalculate after changes.
Does this work in both WPS Spreadsheets and Microsoft Excel?
Yes, if your version supports dynamic array functions such as VSTACK, EXPAND, and FORMULATEXT. WPS Spreadsheets supports these functions and can open and save .xlsx files.
Why does my VSTACK formula return an error?
Check that all table names are spelled correctly, the source tables have matching column counts, and the formula uses the correct column number in EXPAND. A #NAME? error may mean your version does not support dynamic array functions; update WPS Office and try again.
