Share via

Hi I have a list of students (row) and a list of classes (Column) but i want to change it so each student has a separate row for each class. so as in the before and after below

Anonymous
2025-07-09T14:05:43+00:00

Hi I have a list of students (by row) and a list of classes (by Column) but i want to change it so each student has a separate row for each class. It is currently 250 columns wide but each student will only have 3 or 4 classes. I have put a small example of what i need below - is there anyone who can help me with this and give me a quick fix please, preferably without vba as i am not used to using this.

Thank you

What i have now

What i want to achieve

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Andreas Killer 144.1K Reputation points Volunteer Moderator
2025-07-09T14:46:45+00:00

Create a named range "Data" as shown or format the data as table.

Image

Data \ Get Data \ From Table/Range

Right-click the first column and choose "Unpivot other columns"

Rename the columns

Close and Load

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

Sample file:

https://www.dropbox.com/scl/fi/xuddwlr2q1u1zj7k5nmif/fb40b1b8-e84b-4ef3-a36a-30bbaa884835.xlsx?rlkey=mfwqj43hhgmjh7bq4xgc2hw5v&dl=1

Andreas.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2025-07-09T14:44:56+00:00

Hi Vicky Naylor, 

Welcome to Microsoft Community.  

Thank you for such a valuable question. I’m happy to assist you with your inquiry about creating separate rows in Excel.   

You can simply resolve this by using Excel Power Query feature: 

  • Select your entire data table (including headers).
  • Go to the Data tab.
  • Click "From Table/Range" (make sure your data has headers).

Image

  • Power Query will open. In there:  
    • Select all the columns except the Student column(Ctrl/Shift + Click on selected columns).
    • Click on the Transform tab > Unpivot Columns.
    • You’ll see three columns: Student, Attribute (which is your class name), and Value.

Image

  • Rename(Optional):  
    • Attribute → Class
    • Remove the Value column (if it's just a placeholder like "X").
    Image
  • Click Close (X button on the upper right corner) & Keep to export it back to Excel.

Image

  • You will see the result appear in the new sheet: Image

Note: Some features might place in different locations due to different in Excel versions, but usually they are in the same Tab. 

Feel free to reply if you need any further assistance. Thank you for reaching out to us! 

Best Regards,     

Tamara-Hu- MSFT | Microsoft Community Support Specialist.

Was this answer helpful?

1 person found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-07-10T07:03:51+00:00

    Thank you , this worked perfectly :)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-07-10T07:01:57+00:00

    Thank you so much :)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-07-09T15:06:09+00:00

    In cell G2:

    =TEXTSPLIT(TEXTJOIN("",, REPT(A2:A5 & ",", BYROW(B2:E5, LAMBDA(a, COUNTIF(a, "Y"))))),, ",", TRUE)

    In cell H2:

    =TEXTSPLIT(ARRAYTOTEXT(BYROW(B2:E5, LAMBDA(a, ARRAYTOTEXT(INDEX(B1:E1, TOCOL(SEQUENCE(, COLUMNS(a)) / (a = "Y"), 3)))))),, ", ")

    In cell I2:

    =TEXTSPLIT(REPT("Y,", COUNTIF(B2:E5, "Y")),, ",", TRUE)

    Or you can use this single formula:

    =LET(d, B2:E5, HSTACK(TEXTSPLIT(TEXTJOIN("",, REPT(A2:A5 & ",", BYROW(d, LAMBDA(a, COUNTIF(a, "Y"))))),, ",", TRUE), TEXTSPLIT(ARRAYTOTEXT(BYROW(d, LAMBDA(a, ARRAYTOTEXT(INDEX(B1:E1, TOCOL(SEQUENCE(, COLUMNS(a)) / (a = "Y"), 3)))))),, ", "), TEXTSPLIT(REPT("Y,", COUNTIF(d, "Y")),, ",", TRUE)))

    Arrange the range in formula as per you need.

    Hope this helps.

    Was this answer helpful?

    0 comments