Learn how to transpose data in Excel and quickly shift the orientation of your columns and rows on a worksheet.
Transposing data in Excel means switching or rotating data from the horizontal array to vertical array, or from vertical array to horizontal array. In simple terms, the TRANSPOSE function changes rows to columns and columns to rows.
In Excel, you can transpose data in two different ways, using the TRANSPOSE function and Paste Special method. In this article, we will show you how to switch the row value into column value and column value into row value in Excel.
Using TRANSPOSE Function
TRANSPOSE function allows you to switch the orientation of a given cell range (an array) from vertical to horizontal or vice versa. Since it’s an array formula, the function must be entered in a range that has the same number of rows and columns as the source.
The syntax for Transpose function is:
In the following example, we are going to transpose the table that contains the volume of animal exports by county.
First, find out how many columns and rows your source table contains. Then, select the exact number of cells as the original set of cells, but in the other direction.
For example, if you have 5 rows and 3 columns, you need to select 3 rows and 5 columns of cells, for the transposed data. In our case, we have 5 rows and 4 columns, so you need to select 4 rows and 5 columns.
With a range of blank cells selected, enter the formula:
=TRANSPOSE(A1:D5), but don’t hit the ‘Enter’ key! At this point, your Excel will look similar to this:
Remember, an array formula must always be finished by pressing ‘CTRL + SHIFT + ENTER’. By doing this, the formula bar puts a set of braces around the formula, so that the output will be an array of data and not a single cell.
Now, press ‘Ctrl + Shift + Enter’. Once, the key combination is pressed, the data will be transposed.
Transpose function only copies the values, not the formatting of the original data. As you can see, the formatting of the original header is not copied. Also, Transpose is a dynamic function, when your value in original data is modified, it will be reflected in the transposed data, but not vice-versa.
How to Transpose Data in Excel Without Zeros
If you transpose a table or array of data with one or more empty cells, those cells will have zero values in the transposed table, as shown in the picture below:
In order to copy the empty cells while transposing, use the IF function inside your TRANSPOSE formula to check whether a cell is blank or not. If the cell is blank, it returns an empty string (“”) to transpose.
If your table has empty cells, enter the formula:
=TRANSPOSE(IF(A1:D5="","",A1:D5)) in the selected cells.
Then, press the ‘Ctrl + Shift + Enter’ key combination. Now, the blank space in cell D5 is copied to the transposed cell E11.
How to Transpose in Excel using Paste Special Method
Another way you can transpose data in Excel is by using the Paste Special method. But transposing data using Paste Special method comes with a con and a pro. This feature allows you to transpose data with the original content formatting but the transposed data will be static.
Unlike the TRANSPOSE function, if you change a value in the original data, it will not reflect in the transposed content when using the Paste Special method.
First, select the range that you want to transpose, and then right-click and select ‘Copy’ or press ‘CTRL + C’ to copy the table.
Next, select the range of cells that matches the exact size of the table you’re transposing, as you did for the previous method.
Then, right-click on the selected cells and click ‘Paste Special’.
In the Paste Special dialog box, check the ‘Transpose’ checkbox and click ‘OK’.
Now, the table will be transposed (copied) to selected cells.
In this method, you don’t have to worry about black cells becoming zeros in the transposed table as the empty cells will also be copied to the selected cells.