How to Unpivot Excel Data Using Custom LAMBDA Functions
Question details
The user wants to transform a wide, human-readable table with merged cells into a long database-style format using custom LAMBDA functions instead of Power Query.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Transforming and restructuring wide tabular data with merged headers into a normalized database format for analysis.
- Observed behavior
- Wide tables require restructuring into unpivoted formats directly via cell formulas while handling errors, removing blanks, and processing complex header configurations.
Ensure your spreadsheet software is updated to a modern version that supports dynamic array functions and LAMBDA, and always back up your original dataset before applying bulk data transformations.
Apply a Custom UNPIVOT_PLUS LAMBDA Function
Define and use a custom LAMBDA formula in the Name Manager to dynamically unpivot data arrays directly in the spreadsheet grid.
By defining a custom LAMBDA formula (such as UNPIVOT_PLUS) in the Name Manager, you can pass arguments like the source table range, identifier columns, header rows, and behavior flags. This circumvents the need to open Power Query for straightforward structural transformations and allows the data to refresh instantly on the grid.
Navigate to the Formulas tab on the top ribbon and click on 'Name Manager'.
Click 'New' to create a new named range. Name it 'UNPIVOT_PLUS' and paste your fully developed LAMBDA formula code into the 'Refers to' field, then save.
In a blank area of your worksheet, type `=UNPIVOT_PLUS(source_range, id_columns, header_rows, "Attribute", "Value")`, replacing the arguments with your actual table references.
Press Enter. The dynamic array will calculate and spill the unpivoted, long-format database table into the adjacent cells automatically.
Use SPLIT_INJECT for Column Expansion
Use a companion LAMBDA function to split delimited columns and expand them while preserving the original field structure.
Unpivot and Transform Data Seamlessly in WPS Spreadsheets
WPS Spreadsheets provides robust support for modern dynamic arrays and advanced formulas. Whether you rely on intricate LAMBDA structures or prefer built-in data transformation tools, WPS handles complex data models with ease.
- 1. Open Your Data: Launch WPS Spreadsheets and open the .xlsx workbook containing your wide tabular data.
- 2. Define Custom Formulas: Go to the Formulas tab, select Name Manager, and define your LAMBDA unpivot logic.
- 3. Apply Array Transformations: Type your newly defined formula into the grid to instantly unpivot and restructure the data.
- 4. Save with Full Compatibility: Save your work in the standard .xlsx format to ensure your custom functions remain compatible across platforms.
Frequently Asked Questions
Do I need Power Query to unpivot data?
No. By utilizing custom LAMBDA functions or modern dynamic array formulas, you can restructure and unpivot your data directly within the worksheet grid without ever launching Power Query.
Can custom LAMBDA functions handle merged header cells?
Yes. A well-designed custom LAMBDA function can include logic for fill-down behaviors, allowing it to correctly interpret and unpivot data that falls under merged column or row headers.
Why does my dynamic array return a #SPILL! error?
A #SPILL! error occurs when there is existing text, formatting, or data blocking the range where the LAMBDA function is attempting to output the unpivoted results. Clear the adjacent cells to allow the data to expand.
Will my LAMBDA function save correctly in an .xlsx file?
Yes. Custom LAMBDA functions and named formulas are completely supported and saved natively within the standard .xlsx format, ensuring they function correctly in compatible modern spreadsheet applications.
