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. If you are curious to know how you can swap cells in Excel, then this article might come in handy for you. In this article, we will discuss, how you can swap cells in Excel in elaborate explanations.
Download Practice Workbook
Download this practice workbook below
3 Easy Ways to Swap Cells in Excel
For demonstration purposes, we will use the below dataset. In this dataset, we got the sales record of the salesman.
1. Drag Value to Adjacent Cell
Here, we will show how you can swap cell content among two cells by dragging the cell boundary.
Steps
- Here we have the sales record of the salesman mentioned in the range of cells B5:B6.
- The data mentioned in cell B5:C5 will be swapped with the B6:C6.
- Then we copy the range of cells B5:C6 to the range of cell B11:C12.
- At the same time, we also modify the table a little bit.
- With the table name with the Before and the copied table with the After.
- Then select cell B12:C12.
- Then place the arrow on the corner of the cell boundary, and press Shift.
- The cursor will reform to an arrowhead cursor.
- While holding the Shift arrow, try to drag the cursor to the below cell.
- When you try to move around your cursor to swap cell content, a heavy marker will appear.
- The marker will move and change shape according to the intended selection.
- When the shape is like the below image, release the shift button and mouse.
- After releasing the mouse, you will notice that the range of cells is now swapped.
Read More: How to Swap Non-Adjacent Cells in Excel (3 Easy Ways)
2. Using Cut-Paste Feature
Using the Cut and Paste feature in an appropriate way, we can easily swap the cell contents between two cells.
Steps
- Here we have the sales record of the salesman mentioned in the range of cell B5:B6.
- The data mentioned in cell B5:C5 will be swapped with the B6:C6.
- Then we copy the range of cell B5:C6 to the range of cell B11:C12.
- At the same time, we also modify the table a little bit.
- With the table name with the Before and the copied table with the After.
- Then we cut the range of cells B11:C11 by right-clicking on the cell.
- Then from the context menu, click on the Cut.
- Then select one cell beneath the intended cell, which is cell D13.
- Then click on the Ctrl+Shift+”=” or click Ctrl+”+”.
- After clicking the designated button, we will notice that the cells are swapped.
3. Embedding VBA Code
Using a small VBA code will drastically reduce the time required for the swap cell content in Excel.
Steps
- First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’for opening the Visual Basic Editor.
- Then there will be a new dialog box. In that dialog box, click on Insert > Module.
- Inside the Module editor window, enter the following code
Sub Swap_Cells()
Dim x As Range, y As Range
Dim m As Variant, n As Variant
xTitleId = "Exceldemy"
Set x = Application.Selection
Set x = Application.InputBox("Range1:", xTitleId, x.Address, Type:=8)
Set y = Application.InputBox("Range2:", xTitleId, Type:=8)
Application.ScreenUpdating = False
m = x.Value
n = y.Value
x.Value = n
y.Value = m
Application.ScreenUpdating = True
End Sub
- Then close the Module window.
- After that, go to View tab > Macros.
- Then click on View Macros.
- After clicking View Macros, select the macros that you created just now. The file name here is Swap_Cells. Then click Run.
- After clicking OK, there would be a range box asking for the first range that is going to be swapped.
- Enter B11:C11 and enter OK.
- After clicking OK, there would be another range box asking for the second range that is going to be swapped.
- Enter B12:C12 and enter OK.
- After clicking OK, you will notice that the cell values are swapped.
Read More: How to Swap Text in Excel (4 Easy Methods)
How to Swap Rows in Excel
We can swap multiple rows in Excel by following the method. This method involves dragging the cell boundary in a specific way.
Steps
- Then we copy the range of cells B7:F12 to the range of cells B17:C21.
- At the same time, we also modify the table a little bit.
- With the table name with the Before and the copied table with the After.
- Select the range of cells B17:F17.
- Press and hold the “Shift” key on your keyboard. Now move your cursor to the edge of the next adjacent rows until it turns into this icon.
- Click and hold your mouse and hold the “Shift”. Move your mouse and you will see a gray heavy line appears under the selected row that we want to swap.
- When you try to move around your cursor to swap cell content, a heavy marker will appear.
- The marker will move and change shape according to the intended selection.
- When the shape like the below image appears, release the shift button and mouse.
- After releasing the cursor, you will notice that the rows are now swapped.
How to Swap Columns in Excel
We can swap multiple columns in Excel by following the method. This method involves dragging the cell boundary in a specific way.
Steps
- Then we copy the range of cells B6:E14 to the range of cells B18:C26.
- At the same time, we also modify the table a little bit.
- With the table name with the Before and the copied table with the After.
- Select the range of cell B18:C18.
- Press and hold the “Shift” key on your keyboard. Now move your cursor to the edge of the next adjacent rows until it turns into this icon.
- Click and hold your mouse and hold the “Shift”. Move your mouse and you will see a gray heavy line appears side of the column.
- When you try to move around your cursor to swap cell content, a heavy marker will appear.
- The marker will move and change shape according to the intended selection.
- When the shape is like the below image, release the shift button and mouse.
- After releasing the button, you will see that the column is now swapped.
Conclusion
To sum it up, the issue of how we can swap cells in Excel is answered here in 3 different ways. The VBA Macro method requires prior VBA-related knowledge to understand from scratch.
We also answered how you can swap rows and columns in Excel.
For this problem, a macro-enabled workbook is available to download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.
=rand formula also swip data
The RAND function is supposed to return only a random value, which is used for shuffling the dataset values. In this case, we are trying to swap cell values within two separate cells. This means your statement is not applicable. If you insist that it is possible, please write down the process or send us your Excel file through email. Thank you.