How to quickly swap contents of two cells in Excel?

Sometimes users enter data in the wrong column, due to this user need to swap the available contents between the two cells. This can be done in many possible ways. In this article, we will swap the contents of the two cells in Excel by using the normal drag and drop facility, and by using the VBA code. Both the provided examples are accurate, precise, and thorough. By following all the guided steps, the user can easily swap the contents of the two cells. After swapping the values will move back to their original supposed location or cell in the Excel spreadsheet.

To Swap the Content of Two Cells in Excel by using the Drag and Drop Facility

• Step 1 − In this article, the user will learn the process of swapping contents from the two cells in excel. To do so assume two columns, that is column I and column II. Both these columns are provided below −

• Step 2 − In the provided sheet two column values B and C of 5 th row can be swapped with each other. Select both the cells, click on the B5, and C5 cells. Both the Selected cells are provided below for reference −

• Step 3 − Press "Shift" key, and place the cursor at the right border. Then drag the cursor to the right border of cell C4. This will display a "工", symbol. After the appearance of this symbol release the mouse. After that both the cell contents have been swapped. Sample snapshot of the swapped content can be shown below −

To Swap Content of Two Cells in Excel by using the VBA Code

• Step 1 − Assume the same excel sheet, as chosen for the above example. Snapshot for reference is provided below −

• Step 2 − After that click on the "sheet 1" tab, and among the list of displayed options, the user can select the "View Code" option. Consider the snapshot provided below −

• Step 3 − The above step will open a "Microsoft Visual Basics for Applications" dialog box. This dialog box is a blank code area. Here, the user can type the required VBA code. This VBA code will perform the required task accordingly. A snapshot for reference is provided below −

• Step 4 − Code to copy is provided below −

' define function header
Sub swapping_two_inputs()
' declare required variables
Dim r1 As Range, r2 As Range
Dim a As Variant, a2 As Variant
' setting id title
x_id_title = "x title box"
' calculate value of r1
Set r1 = Application.Selection
' set input value of r1
Set r1 = Application.InputBox("Range1:", x_id_title, r1.Address, Type:=8)
' calculate value of r2
Set r2 = Application.InputBox("Range2:", x_id_title, Type:=8)
' here screenupdate will remain false
Application.ScreenUpdating = False
' assign value to r1
a = r1.Value
' assign value to r2
a2 = r2.Value
' assign value to a2
r1.Value = a2
r2.Value = a
' set screnupdate to true
Application.ScreenUpdating = True
End Sub

Snapshot for VBA code is provided below −

• Step 5 − Click on the "Run" button. This will open a "Macros" dialog box. From the dialog box, click on the defined function or module name, and further click on the "Run" button. snapshot for the same is provided below −

• Step 6 − The above-provided step will display an "x title box" dialog box on the console. in this box select the value from the first column, that contains the value to be swapped. After that click on the "OK" button. In this case select cell B5, snapshot for reference is provided below −

• Step 7 − As soon as the user clicks on the "OK" button. Another dialog box will appear to read another input. In this input label enter the value the user wants to swap from the IInd column. After that click on the "OK" button. A snapshot of the same is provided below −

• Step 8 − The final swapped values are displayed below −

Conclusion

The above provided article allows user to swap the values available in two different columns with each other. Here, each value is swapped with another column value. In the first example, the user can use the normal drag and drop method, while the second example allows user to simply use VBA code to perform the same task.