How to Move Data from Row to Column in Excel (4 Easy Ways)

Many Excel users are accustomed to the process of transposing data. It happens rather frequently that after creating a complicated table, you discover that rotating it would greatly improve the analysis or graphing of the data. In this article, we will show you how to move data from row to column in Excel with some easy ways.

How to Move Data from Row to Column in Excel: 4 Handy Ways

In the following methods, you will learn to move data from row to column in Excel by using the Paste Special command, utilizing the TRANSPOSE function, using the INDIRECT and  ADDRESS functions, and applying a VBA code. Let’s suppose we have a sample data set.

1. Using Paste Special Command to Move Data from Row to Column in Excel

Row to column or vice versa changes can be completed with the Paste Special functionality in a matter of seconds. Additionally, this approach duplicates the layout of your original material, which strengthens its case even further. Here, we will demonstrate to you how to move data from row to column in Excel using the Paste Special command.

Step 1:

• Firstly, select the data table below.
• Then copy the selected table by clicking CTRL+C on the keyboard.

Step 2:

• Besides, select the new cell where you want to copy your transpose data.
• Now, choose the Transpose icon.

Step 3:

• Consequently, the graphic below will convey the results of transferring the data from row to column.

Read More: How to Flip Columns and Rows in Excel

2. Utilizing TRANSPOSE Function to Move Data from Row to Column

The TRANSPOSE function converts a horizontal data range into a vertical data range and the opposite, a vertical range into a horizontal range. In this part, you will learn to use the TRANSPOSE function to move data from row to column.

Step 1:

• Select the same number of blank cells, but in the reverse direction, as there are rows and columns in your main table. Our sample data table, for instance, includes 6 columns and 6 rows total, including headings. We choose a range of 6 columns and 6 rows because the TRANSPOSE function will convert columns to rows.
• Now, type the following formula in the upper left empty cell.

Step 2:

• Finally, the graphic below will display the results of transferring the data from row to column.

Read More: How to Flip Data from Horizontal to Vertical in Excel

3. Using INDIRECT and ADDRESS Functions to Move Data from Row to Column

In Excel worksheets, this function offers a more adaptable way to convert rows to columns. Because you use a standard function rather than an array formula, it allows you to make any changes to the transposed data. The general syntax of the INDIRECT and ADDRESS functions is given below.
Formula Syntax of the INDIRECT Function

Arguments of the INDIRECT Function

• Ref_text represents a reference cited in the text.
• A1 represents a logical value designating either the A1 or the R1C1 reference style Rows are labelled numerically in A1 format, and columns are labelled alphabetically. Both the rows and the columns are given number labels in the R1C1 reference style (Optional).

Formula Syntax of the ADDRESS Function

Arguments of the ADDRESS Function

• Row_num is a number that indicates the row to use as the cell reference.
• Column_num is a numeric value that indicates the column to use as the cell reference.
• Abs_num indicates the kind of reference to return as a numeric value (Optional).
• A1 represents a logical value designating either the A1 or the R1C1 reference style Rows are labelled numerically in A1 format, and columns are labelled alphabetically. Both the rows and the columns are given number labels in the R1C1 reference style ( Optional).
• Sheet as a text value that displays the name of the worksheet (Optional).

Step 1:

• If the first row and column of your data are not 1 and A, you will need to apply a somewhat more complicated formula.
• So, we will write down the following formula.

where B4 is the cell that is top-left in our original data table.

• Then, click ENTER.

Step 2:

• Now, drag the cursor in the lower right corner of the chosen cells to copy the formula upward and downward to as many rows and columns as necessary.

Step 3:

• Finally, we shall see the outcomes of moving the data from row to column in the graphic below.

Read More: How to Convert Multiple Rows to Columns in Excel

4. Applying VBA Code to Move Data from Row to Column in Excel

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate a VBA code that makes it very easy to move data from row to column in Excel.

Step 1:

• Firstly, we will open the Developer tab.
• Then, we will select the Visual Basic command.

Step 2:

• Here, the Visual Basic window will open.
• After that, from the Insert option, we will choose the new Module to write a VBA code.

Step 3:

• Now, paste the following VBA code into the Module.
• To run the program, click the “Run” button or press F5.

Sub MoveData_From_RowToColumn()
'Declaring variables
    Dim DataRange As Range
    Dim SelectRange As Range
'Setting the Data Range
    Set DataRange = Application.InputBox(Prompt:="Choose the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
'Setting the cell where we will put our transposed data
    Set SelectRange = Application.InputBox(Prompt:="Choose the upper left cell of the selected range", Title:="Transpose Rows to Columns", Type:=8)
'Copying the Data Range
    DataRange.Copy
'Select the cell
    SelectRange.Select
'Paste the Data in selected cell
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
End Sub

MoveData_From_RowToColumn()

    Dim DataRange As Range
    Dim SelectRange As Range

   Set DataRange = Application.InputBox(Prompt:="Choose the range to transpose", _
    Title:="Transpose Rows to Columns", Type:=8)

Set SelectRange = Application.InputBox(Prompt:="Choose the upper left cell of the selected range", _
    Title:="Transpose Rows to Columns", Type:=8)

DataRange.Copy

SelectRange.Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=True

Step 4:

• Now, select the data range from B4 to G9 first.
• Then, click OK.

Step 5:

• Similarly, select the upper cell of the destination range where you want to transpose your data.
• Then, click OK.

Step 6:

• Finally, we will get the results of transposed data from row to column in the below image.

Read More: Excel Macro: Convert Multiple Rows to Columns

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

Conclusion

In this article, I’ve covered 4 handy methods to move data from row to column in Excel. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Related Articles

• Excel VBA to Transpose Array
• How to Transpose Multiple Columns to Rows in Excel
• VBA to Transpose Multiple Columns into Rows in Excel
• How to Change Vertical Column to Horizontal in Excel
• How to Paste Link and Transpose in Excel

<< Go Back to Transpose Data in Excel | Learn Excel