How to Unpivot an Excel Matrix into Combinations and Values
Question details
The user wants to convert a two-dimensional matrix of locations and values into a normalized three-column list containing the first location, second location, and the corresponding intersecting value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transforming data from a cross-tabular matrix format into a flat list for easier database entry or deeper pivot analysis.
- Observed behavior
- The data is currently arranged in a grid with row and column labels, which makes standard database filtering or flat-list referencing difficult.
Ensure your matrix data has clear row and column headers without any merged cells. If you plan to use the formula solution, verify that you are using a version of Excel that supports dynamic array functions (such as Microsoft 365).
Unpivot Matrix using Power Query
This is the most robust and user-friendly method to transform a matrix into a flat list without writing complex formulas.
Power Query features a built-in 'Unpivot' tool that can instantly convert two-dimensional cross-tabulated data into a normalized, flat format. This method is highly recommended as it easily adapts if your matrix expands in the future.
Select any cell inside your matrix data. Go to the Data tab on the ribbon and click 'From Table/Range'. If prompted, confirm the range of your table and ensure 'My table has headers' is checked.
Once the Power Query Editor opens, click the header of the first column (the one containing your row labels, such as the first set of locations).
Right-click the header of that first column and select 'Unpivot Other Columns' from the context menu. This will instantly collapse the rest of the matrix into an 'Attribute' and a 'Value' column.
Double-click the new 'Attribute' and 'Value' column headers to rename them to something more meaningful for your data, such as 'Destination' and 'Distance'.
Click the 'Close & Load' button on the Home tab. The flattened, three-column list will be exported to a new worksheet.
Unpivot using Dynamic Array Formulas
Use a combination of LET, TOCOL, HSTACK, and VSTACK to dynamically unpivot data if you prefer using formulas in Microsoft 365.
Manage Complex Data Matrices Easily with WPS Spreadsheet
WPS Spreadsheet offers a highly compatible and lightweight environment for your data analysis needs. It fully supports advanced array formulas and provides robust tools to manipulate, unpivot, and transform your matrices without the heavy resource usage of other software.
- 1. Open your matrix dataset: Launch WPS Spreadsheet and open your existing .xlsx workbook containing the matrix data.
- 2. Apply data transformations: Utilize the formula bar to input dynamic array functions or use the built-in PivotTable and multiple consolidation ranges to restructure your data.
- 3. Save in standard formats: Save your flattened data securely. WPS fully preserves array functions and pivot layouts in the standard Excel format.
Frequently Asked Questions
What does unpivoting a matrix mean in Excel?
Unpivoting refers to transforming data from a two-dimensional grid or matrix (with row and column headers) into a flat, tabular list of records. This usually results in three columns: the row attribute, the column attribute, and their intersecting value.
Why is my Power Query Unpivot option missing?
The Power Query feature is natively available in Excel 2016 and later versions under the Data tab as 'Get & Transform Data'. If you are using Excel 2010 or 2013, you may need to download and install the free Power Query add-in from Microsoft.
Can I unpivot data without Power Query or Office 365?
Yes, in older versions of Excel, you can use the legacy PivotTable and PivotChart Wizard (accessed by pressing Alt, D, then P). You can create a 'Multiple Consolidation Ranges' PivotTable, and then double-click the Grand Total cell to extract a flat, unpivoted list to a new sheet.
What do the TOCOL and VSTACK functions do in the formula approach?
TOCOL transforms a two-dimensional array or matrix into a single flat column. VSTACK stacks multiple arrays vertically on top of one another. When combined with LET, they allow you to dynamically extract headers and values into a continuous three-column list.
