Convert Columns To Rows In Excel

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is 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.

Convert Columns To Rows In Excel - Intro 1

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

Convert Columns To Rows In Excel - Intro 1-1
Table of contents
  • In Excel, you can convert columns to rows. There are two approaches: the Excel Ribbon Method and the Mouse Method.
  • Converting columns to rows or rows to columns: Both methods are also applicable when converting a single column to a row or vice versa.
  • This option is highly advantageous and saves a significant amount of time at work.
  • Using the Excel ribbon, one can convert columns to rows by copying and pasting. This data is beneficial to us, but one must see it in vertical order so that they can compare it easily.

How To Convert Columns To Rows In Excel?

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.

Examples

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

We have the sales data location-wise.

Convert Columns To Rows Example 1

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.

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.


    Convert Columns To Rows Example 1-1

  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.


    Convert Columns To Rows Example 1-2

  5. As a result, this will open a "Paste" dialog box. Then, choose the option "Transpose," as shown below.


    Convert Columns To Rows Example 1-3

  6. Consequently, it will convert the columns to rows and display the data as we want.


    The result is shown below:

    Convert Columns To Rows Example 1-4

    Now, we can put the filter and see the data differently.

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

Example 2

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.
Example 2-1
  • 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.
Example 2-2
  • 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.
Example 2-3

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

Example 2-4

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:

Example 2-5

Important Things To Note

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

Frequently Asked Questions

1. Why you might want to convert columns to rows in Excel?

When you have a large number of columns, the column data at the end is sometimes not visible and does not fit on the screen. In this case, you can use Excel's ā€˜PasteSpecialā€™ option to change its orientation from the current horizontal range to the vertical range.

For example, consider the below table with large dataset showing sample data showing number of male, female and children participants.

Convert Columns To Rows In Excel - FAQ 1

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

Convert Columns To Rows In Excel - FAQ 1-1

2. How to preserve data integrity while converting columns to rows in Excel?

To maintain data integrity when converting columns to rows in Excel, avoid overlapping data by ensuring no empty cells within rows or columns. Ensure consistent data types in each cell, such as numeric values, even after the transpose operation. Include header labels to ensure correct row labeling. Use the "Paste Values" option to copy and paste data as values, preventing formulas from being copied in their original form. Consider saving a copy of the original data for future reference. These tips help ensure data integrity and prevent data loss or corruption during the conversion process.

3. What are the common issues that arise while converting columns to rows in Excel?

Common issues can arise when converting columns to rows in Excel. To fix these, increase the cell size to fit the new data, ensure consistent data types and no empty cells in the original data, check for proper alignment of headers and labels, and ensure each record is complete. If the transposed data contains errors, double-check the formulas and ensure they have been correctly transposed. These steps help ensure a smooth and accurate conversion process.