# How to transpose in excel ?

##### 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.

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:

##### 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
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