How to Convert Rows to Columns in Excel?

There are two different methods for converting rows to columns in Excel, which are as follows:

  • Method #1 – Transposing Rows to Columns Using Paste Special.Method #2 – Transposing Rows to Columns Using TRANSPOSE Formula.

#1 Using Paste Special

It is an efficient method; it is easy to implement and saves time. Also, this method would be ideal if you want your original formatting to be copied.

Example

For example, if you have below data of key industry players from various regions, it also shows their job roles as shown in figure1.

In example 1, the players’ names are written in column form. Still, the list becomes too long, and the data entered also seems unorganized. Hence, we would change the rows to columns using paste special methodsUsing Paste Special MethodsPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more.

The steps to convert rows to columns in Excel using the Paste Special method are as follows:

#2 Using TRANSPOSE Formula

When a user wants to keep the same connections of the rotating table with the original table, it can use a transposing method using a formula. This method is also quicker in converting rows to columns in Excel. But, it does not keep the source formatting and links it to a transposed table.

  • Select the whole data which needs to be transformed or transposed. Then, Copy the selected data by simply pressing Ctrl + C keys. Select a blank cell and ensure it is outside the original data range. Paste the data by pressing the “Ctrl +V” keys starting at the first blank space or cell. Then, select the down arrow from the toolbar under the “Paste” option and select “Paste Transpose.” But the easy way is to right-click the blank you chose first to paste the data. Then, select “Paste Special” from it. Then, a “Paste Special” dialog box will appear from which select the “Transpose” option. And click “OK.” In the above figure, copy the data which needs to be transposed and then click on the down arrow of the Paste option and form the options select Transpose. (Paste-Transpose) It shows how the transpose function is performed to convert rows to columns.

It shows how the transpose function is performed to convert rows to columns.

Consider the example below, as shown in figure 6, where it shows the company’s names, location, and the number of employees.

The steps to transpose rows to columns in excel using formula are as follows:

  • First, count the number of rows and columns in your original excel table. And then, select the same number of blank cells but ensure you have to transpose the rows to columns to choose the blank cells in the opposite direction. Like in example 2, the number of rows is 3, and the number of columns is 6. So, you will select 6 blank rows and 3 blank columns.

  • In the first empty cell, enter the TRANSPOSE formula in excelTRANSPOSE Formula In ExcelThe TRANSPOSE function in excel helps rotate (switch) the values from rows to columns and vice versa. Being a part of the Excel lookup and reference functions, its purpose is to organize the data in the desired format. To execute the formula, the exact size of the range to be transposed is selected and the CSE key (“Control+Shift+Enter”) is pressed.

  • read more ‘=TRANSPOSE($A$1:$F$3)’.

  • Since this formula needs to be applied for other blank cells so press ‘Ctrl+Shift+Enter.’

The rows are converted to columns.

Advantages

  • The Paste Special method is useful when the user needs the original formatting to be continued.In the transpose method, by using the TRANSPOSE function, the converted table will change its data automatically, which means that whenever the user changes the data in the original table, it will automatically change the data in the transposed table, as it is linked to it.Converting the rows to columns in Excel sometimes helps the user to make the audience understand their data easily and so that the audience interprets it without much effort.

Disadvantages

  • If the number of rows and columns increases, then the transpose method using the Paste Special options cannot perform efficiently. In this case, the “TRANSPOSE” function will be disabled when there are many rows to be converted to columns. The “Transpose Paste Special” option does not link the original data to the transposed data. Therefore, if you need to change the data, you must repeat all the steps.In the transpose method, the source formatting is not kept the same for the transposed data by using the formula.After converting the rows to columns in Excel using a formula, the user cannot edit the data in the transposed table as it is linked to the source data.

Things to Remember

  • We can transpose rows to columns using Paste Special methods. We can link the data to the original data by selecting “Paste Link” from the “Paste Special” dialog box.We can also use Excel’s INDIRECT formula and ADDRESS functions in ExcelADDRESS Functions In ExcelThe address function finds the cell’s address and returns an absolute value. It requires two mandatory arguments: the row number and the column number. For example, if we use =Address(1,2), the result will be $B$1.read more to convert rows to columns and vice-versa.

This article is a guide to Convert Rows to Columns in Excel. Here, we discuss switching rows to columns in Excel using the Paste Special and TRANSPOSE formula, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Group Columns in ExcelGroup Columns In ExcelIn Excel, grouping one or more columns together in a worksheet is referred to as group column and I t allows you to contract or expand the column.read moreCompare Two Columns in ExcelCompare Two Columns In ExcelTwo columns in excel are compared when their entries are studied for similarities and differences.read moreUse Columns Formula in ExcelColumn Sort in Excel