Share via

How to transpose from 1 row into columns of 4 with formula instead of copy & paste?

Zen 0 Reputation points
2025-07-22T05:12:48.0233333+00:00

User's image

User's image

Microsoft 365 and Office | Excel | For business | Windows
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. riny 20,530 Reputation points Volunteer Moderator
    2025-07-22T05:21:07.31+00:00

    Provided you are on a modern Excels version, the WRAPROWS function will work for you.

    Screenshot 2025-07-22 at 07.20.28

    0 comments
  2. Daniel-Vo 1,500 Reputation points Microsoft External Staff Moderator
    2025-07-22T08:31:40.35+00:00

    Dear Zen,

    Good day to you and thanks for your post in Microsoft Q&A. I'm sorry for the inconvenience you've encountered. Regarding your requirements, I would like to give you some tips that you can consider: Excel version 2016 and earlier versions:

    =INDEX($A$1:$A$16, (ROW()-ROW($C$1))*4 + COLUMN()-COLUMN($C$1)+1)

    enter image description here

    • How does it work:
      • $A$1:$A$16: Fix ranged contains data in column
      • ROW()-ROW($C$1): Calculates the number of rows relative to the starting cell (here C1, returning 0 for the first row).
      • COLUMN()-COLUMN($C$1): Calculates the number of columns relative to the starting column (returning 0 for the first column).
      • (ROW()-ROW($C$1))*4 + COLUMN()-COLUMN($C$1)+1: Calculates the position in column A based on:
        • Each row in the result represents 3 positions in the original column.
        • Each column in the result increases by 1 position.
      • INDEX: Gets the value from column A at the calculated position.
      • After having result in first cell, cursor to the right to have row and then down to have column Excel 365 or version 2021:
    =WRAPROWS(A1:INDEX(A:A, COUNT(A:A)), 4)

    enter image description here

    • How does it work:
      • This automatically grabs all non-blank cells in column A.
      • Blank cells: If the column has blank cells, COUNTA will ignore them, but you need to make sure the data is continuous from A1 if using a fixed range.

    Once again, I sincerely apologize for the unpleasant experience. I look forward to your information and if you have any questions, please feel free to reach out to me - I’ll be happy to assist you. Wishing you a wonderful day.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  3. Ashish Mathur 100.8K Reputation points Volunteer Moderator
    2025-07-23T00:25:55.8433333+00:00

    Hi,

    In cell G2, enter this formula

    =TOCOL(A2:D3)

    Hope this helps.

    User's image

    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.