How to Split Excel Rows into Multiple Rows Using Dynamic Array Formulas
Question details
The user needs to unpivot a row-based dataset into multiple output rows by repeating key fields and converting horizontal arrays into single columns.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Transforming wide, cross-tabular datasets into a normalized, flat vertical format for database import or advanced data analysis.
- Observed behavior
- The goal is to automatically unpivot the data range using modern dynamic array functions rather than relying on manual copy-pasting or VBA macros.
Ensure you are using a spreadsheet version that fully supports dynamic array functions (such as Microsoft 365 or the latest WPS Office version), as functions like TOCOL and HSTACK are not available in older legacy versions.
Use the LET, HSTACK, and TOCOL Functions to Unpivot Data
You can dynamically unpivot row-based data into vertical columns by defining data ranges with the LET function and reshaping the arrays using TOCOL and HSTACK.
This formula-based approach eliminates the need for Power Query or VBA. By utilizing the LET function, you can assign variables to your data block, headers, and identifiers, making the overall formula process much more efficient and readable.
Select an empty cell with enough surrounding space for the new array to spill. Begin your formula by defining the entire source range, for example: =LET(tbl, A1:H4,
Continue the formula by separating the dataset components. Extract the numerical data area by dropping headers: data, DROP(tbl, 1, 1). Next, extract the row identifiers: code, DROP(TAKE(tbl,, 1), 1). Finally, extract the column headers: attr, DROP(TAKE(tbl, 1),, 1).
Combine the elements by appending the HSTACK function. Use TOCOL and SEQUENCE to correctly repeat the identifiers alongside the unpivoted data values: HSTACK(TOCOL(IF(SEQUENCE(, COLUMNS(data)), code)), TOCOL(IF(SEQUENCE(ROWS(data)), attr)), TOCOL(data))
Add the final closing parenthesis for the LET function and press Enter. The complete dynamic array will automatically spill downwards, displaying your unpivoted dataset.
Manage Dynamic Array Formulas Seamlessly in WPS Spreadsheet
WPS Spreadsheet offers comprehensive support for modern data manipulation techniques. You can easily unpivot and restructure complex datasets using the exact same dynamic array functions without needing to switch software.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the row-based data you need to split.
- 2. Input the unpivot formula: Select an empty cell and enter your =LET(...) dynamic array formula to define and reshape your data.
- 3. Press Enter to execute: Hit Enter. WPS Spreadsheet will automatically calculate the logic and spill the unpivoted columns into the adjacent blank cells.
- 4. Adjust and format headers: If needed, manually add new column headers above the spilled array and adjust the column widths to finalize your report.
Frequently Asked Questions
Why does my dynamic array formula display a #NAME? error?
The #NAME? error typically appears if your spreadsheet software version does not support the functions used in the formula, such as LET, TOCOL, or HSTACK. You need to ensure you are using Microsoft 365, Excel 2021, or the latest version of WPS Office.
What exactly does the TOCOL function do when unpivoting data?
The TOCOL function takes an entire two-dimensional array or cell range and forces it into a single vertical column. This is a critical step in unpivoting, as it converts a wide grid of values into a long, flat list.
Can I unpivot data without using complex formulas?
Yes. If you prefer a non-formula approach, you can use the Power Query feature found in modern spreadsheet applications. By selecting your data and choosing 'Unpivot Columns' within the Power Query editor, you can achieve the exact same flat-table result through a visual interface.
