How to transfer data from some columns to rows while keeping other columns unchanged?

Candy Ip 0 Reputation points
2026-09-13T04:46:12.8066667+00:00

I have data in columns A to F, in which A to C column is the general information of batches of data, D, E & F are data of different columns. I want to make it from row to 3 rows, first row: A+B+C+ 1st combination (i.e.D), 2nd row: A+B+C+2nd (ie info in E) combination, third row: A+B+C+ 3rd combination, (i.e. info in F). But I do not want to manually repeat filling A+B+C for 3 times. What formula or function should I use in this case?

Microsoft 365 and Office | Excel | Other | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. riny 21,520 Reputation points Volunteer Moderator
    2026-09-13T06:25:37.2666667+00:00

    @Candy Ip

    I believe this is what you have in mind:

    User's image

    Personally I would favor unpivoting the data with Power query. But as mentioned in the article linked below, you can do it with a single dynamic array formula that spills the result in one instance. The article explains the technique in depth.

    https://spreadsheetweb.com/how-to-unpivot-data-with-new-excel-functions/

    The Excel formula in A10 is as follows:

    =HSTACK(CHOOSEROWS(Table1[[col1][col3]],QUOTIENT(SEQUENCE(ROWS(Table1[col1])*3,,0),3)+1),TOCOL(Table1[[col4]:[col6]]))

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.