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.

Dataset Transpose

Download Workbook

4 Ways to Transpose Data in Excel

1. Using Paste Special

Select your Data and copy them by pressing CTRL+C

Transpose

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

transpose data

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

transpose cell

You will get the transpose of your data.

transposed 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.transpose data in excel

Now your data will be copied.

transpose

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

Transpose data

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.

Transpose with direct reference

The replaced data will be shown after that.

Transpose with direct reference

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

transpose

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.

Transpose DATA IN EXCEL

 After that, you will get your data transposed.

Transpose DATA IN EXCEL

Read More: Excel Transpose Formulas Without Changing References (4 Easy Ways)


Similar Readings


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, 

=TRANSPOSE (array)

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.

Transpose data in Excel

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.

Transpose dataset

Select 5×7 empty cells and type the formula,

=TRANSPOSE (IF (select your dataset=””,””,select your dataset))

Then press CTRL+SHIFT+ENTER.

Transpose

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

Pivot table transpose

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. 

Pivot table Transpose

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

Transpose pivot table

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

PIVOT TABLE TRANSPOSE

 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.

Transpose Pivot Table

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

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo