Splitting one row with multiple columns into multiple rows with single columns

Copper Contributor

Device: PC/Windows 10

Excel version 2305 Build 16501.20228


Hi all,

I'm working with a bunch of consult data, and I need every consult in it's own row, while still keeping the rest of the client information. Essentially I'm trying to split one row with multiple columns into multiple rows with single columns, while maintaining the data in the rest of the columns. So, turning Table 1



into Table 2.



I'm trying to do this without VBA, or with very simple beginner VBA, since I don't know any VBA.


Does anyone have any insight?


Thanks so much!

7 Replies


The keyword for your web search is "unpivot".



The preferred option would be to use PowerQuery to unpivot the data, but 365 would allow one to write a problem specific Lambda function to unpivot the table.

= Unpivotλ(

where Unpivotλ contains

= LET(
      recordNumber,     SEQUENCE(ROWS(consultationDates)),
      datesNumbered,    IF(consultationDates <> "", recordNumber, NA()),
      datesStacked,     TOCOL(consultationDates, 1),
      numbersStacked,   TOCOL(datesNumbered, 2),
      otherDataStacked, CHOOSEROWS(otherColumns, numbersStacked),
      HSTACK(otherDataStacked, datesStacked)
I'm not sure I'm at an advanced enough level to implement this, but I appreciate the thoroughness. Thank you for the response!
Thank you, this worked! Much appreciated.
Glad to hear you have solved the problem.
You are welcome.