How to Convert Repeating Student Subjects into a Matrix in Excel
Question details
The user needs to convert a two-column list containing student names and their subjects into a matrix format where each student occupies a single row and their corresponding subjects are spread across separate columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Transforming repeating tabular data into a flattened horizontal matrix for easier reading, reporting, or analysis.
- Observed behavior
- The user wants to reshape vertical, repeating data (Student, Subject) into a consolidated horizontal matrix without losing data relationships, but native functions may throw nested array errors.
Ensure your data is formatted as an official Excel Table (press Ctrl+T) before starting. This makes it easier to reference the data dynamically in Power Query and prevents issues when new rows are added.
Use Power Query to Group, Transpose, and Expand Data
Power Query is the most reliable method for pivoting this type of text-based data without running into nested array limitations.
By grouping the data by student name and leveraging custom M code to transpose the subject columns, Power Query can dynamically generate a matrix regardless of how many subjects a student has.
Select any cell inside your data table, go to the Data tab on the ribbon, and click 'From Table/Range' to launch the Power Query Editor.
Select the 'Name' column, navigate to the Home tab, and click 'Group By'. In the dialog, group by Name, set the New column name to 'DATA', and choose 'All Rows' as the operation.
Open the Advanced Editor or modify the formula bar for the Grouped step. Change the 'DATA' column logic to transpose the subjects: Table.Transpose(Table.SelectColumns(_, {"Subject"})).
Click the Expand icon (two diverging arrows) on the header of the new 'DATA' column. Select all dynamically generated columns to extract the transposed subjects.
Once the data looks correct, click 'Close & Load' on the Home tab to output the newly formatted matrix into a new Excel worksheet.

Use Dynamic Array Formulas (Excel 365)
For users on Microsoft 365, you can use dynamic arrays like UNIQUE, FILTER, and TOROW to reshape the data, though it requires advanced formula structuring.
Try WPS Office for Seamless Spreadsheet Management
If you are dealing with complex nested array limitations or want to avoid expensive Office 365 subscriptions, consider switching to WPS Office. It provides a lightweight, highly compatible alternative with powerful data processing capabilities.
- 1. Download and Install: Visit the official WPS Office website to download and install the free suite on your device.
- 2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx workbook directly without formatting loss.
- 3. Analyze Your Data: Utilize WPS Spreadsheets' robust formula library and built-in features to easily pivot, transpose, and organize your datasets.

Frequently Asked Questions
Why do I get a 'Nested arrays are not supported' error in Excel?
This error occurs in modern Excel when dynamic array functions (like MAP) attempt to return an array of arrays—for example, trying to spill multiple columns for a single mapped row. Creating a custom LAMBDA helper function is usually required to resolve this.
Can I convert this list into a matrix using a standard PivotTable?
A standard PivotTable summarizes numeric data effectively, but it is not designed to concatenate or display multiple text values (like subject names) across separate columns for a single row without relying on the Data Model and DAX measures.
Does Power Query update automatically when my original data changes?
No, Power Query does not update instantly as you type. To see the updated matrix after altering your source data, right-click anywhere inside the loaded Power Query matrix table and select 'Refresh', or go to the Data tab and click 'Refresh All'.




