How To Switch Rows To Columns In Excel

How to Convert Rows to Columns in Excel?

There are 2 different methods for converting rows to columns in Excel, which are every bit follows:

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

#ane Using Paste Special

This is an efficient method, as it is easy to implement and saves fourth dimension also. Also, if in case y'all want your original formatting to be copied, and then this method would be the ideal method for you.

You can download this Convert Rows to Columns Excel Template here – Catechumen Rows to Columns Excel Template

Example

For case, if you lot accept below data of key industry players from various regions and also it shows about their chore function equally shown in figure1.

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

Here in case 1, the proper 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 alter the rows to columns using paste special methods Paste special in Excel allows you to paste partial aspects of the data copied. At that place are several means to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such equally CTRL+ALT+5 or ALT+E+S. read more .

The steps to convert rows to columns in excel using the paste special method are as follows:

1. Select the unabridged data which needs to catechumen or transposed so, Copy the selected data past just pressing Ctrl + C.

   
   

2. Select a bare prison cell and brand certain it is exterior of the original data range. Staring at the first blank infinite or cell, paste the data past simply pressing Ctrl +V and then from the toolbar, select the downwardly arrow which is nether the Paste option and select Transpose. Just the easy mode is to right-click the blank which y'all had selected first to paste your data. So select Paste Special from information technology.

   
   

3. Then a Paste Special dialog box will appear from which select the Transpose pick. And Click, OK.

   
   

4. In the to a higher place figure, copy the data which needs to be transposed then click on the downwardly arrow of the Paste option and class the options select Transpose. (Paste-Transpose)

   

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

   

#2 Using TRANSPOSE Formula

When a user wants to keep the aforementioned connections of the rotating tabular array 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.

Example

Consider the beneath example, every bit 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:

• Showtime of all, count the number of rows and columns present in your original excel table. Then, select the same number of blank cells but make certain you have to transpose the rows to columns so y'all will select the blank cells in the opposite direction. Similar in example 2, the number of rows is 3, and the number of columns is vi. So you lot volition select six blank rows and 3 blank columns.

• In the first empty jail cell, enter the TRANSPOSE formula in excel The TRANSPOSE office in excel helps rotate (switch) the values from rows to columns and vice versa. Being a office 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 exist applied for other blank cells so printing 'Ctrl+Shift+Enter.'

The rows are converted to columns.

Advantages

1. The paste special method is useful when the user needs the original formatting to be connected.
2. In the transpose method, past using the transpose role, the converted tabular array will change its information automatically, i.due east., whenever the user changes the information in the original table, it will automatically alter the data in the transposed table, as it is linked to it.
3. Converting the rows to columns in excel sometimes helps the user to make the audience sympathize their data easily and and so that the audition interprets it without many efforts.

Disadvantages

1. If the number of rows and columns increases, and 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 volition be disabled.
2. The transpose paste special pick does non link the original data to the transposed data. Therefore if in case yous need to change the data, then you need to carry out all the steps again.
3. In the transpose method, past using the formula, the source formatting is not kept the aforementioned for the transposed information.
4. After converting the rows to columns in excel by using a formula, the user cannot edit the data in the transposed table every bit it is linked to the source data.

Things to Remember

1. Rows can be transposed to columns by using paste special methods, and the information tin be linked to the original information by simply selecting 'Paste Link' form the paste special dialog box.
2. INDIRECT formula and ADDRESS functions in Excel The accost 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 outcome will exist $B$1. read more than can also be used to convert rows to columns and vice-versa.

Recommended Articles

This has been a guide to Catechumen Rows to Columns in Excel. Here we talk over how to Switch Rows to Columns in Excel using Paste special and TRANSPOSE Formula along with applied examples and downloadable excel template. You may learn more most excel from the following articles –

• Group Columns in Excel
• Compare Ii Columns in Excel
• Utilise Columns Formula in Excel
• Column Sort in Excel

• 35+ Courses
• 120+ Hours
• Full Lifetime Access
• Document of Completion

Acquire More >>

Source: https://www.wallstreetmojo.com/rows-to-columns-in-excel/

Posted by: mcqueengreasse.blogspot.com

Share this post