How to Transpose Data in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

transpose data in excel


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.

Transpose Data with Paste Special Feature

  • 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.

Apply Paste Link Command with Find and Replace Feature to transpose data in excel

  • 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.

apply Find and Replace to transposed data table in Excel

  • 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


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.

Insert TRANSPOSE Function in Excel

  • 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.

Function applied to transpose data in Excel

  • 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.

Transpose data table using the transpose function in Excel

  • 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.

Use Power Query Editor to Transpose Data

  • 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.

Data Table transposed in Power Query Editor in Excel

  • 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.

Source data table with the transposed table in Excel

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

Prantick Bala
Prantick Bala

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo