Rows To Columns In Excel

Publication Date :

10 Jan, 2019

Blog Author :

Edited by :

Reviewed by :

Download FREE Rows To Columns Excel Template and Follow Along!

Rows To Columns Excel Template.xlsx

Table Of Contents

What Is Convert Rows To Columns In Excel?

The Convert Rows to Columns In Excel means that if we have a dataset in the row form, adding more data will increase the data and make it disorganized or unreadable. In such scenarios, we can convert the row-wise data set to column-wise using the Rows to Columns in Excel feature.

For example, we have a dataset with 4 rows and 7 columns, as shown in cells A1:G4. As the data gets added, the worksheet moves toward the right which will hide a few of the data on the left of the worksheet. Therefore, we apply the Rows to Columns in Excel feature, and get the output shown in the cells A7:D13.

key Takeaways

Convert Rows to Columns in Excel helps the user convert a row-wise large dataset into a column-wise flowing dataset.
It helps users to further add data or update the dataset with more information without making the dataset look unorganized or difficult to read.
We can use the Paste Special and the Transpose method, as we learnt, to convert rows to columns and the other way round.
However, we have a few more inbuilt functions in Excel such as the ADDRESS and the INDIRECT functions to perform the conversion from Rows to Columns and vice versa.

How To Convert Rows To Columns In Excel?

There are two different methods for converting Rows to Columns in Excel, namely:

Using Paste Special.
Using TRANSPOSE Formula.

Method #1 - Using Paste Special

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

Example using Paste Special to convert Rows to Columns in Excel

In the below image, we have the data of key industry players from various regions, and their job roles.

Name	Jennifer	Racheal	Milos	Costas	Daniel	Kevin
Country	Spain	England	France	Brazil	Ireland	Germany
Job Role	CTO	CEO	Analyst	Analyst	Media	Director
Domain	Electronics	Chemical	Mechanical	Aerospace	Marketing	Finance

Since the names are added in columns, when we add further details, the data entered seems unorganized and hard to read. Therefore, we will convert the Rows to Columns using paste special methods.

The steps to convert Rows to Columns in Excel using the Paste Special method are as follows:

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.
The “Paste Special” dialog box appears. 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 from the options, select Transpose. (Paste-Transpose)

It shows how the transpose function is performed to convert Rows to Columns.

Method #2 - Using TRANSPOSE Formula

When users want to keep the same connections of the rotating table with the original table, they can use a transposing method using a formula. This method is also quicker in converting Rows to Columns in Excel. However, it does not keep the source formatting and links it to a transposed table.

Example using TRANSPOSE Formula to convert Rows to Columns in Excel.

The image below, i.e., figure 6, shows the company’s names, location, and number of employees.

The steps to convert Rows to Columns in Excel using the Transpose 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, as shown below.

In the first empty cell, enter the TRANSPOSE formula in excel ‘=TRANSPOSE($A$1:$F$3)’.

Since this formula needs to be applied for other blank cells, press “Ctrl+Shift+Enter”.

The rows are converted to columns, as shown below.

Advantages Of converting Rows To Columns In Excel

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 helps the user to make the audience understand their data easily, and the audience can interpret it without much effort.

Disadvantages Of Converting Rows To Columns In Excel

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.

Important Things To Note

Instead of Paste Special, if we just copy and paste normally, we will be unable to perform the conversion of rows to columns, or vice versa.
We must execute the Transpose function as an array formula, i.e., press “Ctrl+Shift+Enter” instead of the normal “Ctrl+Enter”