Convert Columns To Rows In Excel

Convert columns to rows in Excel is a method used to convert the data column-wise into row-wise. It is essential to change the data column into row wise for easy access.

For example, consider the below table with large dataset showing marks obtained a set of students in various subjects.

Now, using convert columns to rows in Excel, we can change the data as shown in the below image.

There are two methods of doing this:

1. Excel Ribbon Method
2. Mouse Method

Let us take the below example to understand this process.

#1 - Using Excel Ribbon

We must follow the below steps for converting columns to rows:

1. We must select the whole data and go to the "HOME" tab.
2. Then, we must click on the "Copy" option under the "Clipboard" section. You may refer to the below screenshot. Else, press "CTRL +C" keys to copy the data.
3. Then, we must click on any blank cell where we want to see the data.
4. Next, we must click on the "Paste" option under the "Clipboard" section. You may refer to the below screenshot.
5. As a result, this will open a "Paste" dialog box. Then, choose the option "Transpose," as shown below.
6. Consequently, it will convert the columns to rows and display the data as we want.

#2 - Using Mouse

Follow the below steps for doing this:

• Step 1:  We must first select the whole data and right-click. As a result, it will open a list of items. Now, we must click on the "Copy" option from the list. You may refer to the below screenshot.
• Step 2: We need to click on any blank cell where we want to paste this data.

• Step 3: Again, right-click and select Paste Special option.
• Step 4: Again, it will open a "Paste Special" dialog box.
• Step 5: Then, click on the "Transpose option," as shown in the below screenshot.
• Step 6: Lastly, we must click on the "OK" button.

Now our data is converted from columns to rows.

Example #1 - Convert Columns To Rows With Copy And Paste

We have the sales data location-wise.

This data is very useful for us, but we want to see this data in vertical order so that it will be easy to compare.

#2 - Using Mouse - Converting Columns to Rows (Or Vise-Versa)

Let us take another example to understand this process.

We have some student score data subject-wise.

Now, we have to convert this data from columns to rows.

Follow the below steps for doing this:

• Step 1: We must first select the whole data and right-click. As a result, it will open a list of items. Now, we must click on the "Copy" option from the list. You may refer to the below screenshot.

• Step 2: We need to click on any blank cell where we want to paste this data.
• Step 3: Again, right-click and select Paste Special option.

• Step 4: Again, it will open a "Paste Special" dialog box.
• Step 5: Then, click on the "Transpose option," as shown in the below screenshot.
• Step 6: Lastly, we must click on the "OK" button.

Now our data is converted from columns to rows. The final result is shown below:

In other ways, when we move our cursor or mouse on "Paste Special" option, it will again open a list of options as shown below:

• Converting columns to rows or vice-versa both methods also work when we want to convert a single column to a row or vice-versa.
• This option is very handy and saves a lot of time while working.