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:

=Transpose(array)

In the following sample spreadsheet, we are going to transpose the table that contains the volume of animal exports by county.

First, you must know how many rows and columns your original table contains. Then, select the exact number of cells as the original range of cells, but in the other direction.

For instance, we have 5 rows and 3 columns, so we 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.

Select a range of blank cells and enter the formula: =TRANSPOSE(A1:D5), but don’t hit the ‘Enter’ key yet! At this point, your Excel will look similar to this:

Now, press ‘Ctrl + Shift + Enter’. Once, the key combination is pressed, the data will be transposed.

Remember, an array formula must always be finished by pressing ‘CTRL + SHIFT + ENTER’. When you press the key combination, it will put a set of curly brackets around the formula in the formula bar so that the result will be treated as an array of data and not a single cell value.

Excel Transpose function only copies the data, 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 range of cells with one or more empty cells, those cells will have zero values when transposed, 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 empty or not. If the cell is blank/empty, it returns an empty string (“”) to the transposed table.

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 original table, 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.