How to Swap Rows in Excel (4 Easy Ways)

While working in Excel, we might need to re-arrange data between rows and cells. Sometimes there is a possibility when you need to swap your data cells or range between rows and columns. In this article, we will learn four simple methods to swap rows in Excel. So, let’s start this article, and explore these methods.

4 Simple Methods to Swap Rows in Excel

In this section of the article, we will discuss 4 convenient methods to swap rows in Excel. Let’s say, we have the following table as our dataset. Our goal is to swap the rows of the table in 4 different methods.

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

1. Applying Copy and Paste Commands

Applying the Copy and Paste commands is one of the most effective ways to swap rows in Excel. You can follow the steps mentioned below to do this.

Steps:

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

As a result, a blank row will be created below the row of “Emily” as shown in the following image.

Now, select the row of “Tywin” and press CTRL + C to copy the row.

Then, click on the row number of Row 6 and press the keyboard shortcut CTRL + V.

Finally, select Row 9 and right-click on any one of the cells of the row, and choose the Delete option. This will delete the duplicate row of “Tywin”.

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

2. Using Mouse and SHIFT Key

Using the mouse and the SHIFT key is another smart way to swap rows in Excel. It is also the most popular method to swap rows. It’s quite simple to do. Now, use the procedure discussed below.

Steps:

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

Then, 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.
Following that, let go of the SHIFT key and release the left mouse button.

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

3. Utilizing Keyboard Shortcut

Using the keyboard shortcut is one of the easiest ways to swap rows in Excel. Now let’s follow the procedure discussed in the following section.

Steps:

Firstly, select the row you want to swap. Here, we selected row 6.
Then, press the keyboard shortcut CTRL + X.

After that, select the first cell of the row where you want to place the cells of row 6. In this case, we selected cell B5.
Finally, use the keyboard shortcut CTRL + SHIFT + = to swap Rows 6 and 5.

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

4. Using VBA Macro

VBA Macro is an extremely useful tool in Microsoft Excel. It provides us with a one-click solution to swap rows in Excel. Let’s use the steps mentioned below to achieve this.

Steps:

- Firstly, go to the Developer tab from Ribbon.
- Now, select the Visual Basic option from the Code group.

As a result, the Microsoft Visual Basic window will open on your worksheet.

After that, in the Microsoft Visual Basic window, go to the Insert tab.
Then, from the drop-down menu, select Module.

Then, 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

Firstly, 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.
After that, we declared a variable temp_range as Variant.
Then, we assigned our first selected range in the range_1 variable and the second range in the range_2 variable.
Then, we used an IF statement to avoid swapping multiple rows with a single row. Only one row can be selected for the range variables.
After that, 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.

After writing the code, click on the Save icon as marked in the following image.

Now, use the keyboard shortcut ALT + F11 to return to the worksheet.
Then, select the rows you want to swap and go to the Developer tab from Ribbon.
Following that, choose the Macros option from the Code group.

Then, choose the swap_rows option in the Macros dialogue box.
Lastly, click on Run.

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

How to Swap Rows and Columns in Excel

In this section of the article, we will learn how to swap both rows and columns at the same time. To do this, let’s follow the steps mentioned in the following section.

Steps:

Firstly, select the entire data set and then press the keyboard shortcut CTRL + C to copy it.

Then, select the cell where you want to paste it. In this case, we selected cell B12.
After that, go to the Home tab from Ribbon.
Now, choose the Paste option.
Next, select the Paste Special option from the drop-down menu.

Subsequently, from the Paste Special dialogue box, check the field of Transpose.
Finally, click OK.

That’s it! You will see that the rows and columns have swapped positions as demonstrated in the following image.

You can also use a number of methods to swap rows and columns in Excel.

How to Swap Columns in Excel

While working in Excel, we often need to swap columns to edit our dataset. In Excel, we can do this by following some simple steps. These steps are quite similar to the steps used in the 2nd method of this article. Now, let’s explore these steps that are required to swap columns in Excel.

Steps:

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

Now, 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.
After that, let go of the SHIFT key and release the left mouse button.

As a result, you will see that the Books column is swapped with the Pencil column as demonstrated in the picture below.

You can also swap columns in Excel by using any of the methods discussed in this article.

How to Swap Cells in Excel

In Excel, you can not only swap the rows and columns but also the individual cells. This swapping procedure is also similar to the 2nd method of this article. Now, let’s follow the instructions outlined below.

Steps:

Firstly, select the cell you want to swap. Here, we selected cell C9.

Following that, 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.
Subsequently, let go of the SHIFT key and release the left mouse button.

Consequently, your selected cell will be swapped as demonstrated in the following picture.

You can also use any of the methods discussed in this article 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 the swapping between rows and columns is done on method 3, remember that this 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.

Download Practice Workbook

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

Swapping Rows.xlsm

Conclusion

So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to swap rows in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below.

How to Swap Rows in Excel (4 Easy Ways)

4 Simple Methods to Swap Rows in Excel

1. Applying Copy and Paste Commands

2. Using Mouse and SHIFT Key

3. Utilizing Keyboard Shortcut

4. Using VBA Macro

How to Swap Rows and Columns in Excel

How to Swap Columns in Excel

How to Swap Cells in Excel

Things to Remember

Practice Section

Conclusion

Further Readings

Leave a reply Cancel reply