How to Convert Rows to Columns in Excel?
There are 2 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
This is an efficient method, as it is easy to implement and saves time as well. Also, if in case you want your original formatting to be copied, then this method would be the ideal method for you.
For example, if you have below data of key industry players from various regions and also it shows about their job role as shown in figure1.
Here in example 1, the name of the players is written in the column form, but the list becomes too long, and the data entered also seems to look 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..
The steps to convert rows to columns in excel using the paste special method are as follows:
- Select the entire data which needs to convert or transposed then, Copy the selected data by simply pressing ‘Ctrl + C.’
- Select a blank cell and make sure it is outside of the original data range. Staring at the first blank space or cell, paste the data by simply pressing ‘Ctrl +V’ and then from the toolbar, select the down arrow which is under the ‘Paste’ option and select ‘Transpose.’ But the easy way is to right-click the blank which you had selected first to paste your 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.
#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 formula. This method is also a quicker way to perform conversion of rows to columns in excel. But, it does not keep the source formatting and links it to a transposed table.
Consider the below example, as shown in figure6, where it shows the name of the companies, its location, and also a number of employees.
The steps to transpose rows to columns in excel using formula are as follows:
- First of all, count the number of rows and columns present in your original excel table. And then, select the same number of blank cells but make sure you have to transpose the rows to columns so you will select 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 excel ‘=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.
- 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, i.e., 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 many efforts.
- If the number of rows and columns increases, then the transpose method by using paste special options cannot perform efficiently. And in this case, when there are a lot of rows to be converted to columns, the ‘Transpose’ function will be disabled.
- The transpose paste special option does not link the original data to the transposed data. Therefore if in case you need to change the data, then you need to carry out all the steps again.
- In the transpose method, by using the formula, the source formatting is not kept the same for the transposed data.
- After converting the rows to columns in excel by using a formula, the user cannot edit the data in the transposed table as it is linked to the source data.
Things to Remember
- Rows can be transposed to columns by using paste special methods, and the data can be linked to the original data by simply selecting ‘Paste Link’ form the paste special dialog box.
- 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. can also be used to convert rows to columns and vice-versa.
This has been a guide to Convert Rows to Columns in Excel. Here we discuss how to Switch Rows to Columns in Excel using Paste special and TRANSPOSE Formula along with practical examples and downloadable excel template. You may learn more about excel from the following articles –