CODEWITHSUNDEEP
excelsplitrowmultiple-columns
Aarav
Aarav

Reputation: 3

How to split a single row into multiple columns in Excel

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?

Upvotes: 0

Views: 17463

Answers (1)

David Zemens
David Zemens

Reputation: 53623

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.

Upvotes: 4

Related Questions