What is Transpose in Excel?
Transpose is a special pasting option that changes Excel rows to columns and vice versa.
In other words, it inverts the copied range before pasting it, so that all the columns become rows and the rows become columns.
There are two ways to change Excel rows to columns or columns to rows. One involves using Past Special Transpose, and the other involves using the TRANSPOSE() function.
Let’s look at how to change Excel rows to columns using these two methods.
Excel Rows to Columns using Past Special
Using the Past Special to transpose simply involves copying and pasting the range, taking into consideration one key setting.
Use the following steps to transpose using Paste Special:
- Select the range you wish to transpose
For instance, if you want to paste a row into a column, first select the range in the row you wish to convert into a column.
- Copy the selected range.
To copy the range, right-click in the range and click copy in the shortcut menu. You can also use the copy shortcut, Ctrl+C to copy the range.
- Click an empty cell in the column to which you want to transpose.
Here, we’ll use cell A2. This way, the leftmost cell (Cell B2) will become the topmost cell (Cell A2).
- Right-click, and then click Paste Special.
The following window will appear:
- Check Transpose
- Now, click Ok to change the row into a column
Below will be the result:
The above example shows you how to transpose a single row.
If there are two or more rows to be transposed, the steps remain the same as shown below:
- Select the rows you wish to transpose
- Again, right-click and select copy. Or press Ctrl+C to copy.
- Now click in an empty cell, right click and select Past Special.
The Past Special windows appears. Check Transpose and click OK.
As you can see, all the rows are changed to columns and all columns to rows.
When you are working with a simple set of data, using the Past Special to transpose is very handy.
Now let’s look at how to perform the same task using a functioin.
Excel Rows to Columns using Transpose function
As mentioned before, one can also use the transpose function to change Excel rows to columns.
The reason why you’ll want to use a function to transpose is that, the transposed data automatically update when the original data is altered. The Past Special transpose only past value and not formulas.
Syntax of the TRANSPOSE function
=TRANSPOSE(array)
The array argument represents the set of data you want to transpose.
This kind of function is called the array function, and it doesn’t work like the other functions.
Thus, if you enter this function and press enter, it’ll result in an error. What you should rather do is that, after entering the function, press Ctrl+Shift+Enter.
Below are the steps one can follow to change rows to columns using the Transpose function:
- Select a range equal to the data.
Unlike other functions, to use the TRANSPOSE function, you need to select the range of cells equal to the range you are transposing.
For example, if you are transposing a range of 8 columns and 4 rows, you’ll have to select a range of 4 columns and 8 rows, because the columns will become rows and rows becomes columns.
See screenshot:
- After the selection, and without clicking anywhere else, start typing the formula with: TRANSPOSE(
- Now, click and drag to select the table. Or just manually enter the range of the data to be transposed.
- Close the parentheses, and press Ctrl+Shift+Enter.
Since this is an array formula, if you use Enter instead of these key combinations, it will result in an error.
At this point, you have successfully changed Excel rows into columns and columns into rows using the TRANSPOSE function.
To see why the function approach is useful, try changing the original data, and you’ll see that the transposed data will also update automatically. This is not possible with the Past Special transpose.