Prantick Bala
The Transpose of Data means replacing the rows with the columns and the columns with the rows. By using some tricks, you can easily transpose data. In this article, I will introduce you to 4 easy ways to Transpose Data in Excel.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
4 Easy Ways to Transpose Data in Excel
We are going to use the below dataset using which we can transpose data in Excel. Consider the following Dataset. Here the quarterly sales of a company in different continents are given where different rows show the sales in different continents and different columns show the sales in different quarters of a year. In order to avoid the compatibility issue, you should opt for the Excel 365 edition.
1. Transpose Data with Paste Special Feature
Paste Special has a wide array of useability or extra feature that can help us to execute a wide array of operations. In the Paste Special tool, you can use the transpose command. You can follow the steps mentioned below to execute this transposition of data.
STEPS:
- Select your Data in the dataset and copy them by pressing Ctrl+C.
- After that, select a B11 and right-click on the mouse and from the context menu click on Paste Special.
- Paste Special box will appear. Check the Transpose box and press OK.
- Finally, you will get the transposition of your data.
Note:
The disadvantage of this method is that the transposed data is not linked with the source data. So if you change source data, it won’t be reflected in your transposed data. In this next method, we will learn to overcome this problem.
Read More: Excel Paste Transpose Shortcut: 4 Easy Ways to Use
2. Apply Paste Link Command with Find and Replace Feature
In the Paste Special option, we also have the Paste Link command using which we can create a link between the source data and the copied destination. Then we will use the Find and Replace tool to finish up the task. You can follow the steps mentioned below to carry out the operation of the transposition of data.
STEP 1: Paste Source Data with Link
- Select your Data then right-click on the mouse, then from the context menu, select Copy.
- Select an empty cell then right-click on your mouse cursor.
- Subsequently, select Paste Special.
- In the Paste Special box click on Paste Link, and click OK.
- Now your data will be copied with the link, as shown in the image below.
STEP 2: Replace Link and Transpose Data
- Firstly, select all the copied data.
- Go to the Home tab > Editing > Filter & Select > Replace.
- Type = in Find what box and !@# in Replace with (You can use any other combination of special characters just to make sure this combination is not used anywhere else in your dataset).
- Then click Replace All.
- The replaced data will be shown after that.
- Now transpose the replacement data in any empty cell using the procedure described in method 1.
STEP 3: Bring Back Data Link
- Initially, select all the transposed data.
- Go to Home > Editing > Filter & Select > Replace.
- Type !@# in Find what box and = in Replace with box.
- Click Replace All.
- After that, you will get your data transposed.
- Finally, after some modification, the transposed table will look like the below image.
Read More: Excel Transpose Formulas Without Changing References (4 Easy Ways)
Similar Readings
- How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
- How to Convert Columns to Rows in Excel Based On Cell Value
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
- How to Reverse Transpose in Excel (3 Simple Methods)
- How to Reverse Order of Columns Horizontally in Excel
3. Insert TRANSPOSE Function in Excel
Using the TRANSPOSE function we can transpose the data to another cell. Using a function is a very great idea as it does this transpose job bypassing various lengthy processes. You can follow the steps below to transpose the values in Excel.
STEPS:
- First, you have to choose a range of empty cells.
- If your original data has M rows and N columns, you must select an empty cell range containing N rows and M columns. For our dataset, we have a data Matrix of 5 Rows and 5 columns. So we have to select cells having 5 Rows and 5 columns.
- So we select a range of cells B11:F15.
- Then enter the following formula in the formula box.
=TRANSPOSE(B4:F8)
- Here, array means the dataset you have to transpose. So you have to select B4 to F8.
- After selecting your dataset and entering the formula, you have to press CTRL+SHIFT+ENTER. Remember in this case, pressing Enter will show you an error, you must press CTRL+SHIFT+ENTER.
- After clicking those buttons, you can see that the selected data are transposed as in the image below.
3.1 Handling of Empty Cell
If there is any empty cell in our dataset, we can use the TRANSPOSE formula with a small modification to transpose our data. Go through the below steps to carry out the operation.
STEPS:
- Consider the following dataset with an empty cell of E7.
- Select 5×5 empty cells and type the formula,
=TRANSPOSE(IF(B4:F8="","",B4:F8))
- Then press CTRL+SHIFT+ENTER.
- Finally, the data will be transposed as the image shown below.
Read More: How to Convert Single Columns to Rows in Excel with Formulas
4. Use Power Query Editor to Transpose Data
You can also Transpose your Data by using the Power Query in Excel. The Power Query has a multitude of functions or operations that can execute varied tasks quite smoothly. You can follow the below steps to use Power Query to transpose data.
STEPS:
- For this, select the range of cells B4:F8 and then go to Data > From Table/Range.
- Next, you can have the table now loaded to the power query editor.
- Then go to the Transform tab and from there click on the Use First Row as Header.
- Clicking on this we will see that the header is now included as a regular row.
- Afterward, we can transpose the table, we go to the Translate tab > Transpose.
- After clicking the transpose, we will see that the table is now transposed as below image.
- Now we can load the table to the sheet by going to the Home tab > Close & Load > Close & Load To.
- After then the a new window will open, and from there select Table in the Select how you want to view this data in your workbook.
- Then we have to select the destination of the newly modified table.
- Select the Existing worksheet, and then select cell B11 using the range box.
- Subsequently, click OK after this.
- Finally, we will see that the table is now in the sheet.
Read More: Conditional Transpose in Excel (2 Examples)
Conclusion
You can Transpose Rows into columns and columns into rows by using any of the four methods. If you know any other ways to transpose columns into rows or rows into columns, comment here, so we will also get a chance to know. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
Related Articles
- Data clean-up techniques in Excel: Changing vertical data to horizontal data
- How to Swap Rows in Excel (2 Methods)
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- Excel VBA: Transpose Multiple Rows in Group to Columns
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- VBA to Transpose Array in Excel (3 Methods)
