Combine Student Class Rows into Separate Columns in Excel
Question details
Combine multiple class records for each student into one row, with each class in a separate column.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A large list contains multiple rows per student, and the user wants to reorganize the classes without moving them manually.
- Observed behavior
- The desired output has one row per student and a separate column for each related class.
Arrange the source data in columns with headers such as Student and Class, with one class record per row. Decide the order in which each student's classes should appear and sort the source data accordingly.
Use Power Query to Pivot Each Student's Classes into Columns
Use this method for a large list or a source table you expect to update and refresh.
A standard PivotTable can count classes by student, but it does not normally display class names as text values in numbered columns. Power Query can reshape the records into that layout.
Select the Student and Class data in Excel, choose Data > From Table/Range, and confirm that the table has headers.
In Power Query Editor, select Student, choose Home > Group By, and group by Student. Name the new column Records and select All Rows as the operation.
Choose Add Column > Custom Column. Name it Numbered and enter Table.AddIndexColumn([Records], "Class No", 1, 1, Int64.Type).
Use the expand button on the Numbered column to select Class and Class No. Clear the option to use the column name as a prefix, then remove the Records column.
Select Class No, choose Transform > Pivot Column, and select Class as the Values Column. Under Advanced options, choose Don't Aggregate, then select OK.
Choose Home > Close & Load. To update the result after changing the source table, use Data > Refresh All.

Use Helper Columns and Formulas to List Classes Across a Row
Use formulas when you prefer to keep the reshaped list on a worksheet without Power Query.
Combine Student Classes Across Columns in WPS Spreadsheets
Use helper columns and lookup formulas in WPS Spreadsheets to arrange each student's class records on one row, then save the result in an Excel-compatible format.
- 1. Open the class list: Open the workbook in WPS Spreadsheets. Put student names in column A and classes in column B, with headers in row 1.
- 2. Number each student's records: Enter =COUNTIF($A$2:A2,A2) in C2 and =A2&"|"&C2 in D2. Fill both formulas down to the last source row.
- 3. Make a student list: Copy the student names to column F, select the copied names, and use Data > Remove Duplicates. Enter class numbers 1, 2, 3, and so on across row 1 starting in G1.
- 4. Fill the class columns: In G2, enter =IFERROR(INDEX($B$2:$B$1000,MATCH($F2&"|"&G$1,$D$2:$D$1000,0)),""). Fill the formula across and down, adjusting the row limits to cover the source list.
- 5. Save the workbook: Choose File > Save As and select XLSX to keep the workbook in an Excel-compatible format.

Frequently Asked Questions
Can a regular Excel PivotTable show each class name in a separate cell?
A regular PivotTable can show class names as column labels and count records, but its Values area does not normally display class names as text. Use Data > From Table/Range and pivot an indexed class column in Power Query for the requested layout.
How do I update the Power Query result when the class list changes?
Add or edit records in the source Excel table, then choose Data > Refresh All. The query will regroup and pivot the updated records.
What happens if one student has more classes than the others?
Power Query creates a numbered column for each class position found in the data. With the formula method, add enough numbered headings across row 1 and fill the lookup formula into those columns.
Will repeated class names be kept?
Yes. Both methods number each source record for a student, so repeated class names remain separate entries unless you remove duplicates from the source data.




