Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Transpose in Excel (5 Easy Ways)

Today, we are going to show how to Transpose operations in Excel. The Transpose operation 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 allow users to Transpose their data and present it according to their needs.


Download Practice Workbook

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


5 Methods to Transpose in Excel

In this article, we will discuss 5 effective ways of how to perform Transpose operations in Excel. Firstly, we will use the Paste Special command to do the task. Secondly, we will use the TRANSPOSE function. Thirdly, we will apply the INDIRECT function to get the job done. Fourthly, we will implement the INDEX function. Finally, we will use the Paste Special and the Find & Replace command to perform Transpose operation in Excel. Here, we will have a data set of sales of three cities for the 1st four months of 2021 to perform the Transpose operation.

how to transpose in excel


1. Using Paste Special

The Paste Special command allows users to paste a value or range of values according to their needs. Here, we will use this feature to show how to transpose in Excel. Firstly, we will use Ribbon Commands to get the job done. Then, we will use keyboard shortcuts to transpose the values.

1.1. Ribbon Commands

In this sub method, we will explore the Ribbon Commands to use the Paste Special feature to transpose the values.

Steps:

  • To begin with, select the B4:E8 range and press Ctrl+C to copy the range.

using ribbon commands to show how to transpose in excel

  • Then, click on the B10 cell.
  • Next, first, go to the Home tab.
  • Secondly, select the Paste option.
  • Finally, from the drop-down options, choose the Transpose sign.

using ribbon commands to show how to transpose in excel

  • As a result, the data will get transposed.

using ribbon commands to show how to transpose in excel


1.2 Keyboard Shortcut

Here, we will use keyboard shortcuts to perform the transpose operation.

Steps:

  • At the beginning, copy the B4:E8 range by clicking on Ctrl+C.

using keyboard shortcuts to show how to transpose in excel

  • Now, click on the B10 cell and press Ctrl+V.
  • Consequently, the data will be pasted into that cell.
  • Next, click on the Ctrl option to the bottom-right corner of the pasted data.
  • Finally, select the transpose sign from the drop-down options.

using keyboard shortcuts to show how to transpose in excel

  • As a result, we will see the transposed version of our dataset.

using keyboard shortcuts to show how to transpose in excel

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


2. Using TRANSPOSE Function

The TRANSPOSE function is the built-in function for performing the transpose operation in Excel. In this method, we will explore this function to perform the transpose operation.

Steps:

  • At the start, click on the B10 cell and type,
=TRANSPOSE(B4:E8)
  • Then, press Enter.
  • As a result, we will find that the transpose operation is completed.

applying transpose function to show how to transpose in excel

Read more: Conditional Transpose in Excel (2 Examples)


Similar Readings


3. Utilizing INDIRECT Function

In this instance, we will use the INDIRECT function to perform the transpose operation in Excel. We will transpose the rows one-by-one in this case by applying different formulas incorporating the INDIRECT function and the COLUMN function.

Steps:

  • To start with, choose the B10 formula and enter,
=INDIRECT(“B”&COLUMN()+2)
  • Then, press Enter.
  • As a result, we will get the first value of the transposed row.

applying indirect function to show how to transpose in excel

  • Now, move the cursor to the right to Autofill the rest of the values.

applying indirect function to show how to transpose in excel

Now, the first column in the dataset will become the first row.

  • Next, click on the B11 cell and type,
=INDIRECT(“C”&COLUMN()+2)
  • Now, hit Enter.
  • As a result, we will get the first value of the second row of the output dataset.

applying indirect function to show how to transpose in excel

  • Next, move the cursor to the right to autofill the values.

applying indirect function to show how to transpose in excel

  • Again, select the B12 cell and enter the following,
=INDIRECT(“D”&COLUMN()+2)
  • After that, press Enter.
  • Consequently, we will get the first value of the third row.

applying indirect function to show how to transpose in excel

  • Again, slide the cursor to the right to autofill.

applying indirect function to show how to transpose in excel

  • Finally, choose the B13 cell and type the following,
=INDIRECT(“E”&COLUMN()+2)
  • Then, hit Enter.
  • As a result, we will have the first value of the fourth row.

applying indirect function to show how to transpose in excel

  • Finally, move the cursor to the right to get the entire transposed row.

applying indirect function to show how to transpose in excel

Read more: Transpose Rows to Columns in Excel


4. Applying INDEX Function

In this example, we will use the INDEX function to transpose our dataset. We will combine the INDEX function with the COLUMN function to do so.

Steps:

  • To begin with, select the B10 formula and type,
=INDEX($B$4:$E$8,COLUMN()-1,1)
  • Then, hit Enter.
  • Consequently, we will have the first value of the transposed row.

applying index function to show how to transpose in excel

  • Next, slide the cursor to the right to autofill the rest of the values.

applying index function to show how to transpose in excel

Now, the first column in the dataset will become the first row.

  • Now, click on the B11 cell and enter,
=INDEX($B$4:$E$8,COLUMN()-1,2)
  • Now, press Enter.
  • As a result, we will get the first value of the second row.

applying index function to show how to transpose in excel

  • Next, move the cursor to the right to autofill the values according to the formula.

applying index function to show how to transpose in excel

  • Thereafter, click on the B12 cell and enter the following formula,
=INDEX($B$4:$E$8,COLUMN()-1,3)
  • After that, press the Enter button.
  • Consequently, we will have the first value of the third row.

applying index function to show how to transpose in excel

  • Again, slide the cursor to the right to autofill.

  • Finally, select the B13 cell and type the following formula,
=INDEX($B$4:$E$8,COLUMN()-1,4)
  • Then, press Enter.
  • As a result, we will have the first value of the fourth row.

applying index function to show how to transpose in excel

  • Finally, slide the cursor to the right to get the entire dataset transposed.

applying index function to show how to transpose in excel

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


5. Using Combination of Paste Special and Find & Replace Command

In this final method, we will use the Paste Special command in combination with the Replace command. Here, we will paste our dataset using the Paste Special command and then use the Replace command to replace the values inside it. Finally, we will copy and paste the dataset again and replace them again only to transpose the dataset.

Steps:

  • To begin with, choose the B4:E8 cell range and press Ctrl+C to copy it.

  • Now, click on the B10 cell and right-click.
  • Then, from the available options, select Paste Special.
  • As a result, a prompt will be on the screen.

  • From the prompt, first, choose Paste Link.
  • Then, press OK.
  • As a result, the dataset will be pasted in the selected cell.

  • Next, select the B10:E14 range.
  • Thereafter, go to the Home tab.
  • Then, select the Editing >> Find & Select >> Replace.
  • As a result, a prompt will appear on the screen.

  • After that, type “=” in the Find what box and “xx” in the Replace with
  • Finally, choose Replace All.
  • As a result, all the values will be replaced.

  • Now, choose the B10:E14 cell range again and press Ctrl+C.

  • Next, choose the B16 cell and press Ctrl+V.
  • After that, select the Ctrl option to the bottom right corner of the selection.
  • From the available options, select the transpose sign.

  • Now, choose the B16:F19 cell and go to the Home
  • After that, select Editing >> Find & Select >> Replace.
  • Now, we will see a prompt.

  • This time, write “xx” in the Find What box and “=” in the Replace with
  • Finally, press Replace All.

  • As a result, we will see a transposed dataset.

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


Conclusion

In this article, we have discussed 5 ways of how to transpose in Excel. These methods will help Excel users to transpose their data according to their needs.If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.


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