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.
Download the Practice Workbook
TRANSPOSE Function in Excel (Quick View)
Excel TRANSPOSE Function: Syntax & Arguments
This function converts a vertical range of cells to a horizontal range or vice versa.
|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
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
- 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
- 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|
- 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.
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.