How to Populate Excel Table Rows from Another Table's Headers
Question details
The user needs to copy or extract the headers from a primary Excel table and populate them as rows into a secondary table, while ensuring the adjacent column in the secondary table remains fully editable for manual data entry.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a secondary reference table that relies on the headers of a primary table as its row items, combining automated extraction with manual data entry.
- Observed behavior
- Using Power Query successfully transposes the data but creates a strictly automated output table, which overwrites or prevents manual data entry in the resulting table's adjacent columns upon refreshing.
Ensure both of your datasets are formatted as official Excel Tables (using Ctrl+T) and note the exact name of your source table from the Table Design tab before applying formulas.
Use a Dynamic Array Formula to Transpose Headers
The best way to automatically populate rows from headers while keeping adjacent columns editable is by using the dynamic TRANSPOSE formula.
Unlike Power Query, a dynamic array formula spills its results directly into the worksheet without turning the entire block into a locked query output. This allows you to freely type manual data in the very next column.
Select any cell in your source table, navigate to the 'Table Design' tab on the ribbon, and write down the Table Name located on the far left (e.g., Table1).
In your second table, click the first cell of Column A (where you want the headers to appear vertically) and type the formula: =TRANSPOSE(Table1[#Headers]).
Press Enter. The formula will instantly transpose the horizontal headers into vertical rows, spilling down the column.
Click any cell in Column B next to your new spilled list to manually type your data. The formula will not overwrite your manual entries.
Use Paste Special to Transpose Values Statically
If you do not need the rows to update automatically when the source headers change, you can statically copy and transpose the text to avoid formula limitations entirely.
Easily Transpose Data and Manage Tables with WPS Spreadsheet
WPS Office provides advanced dynamic array formulas and powerful table management features, allowing you to transpose headers and manage manual data entry seamlessly without the strict limitations of heavy data queries.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the source table.
- 2. Locate the destination table: Click on the specific cell in the new table where you want the transposed headers to begin.
- 3. Apply the Transpose formula: Type =TRANSPOSE(Table1[#Headers]) and press Enter to instantly populate the rows.
- 4. Enter manual data seamlessly: Start typing your manual entries in the adjacent column; WPS Spreadsheet safely handles dynamic arrays without locking neighboring cells.
Frequently Asked Questions
Why can't I manually type data into a Power Query result table?
Power Query generates a structured, automated output table connected directly to its data source. Whenever the query refreshes, it completely reconstructs the table, which wipes out or misaligns any manual entries you have made in adjacent columns.
Will the TRANSPOSE formula update if I rename a header in my source table?
Yes. When you use the =TRANSPOSE(Table1[#Headers]) dynamic array formula, any text changes made to the headers in the source table will instantly reflect in the vertical rows of your destination table.
What does the #SPILL! error mean when using TRANSPOSE?
The #SPILL! error occurs when the dynamic array formula does not have enough empty cells below it to display all the transposed headers. To fix this, simply clear any text, spaces, or manual entries in the cells directly beneath the formula.
Can I use this formula method to copy data rows instead of headers?
Absolutely. You can modify the formula to target specific data rows or entire ranges by changing the table reference, such as using =TRANSPOSE(Table1[@Data]) or manually selecting the horizontal range you wish to transpose.
