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

**Table of Contents**hide

**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*).

**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*).

**Step 3:**

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

**(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***)**.

**Step 2:**

- And we will get the desired transposed result.

**(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**.

**Step 2:**

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

**Step 3:**

- Finally, we will get the desired result.

**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)`

**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)`

**Read more: ****Conditional Transpose in Excel (2 Examples)**

**Similar Readings**

**How to Transpose Duplicate Rows to Columns in Excel (4 Ways)****How to Convert Columns to Rows in Excel (2 Methods)****Transpose Multiple Columns into One Column in Excel (3 Handy Methods)****How to Reverse Transpose in Excel (3 Simple Methods)**

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

**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 C**ell B10**. Therefore, we use this formula C**ell 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 C**ell 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**.

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

**Step 2:**

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

**Step 3:**

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

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

**Step 5:**

- Now copy cells
**G3**to**J7**and useÂ**Paste Special Transpose**.

**Step 6:**

- We will get a
**transposed table.**

**Step 7:**

- Now select
**B10**to**F13**and replace**xx**by**=**.

**Step 8:**

- Click on
**Replace All**.

**Step 9:**

- Our desired output is here.

**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

**Data clean-up techniques in Excel: Changing vertical data to horizontal data****How to Swap Rows in Excel (2 Methods)****Convert Columns to Rows in Excel (2 Methods)****Excel VBA: Transpose Multiple Rows in Group to Columns****How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)****Excel Transpose Formulas Without Changing References (4 Easy Ways)****Transpose Multiple Rows in Group to Columns in Excel**