How to split a single row into multiple columns in Excel

Question

In Excel, I want to split up the data in row 1 into four single columns. A1:D1 should remain in place. E1:H1 should become A2:D2 and so on.

I am currently using this formula in A2:

INDEX($1:$1;(ROW()-1)*4+COLUMN()-1)

... But I am getting a #REF! error.

How do I solve this problem?

David Zemens · Accepted Answer · 2013-05-11 01:50:12Z

4

Try using this formula in Cell A2 (you may need to use semicolon instead of commas in non-English version of Excel)

=OFFSET(A$1,0,(ROW()-1)*4)

Then, copy & paste or drag the formula across columns B thru D, as many rows as you need.

enter image description here

Alternatively, your formula (=INDEX($1:$1,(ROW()-1)*4+COLUMN()-1)), is not returning an error for me, however it is not returning the correct column. If you prefer the Index function you can use:

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

Substitute semicolons for commas if needed.

edited May 11, 2013 at 1:50

answered May 11, 2013 at 1:43

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Zemens Over a year ago

@Aarav you're welcome! Do consider "accepting" the answer if it has helped you. Cheers.

Collectives™ on Stack Overflow

How to split a single row into multiple columns in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related