How to Transpose in Excel (5 Easy Ways)

Transpose of Excel is very useful to us. Most of the time we do this manually. But here we are going to describe some easy methods to transpose. These methods will help to choose our desired method as per our needs.
Here, we will have a data set of sales of three cities for the 1st four months of 2021.


Download Practice Workbook

Download this practice sheet to exercise while you are reading this article.


5 Methods for How to Transpose in Excel

1. Using Paste Special to Transpose

The easiest way to transpose is by using Paste Special. You can implement this Paste Special with both Ribbon Commands, Keyboard Shortcuts, and Mouse Shortcuts. First, we will discuss Ribbon commands.

(a) Ribbon Commands

Step 1:

  • First, select the range we want to transpose.
  • Go to Home (see point 1 in image).
  • From the ribbon select Copy (see point 2 in image).
  • From the Copy drop-down select Copy (see point 3 in image).

How to Transpose in Excel: Copy cells for Paste Special

Step 2:

  • Select the cell where we will transpose.
  • From the ribbon go to the Paste (see point 1 in image) drop-down and select Transpose(T) (see point 2 in image).

Apply Transpose in Paste Special

Step 3:

  • After we click Transpose(T) you will get your desired result.

Transposed cells after using Ribbon Shortcut Paste Special


(b) Keyboard Shortcut to Transpose

You can also do this by using Keyboard Shortcut.

Step 1:

  • Select the range we want to transpose.
  • Press Ctrl + C.
  • Select the cell where you will transpose.
  • Press Ctrl + V.
  • After that from the Copy (see point 1 in image) drop-down select Transpose(T) (see point 2 in image).

Transpose by Keyboard Shortcut

Step 2:

  • And we will get the desired transposed result.

Transposed result applying Keyboard shortcut


(c) Mouse Shortcut

Another method is to Paste Special by Mouse Shortcut.

Step 1:

  • First, select the range you want to transpose.
  • Press Ctrl + C.
  • Select the cell where you will transpose.
  • Right-click the mouse and in the Pop-Up, we will get Paste Special.

Mouse Shortcut in Paste Special

Step 2:

  • Click the mouse in the Paste Special and we will get another pop-up window.
  • Tick Transpose and press OK.

Paste Special Options

Step 3:

  • Finally, we will get the desired result.

Transposed cells

Read More: How to Transpose Array in Excel (3 Simple Ways)


2. TRANSPOSE Function to Transpose in Excel

TRANSPOSE is an Excel default function for transpose purposes. We can transpose by using this function and following some conditions.

Step 1:

  • Determine the rows and columns of data. In our dataset, we have 5 rows and 4 columns.
  • Select a vacant region in the worksheet which is 4 rows deep and 5 columns wide.
  • Type the TRANSPOSE function following the equal sign and we will enter the determined data range to transpose. The formula is:
=TRANSPOSE(B3:E7)

How to write Transpose function

Step 2:

  • Press Ctrl + Shift + Enter (We cannot just click the Enter key but Ctrl + Shift + Enter as it is an array formula) The formula is:
=TRANSPOSE(B3:E7)

How to implement Transpose function

Read more: Conditional Transpose in Excel (2 Examples)


Similar Readings


3. Using INDIRECT Function in Excel

INDIRECT is one of the functions used to transpose in Excel. We will put the formula on Cell B9 and press Enter.

=INDIRECT(“B”&COLUMN()+1)

And the following formula in B10 and press Enter:

=INDIRECT("C"&COLUMN()+1)

For B11 formula is and press Enter:

=INDIRECT("D"&COLUMN()+1)

Lastly formula in B12 and press Enter:

=INDIRECT("E"&COLUMN()+1)

Now select these four cells and drag the Fill Handle across to the column. So there we will get the data transposed. But let’s understand what the formula is doing.

INDIRECT function to transpose

Note: Indirect formula helps in converting text strings to cell references. The COLUMN function gives us the number of columns in which the formula is entered.

For example, if we put =COLUMN in any cell of column A it will give the value of 1 as it is the first column. Here we put the formula on column B, it will return 2 as it is the 2nd row.

So, with the column function, we were able to incremental numbers as we drag the formula across columns. But as we inserted the formula in the second column and not the first column so it would have returned 2 in which case the first address to be fetched would have been B9. But we wanted the values from the first cell of column B therefore we had to insert “+1”.  So the formula:

=INDIRECT(“B”&COLUMN()+1)

Read more: Transpose Rows to Columns in Excel


4. Apply INDEX Function to Transpose

Using the INDEX Fubction we can easily understand how to transpose our desired data. The INDEX function is given below:

INDEX(reference, row_num, [column_num],[area_num])

Using the INDEX function we can easily understand how to transpose our desired data. The INDEX function is given below:

INDEX(reference, row_num, [column_num],[area_num])

for row_num: now we will use the COLUMN() function to generate numbers so that as we drag the formula towards the right the row number will get updated because we are jumping across columns and thus the COLUMN function will fetch the column number. Adjust the value generated by the column number by adding or subtracting constants so that you get the desired result.

for column_num: mention 1, 2, 3, and so on, depending on the column within the selected array from which we want the values fetched.

I want the data transposed from Cell B10. Therefore, we use this formula Cell B10:

=INDEX($B$3:$E$9,COLUMN()-1,1)

Here, we use -1 as we are using columns B, and 1 for the first column of the range. Now, do the rest of the cells.
For Cell B11:

=INDEX($B$3:$E$9,COLUMN()-1,2)

Cell B12:

=INDEX($B$3:$E$9,COLUMN()-1,3)

And Cell B13:

=INDEX($B$3:$E$9,COLUMN()-1,4)

Select four cells and drag them to the right until Column F.

INDEX function to transpose

Read More: How to Transpose Rows to Columns Based on Criteria in Excel (2 Ways)


5. Paste Special Magic to Transpose in Excel

Here, we will transpose using Paste Special but in a different way.

We can also use Paste Special by mouse shortcut.

Step 1:

  • First, select the range you want to transpose.
  • Press Ctrl + C.
  • Select the cell where you will transpose.
  • Right-click the mouse and in the Pop-Up, we will get Paste Special.

How to go Paste Special

Step 2:

  • Click the mouse and in the Paste Special and we will get another Pop-Up window.
  • Select Paste Link and press Ok.

Paste Special Options

Step 3:

  • We will get the same data in the selected cell.

Paste data

Step 4:

  • Select the cells from G3 to J7 and go to Replace option.
  • Put = on the Find what and xx on Replace with.
  • Then click Replace All.

Replace methods

Step 5:

  • Now copy cells G3 to J7 and use Paste Special Transpose.

Transpose from Paste Special

Step 6:

Transpose using Paste Special

Step 7:

  • Now select B10 to F13 and replace xx by =.

Replace in Transpose

Step 8:

  • Click on Replace All.

Replace operation implemented

Step 9:

  • Our desired output is here.

Transpose done using Paste Special Magic

Read More: VBA to Transpose Array in Excel (3 Methods)


Remember

When we use the array function, we must remember that we should press Ctrl+Shift+Enter instead of just Enter only.


Conclusion

Transpose is very common in Excel. We tried all the easy ways to transpose. Here, we mentioned 5 methods. Those are the probable methods for all datasets.


Further Readings

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo