In Excel, if you want to change columns to rows, you can use the TRANSPOSE function. With this function, all you need to do is select a range with equivalent cells from where you want to add the changed data, refer to the range, and use CONTROL + SHIFT + ENTER.
In the following example, you have a list of names and ages. And we need to change these two columns into rows.
Steps to Change Columns to Rows
- First, in the range D1:H2, enter the TRANSPOSE function.
- Next, refer to the range A1:B6.
- After that, type the closing parentheses.
- In the end, press and hold CRTL + SHIFT and press ENTER.
=TRANSPOSE(A1:B6)As you can see in the above example, we have successfully converted our columns into rows.
To understand how the transpose function works, you need to understand that it takes the array (range) you specify into it and then converts vertical cells into horizontal cells and vice versa.
But there is one thing that you need to take care of that you need to select the same number of cells oppositely. And you need to use CONTROL + SHIFT + ENTER to make this function work.
Here’s one more thing, once you create TRANSPOSE works in a dynamic way, once you change a value in any of the cells from the original cells, that value will also be changed in the transposed range.
And if you want to change the transposed range into values and don’t want to connect it with the original values, you can use paste a special value, or select the range using the keyboard shortcut Ctrl + C and then Alt + V ⇢ V.