Rows To Columns In Excel

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Sheeba M

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

Convert Rows to Columns Example 1-5

How To Convert Rows To Columns In Excel?

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

  1. Using Paste Special.
  2. Using TRANSPOSE Formula.
Table of Contents
  • 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.

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.

NameJenniferRachealMilosCostasDanielKevin
CountrySpainEnglandFranceBrazilIrelandGermany
Job RoleCTOCEOAnalystAnalystMediaDirector
DomainElectronicsChemicalMechanicalAerospaceMarketingFinance

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:

  1. Select the whole data which needs to be transformed or transposed. Then, Copy the selected data by simply pressing Ctrl + C keys.


    Convert Rows to Columns Example 1-1

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


    Convert Rows to Columns Example 1-2

  3. The “Paste Special” dialog box appears. Select the “Transpose” option, and click “OK.”


    Convert Rows to Columns Example 1-3

  4. 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)


    Convert Rows to Columns Example 1-4
    It shows how the transpose function is performed to convert Rows to Columns.
    Convert Rows to Columns Example 1-5

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.

TRANSPOSE Formula Example 2

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.
TRANSPOSE Formula Example 2-1
  • In the first empty cell, enter the TRANSPOSE formula in excel ‘=TRANSPOSE($A$1:$F$3)’.
TRANSPOSE Formula Example 2-2
  • Since this formula needs to be applied for other blank cells, press “Ctrl+Shift+Enter”.
TRANSPOSE Formula Example 2-3

The rows are converted to columns, as shown below.

TRANSPOSE Formula Example 2-4

Advantages Of converting Rows To Columns In Excel

  1. The Paste Special method is useful when the user needs the original formatting to be continued.
  2. 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.
  3. 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

  1. 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.
  2. 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.
  3. In the transpose method, the source formatting is not kept the same for the transposed data by using the formula.
  4. 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

  1. 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.
  2. We must execute the Transpose function as an array formula, i.e., press “Ctrl+Shift+Enter” instead of the normal “Ctrl+Enter”

Frequently Asked Questions (FAQs)

1. Why is the convert Rows to Columns in Excel not working?

A few reasons why the Rows to Columns in Excel may not work are,
a. We must select the exact rows and columns, i.e., to convert 5 rows and 10 columns we must select 10 rows and 5 columns of empty cells. In other words, 5r*10c become 10r*5c, vice versa. If it is less or more the conversion will not take place.
b. We have copied the data incorrectly.

2. Where is the INDIRECT function in Excel found?

First, choose an empty cell - select the “Formulas” tab - go to the “Function Library” group - click the “Lookup & Reference” option drop-down - select the “INDIRECT” function, as shown below.

Rows to Columns in excel - FAQ 2

3. How to convert Rows to Columns in Excel using the Transpose method?

The steps to convert Rows to Columns in Excel using Transpose method are,
·       First, choose the empty cell range for the output, i.e., if there are 2 rows and 5 columns in the source dataset, then the output cell range must be selected as 5 rows and 2 columns.
·       Next, type the formula =TRANSPOSE(cell_range1) and make it absolute, where the cell_range1 argument value is the source dataset. We will see the formula appears in the first selected cell in the cell range.
·       Finally, enter the keys ““Ctrl+Shift+Enter” to execute the conversion.