How to Office

How to Swap Rows in Excel – Full Tutorial

Well, when we are handling a worksheet, then we may need to swap the contents of two cells most of the time. Generally, most of us actually know “to cut, paste, and copy” for swapping the contents. However, it’s time-consuming actually. We are going to talk about how can you instantly swap contents in cells in Excel. In this article, we are going to talk about How to Swap Rows in Excel – Tutorial. Let’s begin!

When choosing text in MS Word, then you guys might have accidentally tapped at the wrong time. Or release the mouse button when you did not really intend to and ended up rearranging text. Swapping text, when done accidentally is really irritating but a swap function is nevertheless really useful. And if you guys think about an application such as MS Excel, a swap function is a must-have.

Along with MS Excel, you have cells filled with data that you may need to swap between cells, or even between rows and columns as well. Fortunately, MS Excel has a swap function that actually lets you swap date in cells easily. Let’s see how it actually works.

How to Swap Rows in Excel

  • First, open the Excel file that you want to swap cells, columns, or rows in.
  • Choose the cell, row, or column you need to swap the contents of and hold down the Shift key. Then, tap at the rightmost border of the cell, and drag it outwards as well. However, do not release the Shift key.
  • Whenever the cursor becomes a thick horizontal ‘I’ shape at the bottom of the adjacent cell. Then it will swap the value of the cell along with the cell below that line. Likewise, if you guys move it to the top of the adjacent cell and then it turns into a thick horizontal ‘I’ shape at the top. The value of the cell will be then swapped along with the cell above the adjacent one.

how to swap rows in excel

If, somehow, you drag it to the right of the adjacent cell and the cursor transforms into a thick ‘I’. Then the contents of the selected cell will be swapped out with those in the adjacent cell.

  • In order to swap values between rows and columns, select the whole row or column. And then hold down Shift before dragging your cursor out to the row or column you need to swap its values with as well.

Now release the Shift key and the mouse button when you have got the cursor positioned over. The place you want to swap the contents of the cell/row/column to.

Swap Two Nonadjacent Cell/Rows Contents along With VBA Code in Excel

When the two cells are nonadjacent, then the above method will not really work. However, we can swap them along with the VBA code.

  • Tap on the Ctrl key and choose the two nonadjacent cells that you want to swap.
  • Tap on Developer>Visual Basic, a new Microsoft Visual Basic for applications window will be displayed. Press Insert>Module, and input the following code into the Module as well:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub SwapTwoRange()
'Updateby20131120
Dim Rng1 As Range, Rng2 As Range
Dim arr1 As Variant, arr2 As Variant
xTitleId = "KutoolsforExcel"
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("Range1:", xTitleId, Rng1.Address, Type:=8)
Set Rng2 = Application.InputBox("Range2:", xTitleId, Type:=8)
Application.ScreenUpdating = False
arr1 = Rng1.Value
arr2 = Rng2.Value
Rng1.Value = arr2
Rng2.Value = arr1
Application.ScreenUpdating = True
End Sub
  • Then press the play button to run the code, then a dialog is displayed for you to choose the two cells you wish to swap between.
  • Tap on Ok in the popped-up dialog, the two selected cells are swapped.

Notes: This VBA code can’t actually swap the formatting of the two cells.

Swap Two Random Cell/Rows Contents along With Only Several Clicks in Excel

Along with Kutools for Excel‘s Swap Ranges utility, we not only can swap two adjoining cells, rows, or columns actually. However, also can switch two nonadjacent cells, rows, or columns easily.

Before you apply Kutools for Excel, please download and install it first.

  • Tap on Kutools > Range Swap Ranges,

How to Swap Rows in Excel

Now in the Swap Ranges dialog box, you guys need to:

    • Press the first button in the Swap Range 1 box in order to select the cells that you like to swap.
    • Tap on the second button from Swap Range 2 box to choose the cells you will swap with.
      Tips: You can also choose the two ranges before applying the feature. Then you can see the selected two ranges are displayed in the Swap Range 1 and Swap Range 2 boxes separately as well.
    • Press the OK button.

Then two selected cells or ranges are swapped immediately as well.

Conclusion

Alright, That was all Folks! I hope you guys like this “how to swap rows in excel” article and also find it helpful to you. Give us your feedback on it. Also if you guys have further queries and issues related to this article. Then let us know in the comments section below. We will get back to you shortly.

Have a Great Day!

Also See: FBI Fingerprint Background Check for Employment

About the author

Windy Moore

Leave a Reply