In MS Excel, some users prefer the formation of data vertically in columns while others choose horizontal arrangements in rows. In situations when you need to immediately modify the orientation of a given range, **TRANSPOSE **is the function to use. In this article, we will see how to use the **TRANSPOSE **function in Excel.

**Table of Contents**hide

## Download the Practice Workbook

**TRANSPOSE Function in Excel (Quick View)**

**Excel TRANSPOSE Function: Syntax & Arguments**

**Summary**

This function converts a vertical range of cells to a horizontal range or vice versa.

**Syntax**

`=`

`TRANSPOSE`

` (array)`

**Arguments**

Argument |
Required or Optional |
Value |
---|---|---|

array |
Required | Pass the array or range of cells to transpose. |

**How to Use the TRANSPOSE Function in Excel (5 Ways)**

**Example 1: Rotate Data from Rows to Columns or Vice Versa Using TRANSPOSE Option**

Let’s have a dataset of sales information with salesperson names and the months. All the data are in a vertical formation. Now our task is to rotate the whole data into horizontal formation by converting their rows to columns. For this operation, we will use the **TRANSPOSE **option in Excel.

**Step 1:** Select the dataset and copy them (Shortcut **CTRL **+ **C**)

**Step 2: **Now select a new location from where you want to start your new table. Then right-click on the mouse and select the **Transpose **option from the **Paste Options**

**Step 3: **After clicking on the Transpose option all the data will be formatted vertically

**Step 4:** Now if you want to make this horizontal format to vertical again, then again copy the dataset

**Step 5: **Select a new location and select the **Transpose **option from **Paste Options**

**Step 6: **After clicking on the Transpose option all the data will be formatted as the main dataset

**Example 2: Use Formula Using TRANSPOSE Function to Copy or Paste Data**

If data are arranged horizontally or vertically in a worksheet, we can use the **TRANSPOSE **function to list the data vertically, in a different location. And we can use the function as a formula in two ways with a dynamic array formula. Let’s see them one by one.

For this, we will use the same dataset above. We will convert them into a horizontal format using a dynamic array formula with the **TRANSPOSE **function.

**Step 1: **Enter the following formula in cell **B14 **and press **ENTER**

`=TRANSPOSE(B3:H11)`

**Step 2: **Then all the data will be transformed from vertical to horizontal formation

**Example 3: Transpose Table without Zeros Using TRANSPOSE Function**

If we transpose any data table in Excel and if there is any blank cell on that table, after transposing it automatically replace the blank cells with zeros.

Now our task is to transpose the table without zeros Using the **TRANSPOSE **function. Our dataset will be like this:

**Step 1: **Enter the following formula in cell **B14 **and press **ENTER**

`=TRANSPOSE(IF(B3:H11="","",B3:H11))`

**IF(B3:H11=””,””,B3:H11)**here**B3:H11=””**this is our condition. We are checking if there are any blank cells in the given range or not. If there is any blank cell then print**“”**(blank cell), otherwise print values from**B3:H11**this range. To explore more about IF function, you can visit this link- After that, if there are no blank cells then the
**TRANSPOSE**function transposes the data using the**B3:H11**dataset.

**Step 2: **Now as we can see all the blank cells have remained the same as the source table

**Example 4: Manipulate Data at the Time of Transposing Using TRANSPOSE Option**

Let’s we have a dataset of two team player’s name. The dataset is horizontally oriented. Now our task is to transpose the dataset vertically and rewrite all the names like this formula “**Team Name – Player Name**”.

**Step 1:** First we will go for **Team A**. Select the data from Team A only and copy them

**Step 2:** Select cell **B9 **and right-click on the mouse. Select **Paste Special **from the options

**Step 3:** A new window named **Paste Special **will pop up. Click on the **Paste Link **button

**Step 3:** Now the copy of the selected range will be created. Now open the Find and Replace option (Shortcut **CTRL **+ **F**). Then follow the procedure:

- Go to
**Replace**tab - Write = (equal sign) in
**Find what**option and Write “Team A -” As**Replace**with option - Lastly, click on
**Replace All**button

**Step 4: **All the data will be replaced as per our need

**Step 5: **Now copy the selected data again (**B15:F15**) and go to cell **B9. **Select the **Transpose **option under **Paste Special**

**Step 6: **All the data will be transposed vertically

**Step 7:** Do the same process for **Team B** also

**Example 5: Use TRANSPOSE Function in Formula to Calculate Highest Total Salary for the Following Years**

Let’s have a dataset of employee’s salary records. All the datasets are organized in the horizontal position. Now our task is to find out the last 4 years’ highest salary summation using a formula. Let’s consider we will get the highest total salary for the following years for the last person **Shawn Richards**.

**Step 1:** Enter the following formula in cell **C13 **and press **CTRL **+ **SHIFT **+ **ENTER**

`=MAX(MMULT(C8:I8,--(ABS(TRANSPOSE(COLUMN(C8:I8))`

`-COLUMN(OFFSET(C8:I8,0,0,1,COLUMNS(C8:I8)-B13+1))`

`-(B13-1)/2)<B13/2)))`

**Formula Explanation**

- The formula tests the ranges to see if there are enough consecutive COLUMNS. The results of those tests (1 or 0) are multiplied by the cell values, to get the total salaries.
**TRANSPOSE**function is used to do all the calculations after converting the rows into columns.**MMULT**function is used to return the matrix product of two arrays.**MAX**is to find out the maximum salary from the given dataset.- Using two minus signs (–) next to each other causes the formula to convert a return value of “TRUE” into 1 and a return value of “FALSE” into 0.

**Step 2: **Then the total highest salary sum will be shown

**Things to Remember**

Common Errors |
When they show |
---|---|

Formatting problem | TRANSPOSE does not carry over formatting. |

#VALUE! | If the formula has not been entered as an array formula. To solve this, the formula needs to be entered with CTRL+SHIFT+ENTER |

**Tips**

- If you need text and cell formatting to be transposed as well, then try copying, pasting, and using the Transpose option. But keep in mind that this creates duplicates. So, if your original cells change, the copies will not get updated.
- You don’t have to type the range by hand. After typing
**=TRANSPOSE(**you can use your mouse to select the range. Just click and drag from the beginning of the range to the end. But remember to press**CTRL**+**SHIFT**+**ENTER**when you are done, not**ENTER**by itself. - To remove a
**Transpose**formula from your worksheet, select the whole range referenced in the formula, and press the Delete key. - As an array function, TRANSPOSE does not support modifying part of the array it returns. To edit a Transpose formula, select the entire range the formula refers to, make the desired change, and press
**CTRL**+**SHIFT**+**ENTER**to save the updated formula.

**Conclusion**

These are some ways to use the **TRANSPOSE **function in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.