How to Unpivot Excel Data Using LET and Dynamic Arrays
Question details
The user needs to convert a matrix of task, group, and result columns into a flat, normalized output table using modern spreadsheet functions.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Transforming wide, pivoted matrix data into a flat, normalized tabular format for database storage, pivot tables, or easier downstream analysis.
- Observed behavior
- Data is structured in a wide matrix (pivoted) format and needs to be flattened into a standard list format using dynamic arrays or Power Query tools.
Ensure your spreadsheet software is updated to a recent version that supports modern dynamic array functions like LET, TOCOL, and HSTACK before attempting formula-based unpivoting.
Use LET and Dynamic Array Functions (TOCOL, HSTACK)
Utilize the LET function combined with TOCOL, HSTACK, and LAMBDA to dynamically flatten and unpivot your matrix data into a normalized table.
Dynamic arrays provide a powerful, real-time way to reshape data directly in your worksheet without needing manual updates or external scripts.
Click on an empty cell where you want the upper-left corner of your new, unpivoted table to begin.
Input the dynamic array formula: =LET(v,H3:U16,t,v<>"",λ,LAMBDA(x,TOCOL(IFS(t,x),2)),a,INDEX(A3:G16,λ(SEQUENCE(ROWS(v))),{1,2,4,3,6,7}),HSTACK(TAKE(a,,4),λ(H2:U2),DROP(a,,4),λ(v)))
Modify the range 'H3:U16' to match the actual data values you want to unpivot, and adjust the 'A3:G16' array and column indexes '{1,2,4,3,6,7}' according to your existing row labels.
Press Enter to execute the formula. The data will automatically unpivot and spill across the adjacent rows and columns.
Unpivot Columns Using Power Query
Power Query provides a visual, highly reliable, and no-code method to unpivot data matrices quickly, making it the industry standard for repetitive data shaping.
Effortlessly Manage and Unpivot Data with WPS Spreadsheets
WPS Office provides robust spreadsheet capabilities, including support for modern dynamic array functions and advanced data processing tools, making complex tasks like unpivoting data matrices accessible and seamless.
- 1. Install the Software: Download and install the free version of WPS Office from the official website.
- 2. Open Your Matrix Dataset: Launch WPS Spreadsheets and open the workbook containing the wide data you need to normalize.
- 3. Apply Array Formulas: Click on a blank cell and input your complex LET and TOCOL unpivot formulas to restructure the data.
- 4. Analyze the Results: Press Enter to view the spilled data array, and use built-in PivotTables to further analyze your newly flattened dataset.
Frequently Asked Questions
What does it mean to unpivot data in a spreadsheet?
Unpivoting is the process of transforming data from a wide matrix format—where categories are spread across multiple columns—into a flat, normalized table with fewer columns and more rows. This format is heavily preferred for database storage and PivotTable analysis.
Why am I getting a #NAME? error when using the LET function?
The #NAME? error typically occurs if your version of the spreadsheet software does not support the LET function or newer dynamic array functions like TOCOL and HSTACK. You must ensure your software is fully updated to a version that supports these specific features.
Is Power Query better than dynamic arrays for unpivoting?
Power Query is generally preferred for repetitive, large-scale data cleaning tasks because it processes step-by-step transformations automatically without complex formulas. However, dynamic arrays are excellent for smaller datasets that require instant, real-time in-cell updates.
Can I use LAMBDA functions to create custom unpivot formulas?
Yes, you can define a custom LAMBDA function in your Name Manager to encapsulate complex unpivot logic. This allows you to reuse the logic across your entire workbook simply by calling your custom function name and supplying the necessary data ranges.
