How to Use TRANSPOSE Function in Excel (5 Ways)

Excel TRANSPOSE Function

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)

TRANSPOSE Function in Excel (Quick View)

Excel TRANSPOSE Function: Syntax & Arguments

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.

Rotate Data from Rows to Columns or Vice Versa Using TRANSPOSE Option

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

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

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

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

Excel TRANSPOSE Function

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

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

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.

Use Formula Using TRANSPOSE Function to Copy or Paste Data

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

=TRANSPOSE(B3:H11)

Enter the following formula in cell B14 and press ENTER

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

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.

Transpose Table without Zeros Using TRANSPOSE Function

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

Transpose Table without Zeros Using TRANSPOSE Function

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.

Formula using TRANSPOSE and IF functions

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

Excel TRANSPOSE Function

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”.

Manipulate Data at the Time of Transposing Using TRANSPOSE Option

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

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

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

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
    Excel Transpose Function

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

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

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

All the data will be transposed vertically

Step 7: Do the same process for Team B also

Excel TRANSPOSE Function

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.

Use TRANSPOSE Function in Formula to Calculate Highest Total Salary for the Following Years

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.

Enter formula using MAX MMULT COLUMNS OFFSET Functions

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

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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo