How To Transpose Data in Excel (4 Methods)

Transpose Data means replacing the Rows by the Columns and The Columns by 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.

Consider the following Dataset. Here the quarterly sales of a company in different continents is given where different rows show the sales in different continents and different columns show the sales in different quarters of a year.

Download Workbook

4 Ways to Transpose Data in Excel

1. Using Paste Special

Select your Data and copy them by pressing CTRL+C

After that, select an empty cell and right click on that. Select Paste Special.

Paste Special box will appear. Check Transpose box and press OK

You will get the transpose of your data.

The disadvantage of this method is that the transposed data is not linked with source data. So if you make any change in 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.Transpose Data with Direct Reference

Select your Data and copy them by pressing CTRL+C. Select an empty cell> Right click on your mouse cursor> select Paste Special. In the Paste special box click on Paste Link.

Now your data will be copied.

Select all the copied data. Go to Home> Editing> Filter & Select> Replace

Type = in Find what box and [email protected]# in Replace with box. (You can use any other combination of special characters just to make sure this combination is not used anywhere else in your dataset.) Click Replace All.

The replaced data will be shown after that.

Now Transpose the replace data in any empty cell using the procedure described in method 1.

After that, Select all the transposed data. Go to Home> Editing> Filter & Select> Replace. Type [email protected]# in Find what box and = in Replace with box. Click Replace All.

 After that, you will get your data transposed.

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.Transpose Cell Using TRANSPOSE Formula

You can easily transpose your data by using the TRANSPOSE formula. First you have to select a range of empty cells. If your original data has M rows and N columns, you must select empty cells containing N rows and M columns. For our dataset we have a data Matrix of 7 Rows and 5 columns. So we have to select cells having  5 Rows and 7 columns. Now Type the formula, 

Here, array means the dataset you have to transpose. So you have to select B5 to F11.

After selecting your dataset you have to press CTRL+SHIFT+ENTER. Remember in this case, pressing Enter will show you an error, you must press CTRL+SHIFT+ENTER.

Handling 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. Consider the following dataset with an empty cell of E8.

Select 5×7 empty cells and type the formula,

Then press CTRL+SHIFT+ENTER.

Read More: How to Convert Single Columns to Rows in Excel with Formulas

 4. Using PivotTable

You can also Transpose your Data by using the PivotTable. For that you have to create a PivotTable first. Select your data> go to Insert> PivotTable

Create PivotTable window will appear. In the Location Box select an empty cell of your worksheet or If you want to create Pivot Table in a new worksheet select new worksheet. Press OK. 

Now on the right side of Excel, PivotTable Fields will appear.

Now Drag the Sales into the columns box. You can get sales of different Quarter by dragging it to ⅀Values box.

 In this method you can’t get all the rows together. By dragging different quarter into ⅀vales box one by one you can get the quarter sales in rows.

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. Please leave a comment if you have any confusion. 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.

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)