Alok Paul
Throughout the content, we will learn to transpose data in Excel in several ways. Nearly all Excel users employ the data manipulation method of switching rows and columns. In Excel, the transformation of horizontal data into vertical and vertical data into horizontal is known as “Transpose”. We are pinky confident that you are quite familiar with transposing data in regular worksheets.
While constructing various methods, we expound on numerous tools such as Paste Special, Find and Replace during converting the data rows to columns or columns to rows. Further, we also expound on the application of several functions like TRANSPOSE, INDEX, INDIRECT, and so on. We also add additional 26 resources regarding the use of Table, VBA, and Power Query to transpose a dataset.
You will get scattered pages, websites, and blogs over the internet that consume both your energy and time. If you find transposing data tiresome or you are an amateur in MS Excel. This article will keep you on the right track. Please stay tuned to have a great run with our learning sequence.
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.
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.
- 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.
- As a result, the data will get transposed.
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.
- 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.
- As a result, we will see the transposed version of our dataset.
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.
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. 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.
- Now, move the cursor to the right to Autofill the rest of the values.
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.
- Next, move the cursor to the right to autofill the values.
- 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.
- Again, slide the cursor to the right to autofill.
- 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.
- Finally, move the cursor to the right to get the entire transposed row.
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.
- Next, slide the cursor to the right to autofill the rest of the values.
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.
- Next, move the cursor to the right to autofill the values according to the formula.
- 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.
- 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.
- Finally, slide the cursor to the right to get the entire dataset transposed.
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.
Transpose in Excel: Knowledge Hub
- Transpose Data
- Transpose in Excel VBA
- Transpose Array
- Excel VBA to Transpose Array
- Transpose a Table
- Paste Transpose Using Shortcut
- Transpose Formulas Without Changing References
- Transpose Rows to Columns
- Power Query: Transpose Rows to Columns
- Transpose Rows to Columns Using Excel VBA
- Transpose Multiple Rows in Group to Columns
- VBA: Transpose Multiple Rows in Group to Columns
- Transpose Every n Rows to Columns
- Convert Rows to Columns Based on Criteria
- Transpose Duplicate Rows to Columns
- Convert Single Columns to Rows with Formulas
- Transpose Columns to Rows
- Convert Columns to Rows
- Convert Columns to Rows Using Power Query
- VBA to Transpose Multiple Columns into Rows
- Convert Columns to Rows Based On Cell Value
- Convert Multiple Columns into a Single Row
- Transpose Multiple Columns into One Column
- Reverse Transpose in Excel
- Reverse Order of Columns Horizontally
- Perform Conditional Transpose
- Convert Column to Row with Comma
Conclusion
While some people might prefer to work with data displayed vertically, others might feel more familiar with dealing with data portrayed horizontally. In this scenario, transposing data in Excel is inevitable for any user. Throughout this article, we intend to provide a complete guideline with 26 resources to make your learning smoother. Although, we have discussed 5 ways how to transpose in Excel throughout this content. 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 to unlock a great resource for Excel-related content.
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
