Pivot Table Issues

Combine Student Class Rows into Separate Columns in Excel

Elise WilliamsElise Williams Nov 18, 2026 869 views

Question details

Combine multiple class records for each student into one row, with each class in a separate column.

How to Combine Student Class Rows into Separate Columns in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Load the source table

Select the Student and Class data in Excel, choose Data > From Table/Range, and confirm that the table has headers.

2
Group the records

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.

3
Number classes within each group

Choose Add Column > Custom Column. Name it Numbered and enter Table.AddIndexColumn([Records], "Class No", 1, 1, Int64.Type).

4
Expand the numbered records

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.

5
Pivot the class numbers

Select Class No, choose Transform > Pivot Column, and select Class as the Values Column. Under Advanced options, choose Don't Aggregate, then select OK.

6
Return the result to Excel

Choose Home > Close & Load. To update the result after changing the source table, use Data > Refresh All.

Use Power Query to Pivot Each Student's Classes into Columns
Column names matter: The custom formula uses the grouped column name Records. If your source class column has a different name, select that name when expanding and pivoting.
Work with class lists in WPS Spreadsheets

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. 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. 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. 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. 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. 5. Save the workbook: Choose File > Save As and select XLSX to keep the workbook in an Excel-compatible format.
Place each student's classes in separate numbered columnsOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and worksheet controlsWork with large lists without moving each class manually
microsoft office alternative - wps office

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.