How to Automatically Create Excel Table Rows and Columns from Cell Values
Question details
The user needs to automatically generate a structured table with specific rows and columns based on input values, such as creating a dynamic training plan from entered exercise names and set counts.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a dynamic data entry form or training plan where the table structure automatically adjusts to match user-defined parameters.
- Observed behavior
- Standard worksheet functions return dynamic arrays but cannot automatically create or resize a structured Excel Table, requiring a programmed solution instead.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer options in your ribbon to access VBA features.
Use a VBA Macro to Create and Resize Tables
Create a custom VBA script to read the cell inputs and automatically generate a structured Excel Table.
While dynamic array functions can output data, they cannot create or resize a structured Excel Table. VBA allows you to read your input parameters (like rows, columns, and exercise names) to dynamically build the table.
Be careful when automating table creation, as resizing could overwrite or misalign existing data if placed incorrectly.
Go to File > Options > Customize Ribbon and check the Developer option to make macro tools visible.
Click 'Visual Basic' on the Developer tab or press Alt + F11 on your keyboard.
Right-click on your workbook in the Project window, select Insert, and choose Module.
Enter a script that defines your starting cell, reads the row and column counts from your input cells, uses 'ListObjects.Add' to create the table, and optionally clears the input form fields.
Insert a shape or button on your worksheet, right-click it, and select 'Assign Macro' to run your table-generation script with a single click.

Create Dynamic Tables with Macros in WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to seamlessly automate table creation, resize arrays, and build dynamic forms based on cell values.
- 1. Open your Workbook: Launch WPS Spreadsheet and open your macro-enabled (.xlsm) file.
- 2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon to access VBA and Macro features.
- 3. Open the VBA Editor: Click on the VBA Editor icon to write or paste your table-generation code.
- 4. Run your Macro: Execute the script to instantly create your customized rows and columns based on your cell values.

Frequently Asked Questions
Can I create a structured table from cell values without using VBA?
Standard worksheet functions can create dynamic spill arrays (using functions like SEQUENCE), but they cannot automatically generate or resize a formal structured Excel Table (ListObject). VBA or Office Scripts are strictly required to automate structured tables.
How do I prevent new tables from overwriting my existing data?
In your VBA script, you should dynamically calculate the last used row in your worksheet. Use this value to offset the new table's starting position so it is always placed below previously entered data.
Why does Excel restrict 'Format as Table' on dynamic arrays?
Excel restricts the 'Format as Table' feature over dynamic array formulas because dynamic arrays automatically resize based on their formula inputs, which directly conflicts with how Excel manages strict structured table bounds and references.




