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.


Download Practice Workbook

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


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

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 the VBA code. Let’s suppose we have a sample data set.

Handy Ways to Move Data from Row to Column in Excel


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.

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

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: Descriptive Statistics – Input Range Contains Non-Numeric Data 


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.
=TRANSPOSE(B4:G9)

 Utilizing TRANSPOSE Function to Move Data from Row to Column

Step 2:

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

Read More: How to Move Up and Down in Excel (5 Easy Methods)


Similar Readings


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

=INDIRECT(ref_text,[a1])

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

=ADDRESS(row_num,col_num,[abs_num],[a1],[sheet])

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.
=INDIRECT(ADDRESS(COLUMN(B4)-COLUMN($B$4)+ROW($B$4),ROW(B4)-ROW($B$4)+COLUMN($B$4)))

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

Formula Breakdown

  • Firstly, we use three more functions here including ADDRESS, COLUMN, and ROW.
  • Now, the row and column numbers we specify are used by the ADDRESS function to determine the cell address.
  • So, we input the coordinates in the inverse order to our formula.
  • However, this portion of the function (ADDRESS(COLUMN(B4)-COLUMN($B$4)+ROW($B$4),ROW(B4)-ROW($B$4)+COLUMN($B$4))) turns the rows to columns.
  • The rotated data is finally output via the INDIRECT function.
  • Then, click ENTER.

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

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.


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.

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

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

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

VBA Code Breakdown

  • Firstly, we specify a name for the subject as
MoveData_From_RowToColumn()
  • Secondly, we declare the variables as
    Dim DataRange As Range
    Dim SelectRange As Range
  • Now,  we set the data range as
   Set DataRange = Application.InputBox(Prompt:="Choose the range to transpose", _
    Title:="Transpose Rows to Columns", Type:=8)
  • Now, we set the cell where we would like to put our transposed data as
Set SelectRange = Application.InputBox(Prompt:="Choose the upper left cell of the selected range", _
    Title:="Transpose Rows to Columns", Type:=8)
  • Besides, we will copy the data range as
DataRange.Copy
  • Besides, we will select the data range as
SelectRange.Select
  • Finally, we paste the data into the selected cell as
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.

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


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. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo