Excel Transpose Function

While working with Excel, many times, you require switching or rotating cells in your worksheet. The hard way of achieving this is by copying, pasting the data to the required cells. This way, it is time-consuming. The other way round of switching and rotating your cells is using the Excel Transpose function. But this function formulates duplicated data. If you don’t want the duplication in your worksheet, you can type a customized formula instead of using the inbuilt TRANSPOSE function.

For instance, in the following reference image, the function =TRANSPOSE (A24:B44) takes the range of cells A24: B27 and organizes them horizontally (refer to the below screenshot).

What is TRANSPOSE function?

The Excel TRANSPOSE() function copies the data vertical range of cells to a horizontal range of cells, or vice versa (i.e., changes the orientation of horizontal range to vertical range range).

In simple terms, we can say this function “flips” the placement of a given range of cells or Excel array:

1. When you work with a vertical range of cells, the TRANSPOSE() function changes the orientation of vertical range to horizontal range.
2. When you work with a horizontal range of cells, the TRANSPOSE() function changes the orientation to a vertical range.

In other words, when an array is transposed, the first row is flipped, and it becomes the first column of the new array; similarly, the second row of the previous array becomes the second column of the new array, the third row will be converted as the third column, and so on.

In Excel, the TRANSPOSE() function can be used with both cell ranges and arrays. Though users prefer the use of Transposed ranges as they are dynamic, i.e., if data in the original range changes, the TRANSPOSE() data will immediately update data in the new range.

Syntax

Parameters

array (required) – This parameter represents the data which you want to change from vertical to horizontal or vice-versa.

Return Value

The TRANSPOSE function returns a range of cells or array by converting the horizontal range of cells as a vertical range or vice versa.

Steps to use the Transpose Function

1. The selected transpose range must be equal to the source range.
2. The function that you will be entering must be typed as an array formula.
3. To specify an array formula, you should select all the cells where the formula is required.
4. After selecting the cells, type the formula, i.e., =TRANSPOSE(A1:A5).
5. Finally, press Ctrl +Shift+ Enter to confirm it.
6. If you want to make any changes to the TRANSPOSE formula, you must highlight the entire source array, you can make the edits, and once done again, press the Ctrl +Shift +Enter keyboard shortcut to confirm the changes.
7. Once you confirm, you will notice that Excel will automatically put curly braces ” { } ” surrounding the function. The curly braces indicate that it is an array formula.

Example 1: Use TRANSPOSE function to change the cells from vertical to horizontal

Follow the below steps to solve the above problem:

Step 1: Select blank cells same size as the original range

The first step is to select blank cells the same size as the original range in your Excel worksheet. But always remember to select the same size cells in the other direction. For instance, in the below example we have twelve cells here that are arranged vertically:

So, we will select twelve horizontal cells, as shown in the below image:

These blank cells are the location where are transposed cells will be placed afterwards.

Step 2: Type the formula i.e., =TRANSPOSE(

After you select the blank cells, just type the formula: =TRANSPOSE(

It will look similar to the below image:

NOTE: You will notice that those nine horizontal cells are still selected in your Excel worksheet even though you have started typing the TRANSPOSE function.

Step 3: Enter the range of cells you wish to transpose

The next step is to type the range of the cells you want to transpose. Although instead of typing you can point and drag your mouse cursor to select the group of original cells.

In this example, the original cells are positioned from A23 to C26. Therefore our formula becomes: =TRANSPOSE (A23: C26). After entering the range you will notice that the entire array is highlighted with a blue rectangle box.

It will look similar to the below image:

NOTE: After typing the formula keep in need that you don’t press ENTER yet as it will show the #VALUE error. Stop typing, and move to the last step.

Step 4: Press CTRL+SHIFT+ENTER together

The last step is to press CTRL+SHIFT+ENTER keyboards buttons together and Excel will immediately transpose the original cells for you. Below given is the output after transposing the original cells using TRANSPOSE() function.

NOTE: You will notice that curly brackets are automatically added to the beginning and end of your TRANSPOSE formula, to indicate that it is array function.

Question: Now the question arises why it only works by pressing CTRL + SHIFT +ENTER?

Answer: The answer to this question is because the TRANSPOSE function is only used in an array formula, and that’s how you terminate an array function. An array formula is applied to multiple cells. If you recall, in step 1, you have selected multiple cells; therefore, the formula will be applied to more than one cell.

Example 2: Change Orientation of a 4 row x 6 column Dynamic Array (given below) using the TRANSPOSE function.

Follow the below steps to transpose the above 4 row x 6 column Dynamic Array :

Step 1: Select the blank cells

To transpose a 4 row x 6 column horizontal range to a 6 row x 4 column vertical range select 24 blank cells (6 row and 4 columns). In this example, we have selected the range of cells B4:C7.

Step 2: Type the formula and enter array of range of cells

After selecting the blank cells, just type the formula and select the range of cells you flip. You will have the following formula.

=TRANSPOSE(B3:E8)

NOTE: As soon as you type the formula you will notice a night blue rectangle box covering your array.

It will look similar to the below image:

STEP 3: Press Ctrl+Shift+Enter to fetch the output

The last step is to press CTRL+SHIFT+ENTER keyboards buttons together and Excel will immediately transpose the 4 row x 6 columns original dynamic array to 6 row x 4 column range of cells.

It will look similar to the below image:

Eureka! Now we have understood how the TRANSPOSE function works in an Excel worksheet. You can also integrate this function with other Excel formulas. Go ahead and give it a try.

Points to Remember

1. You need not type the entire range of cells manually. After writing =TRANSPOSE( you can utilize your mouse to select the group of cells. Just point the cursor on the first cell and drag the pointer from the start of the range to the end of the selection. But keep in mind once you are done, don’t press the enter button (it will throw an #value error); instead, press CTRL+SHIFT+ENTER to get the result.
2. The TRANSPOSE () function works with text data, and it copies the cell formatting as well. But always remember that this function creates duplicate data.

TRANSPOSE Alternative- Paste Special Option

The TRANSPOSE function is logical when you require a dynamic solution that will automatically update the flipped output whenever there is any change in the original data. However, Excel provides an easy alternative to the TRANSPOSE function, i.e., the Paste Special Option. It can be very useful when you only need a one-time conversion. You can use Paste Special and can easily flip the worksheet data’s orientation without keeping a link to the source data.

Follow the below steps to use the Paste Special option to transpose your worksheet data:

1. Select the source data and copy it by pressing the CTRL + C keyboard shortcut.
   
2. Put your cursor on the cell from where you want the Transpose operation to begin. In our case, we have selected the B11 cell.
   
3. Go to the Excel Ribbon’s Home tab, and click on the Paste drop-down arrow placed on the extreme left of the clipboard tab.
   
4. The following drop-down paste options will be displayed. Select the TRANSPOSE (T) Paste option.
   
5. Excel will transpose the data will paste it to the selected cell.
   
6. Since we have the same data at two positions, you can delete the original data though this step is optional.

NOTE: Make sure to select a single cell or a group of cells to paste the transpose data. IF the cursor is over the source data cells, Excel will throw an error that, “The information cannot be pasted because the copy and paste area are not same size and shape”