# How to transpose in excel ?

#### contactshubhamyadav

##### New Member
Hello everyone,

I am new to this forum and learning excel.

I need some help in excel for transposing cells as shown in the screenshot.

Please can someone help?

Best regards,
Shubham

#### bosco_yip

##### Excel Ninja
Here is a formula solution for your reference only

In A11 array ("Ctrl+Shift+Enter") formula copied right to D11, and all copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,TRANSPOSE(\$A\$3:\$A\$7)&"</b><b>"&TRANSPOSE(\$B\$3:\$B\$7)&"</b><b>"&TRANSPOSE(\$C\$3:\$C\$7)&"</b><b>"&TRANSPOSE(\$D\$3:\$I\$7))&"</b></a>","//b["&(ROW(\$A1)*4+COLUMN(A\$1))-4&"]"),"")

I think it will be easier to solve it by PQ in the "unpivot way", of which I would like to ask for @GraH - Guido , @AlanSidman or @p45cal to give you a help.

#### Attachments

• 12.9 KB Views: 3
Last edited:

#### contactshubhamyadav

##### New Member
Thankyou very much @bosco_yip

I am trying the same formula but I dont know what mistake I am doing.

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,TRANSPOSE(\$A\$3:\$A\$5997)&"</b><b>"&TRANSPOSE(\$B\$3:\$B\$5997)&"</b><b>"&TRANSPOSE(\$C\$3:\$C\$5997)&"</b><b>"&TRANSPOSE(\$D\$3:\$J\$5997))&"</b></a>","//b["&(ROW(\$A1)*5994+COLUMN(A\$1))-5994&"]"),"")

#### vletm

##### Excel Ninja
contactshubhamyadav
Did You press Ctrl+Shift+Enter as asked?
Why do have there two times 5994? ... use four (4) as asked.

#### AlanSidman

##### Well-Known Member
Look at this video to learn how to unpivot your data

#### p45cal

##### Well-Known Member
Borrowing @bosco_yip 's file, PQ solution at cell F10 next to his solution for comparison.

#### Attachments

• 22.5 KB Views: 2

#### bosco_yip

##### Excel Ninja
contactshubhamyadav
Did You press Ctrl+Shift+Enter as asked?
Why do have there two times 5994? ... use four (4) as asked.
Further to Vletm's explanation, here is some more information.

Extract the last part of the formula, it is always in :

=ROW(\$A1)*4+COLUMN(A\$1)-4

Because it is suit with your designed 4 result columns

4 means a serial number will generate in starting from 1 & increase (1,2,3,4,5,6,7,8,9......) in a 4 columns x N rows formula cell areas, something like this :