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.
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.
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.
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 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..
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 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. ‘=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”
Frequently Asked Questions (FAQs)
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.
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.
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.
This article is a guide to Rows to Columns in Excel. Here we learn to convert Rows to Columns using Paste Special, TRANSPOSE(), example, downloadable 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.
- Compare Two Columns in ExcelCompare Two Columns In ExcelTwo columns in excel are compared when their entries are studied for similarities and differences.
- Use Columns Formula in Excel
- Column Sort in Excel