# How to Use TRANSPOSE Function in Excel (5 Ways) 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.

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:

1. Go to Replace tab
2. Write = (equal sign) in Find what option and Write “Team A -” As Replace with option
3. 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.  