How to Swap Rows in Excel (4 Easy Ways)

For this tutorial, we’ll use the following table as our dataset. Our goal is to swap the rows of the table using 4 different methods.

how to swap rows in excel


Method 1 – Apply Copy and Paste Commands to Swap Rows

Steps:

  • Let’s say we want to swap the entire row named “Tywin” and place him under “Emily”. To do that, select the row number of “Jon” by right-clicking the mouse.
  • Choose Insert from the Context Menu.

Applying Copy and Paste Commands to swap rows in excel

A blank row will be created below the row of “Emily” as shown in the following image.

  • Select the “Tywin” row and press CTRL + C to copy it.

  • Click on Row 6 and press the keyboard shortcut CTRL + V.

Pasting copied row to swap rows in excel

  • Select Row 9 and right-click on any one of the cells in the row.
  • Choose the Delete option to delete the duplicate “Tywin” row.

You will see that the Tywin row is now under Emily row, as marked in the image below.

Final output of method 1 to swap rows in excel

Read More: How to Paste Transpose in Excel Using Shortcut


Method 2 – Use the Mouse and SHIFT Key to Swap Rows

Steps:

  • Select the entire row you need to swap. Here, we selected row 6 from our dataset.

Using Mouse and SHIFT Key to swap rows in excel

  • Press and hold your SHIFT key and drag the selected row by left-clicking your mouse to your desired location until you see a solid green line as shown in the following picture.
  • Release the SHIFT key release the left mouse button.

Row 6 and row 5 will swap their positions as shown in the following image.

Final output of method 2 to swap rows in excel


Method 3 – Utilize Keyboard Shortcuts to Swap Rows

Steps:

  • Select the row you want to swap. Here, we selected row 6.
  • Press the keyboard shortcut CTRL + X.

Utilizing Keyboard Shortcut to swap rows in excel

  • Select the first cell of the row where you want to place the cells of row 6. In this case, we selected cell B5.
  • Use the keyboard shortcut CTRL + SHIFT + = to swap Rows 6 and 5.

You should see that row 6 and row 5 of the initial dataset have swapped positions, as demonstrated in the following picture.

Final output of method 3 to swap rows in excel


Method 4 – Use a VBA Macro to Swap Rows

Steps:

    • Go to the Developer tab from Ribbon.
    • Select the Visual Basic option from the Code group.

Using VBA Macro to swap rows in excel

The Microsoft Visual Basic window will open on your worksheet.

  • In the Microsoft Visual Basic window, go to the Insert tab.
  • From the drop-down menu, select Module.

  • Write the following code in the newly created Module.
Sub swap_rows()
If Selection.Areas.Count <> 2 Then Exit Sub
Dim temp_range As Variant
Set range_1 = Selection.Areas(1)
Set range_2 = Selection.Areas(2)
If range_1.Rows.Count <> range_2.Rows.Count Or _
range_1.Columns.Count <> range_2.Columns.Count Then Exit Sub
temp_range = range_1.Value
range_1.Value = range_2.Value
range_2.Value = temp_range
End Sub

Code Breakdown

  • We initiated a sub procedure named swap_rows().
  • Then, we used an IF statement to check if the selected number of rows is equal to 2 or not. If it is not equal to 2, then it will exit the sub procedure.
  • Next, we declared a variable temp_range as Variant.
  • We then assigned our first selected range in the range_1 variable and the second range in the range_2 variable.
  • The next part of the code uses an IF statement to avoid swapping multiple rows with a single row. Only one row can be selected for the range variables.
  • We assigned the value of the range_1 variable in the temp_range variable.
  • Next, we assigned the value of the range_2 variable to the range_1 variable.
  • Then, we reassigned the value of the temp_range variable to the range_2 variable.
  • Finally, we terminated the sub procedure.
  • Click on the Save icon as marked in the following image.

  • Use the keyboard shortcut ALT + F11 to return to the worksheet.
  • Select the rows you want to swap and go to the Developer tab from Ribbon.
  • Choose the Macros option from the Code group.

  • Choose the swap_rows option in the Macros dialogue box.
  • Click on Run.

Row 6 and row 5 will be swapped as shown in the following picture.

Final output of method 4 to swap rows in excel


How to Swap Rows and Columns in Excel

Steps:

  • Select the entire data set and press the keyboard shortcut CTRL + C to copy it.

How to Swap Rows and Columns in Excel

  • Select the cell where you want to paste it. In this case, we selected cell B12.
  • Go to the Home tab from Ribbon.
  • Choose the Paste option.
  • Select the Paste Special option from the drop-down menu.

  • From the Paste Special dialog box, check Transpose field
  • Click OK.

You will see that the rows and columns have swapped positions as demonstrated in the following image.

Final output of method 5 to Swap Rows and Columns in Excel


How to Swap Columns in Excel

Steps:

  • Select the entire column you need to swap. Here, we selected the column of Books from our dataset.

How to Swap Columns in Excel

  • Press and hold your SHIFT key and drag the selected column by left-clicking your mouse to your desired location until you see a solid green line as shown in the following picture.
  • Let go of the SHIFT key and release the left mouse button.

You will see that the Books column is swapped with the Pencil column as demonstrated in the picture below.

Final output of method 6 to Swap Columns in Excel


How to Swap Cells in Excel

Steps:

  • Select the cell you want to swap. Here, we selected cell C9.

How to Swap Cells in Excel

  • Press and hold your SHIFT key and drag the selected cell by left-clicking your mouse to your desired location until you see a solid green line as shown in the following picture.
  • Let go of the SHIFT key and release the left mouse button.

Your selected cell will be swapped as demonstrated in the following picture.

Final output of method 7 to Swap Cells in Excel

Read More: How to Swap Cells in Excel


Things to Remember

  • While swapping rows using Method 2, you need to keep holding your mouse key until you complete your swap
  • When swapping between rows and columns is done in Method 3, remember that the Transpose function is static. That means, if you make changes in the source data, the swapped value will not change.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

practice section to swap rows in excel


Download Practice Workbook

Download this practice sheet to practice while you are reading this article.


Further Readings

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo