How to Swap Cells in Excel (3 Easy Methods)

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.

How to Swap Cells in Excel


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.

Drag Value to Adjacent Cell to Swap Cells in Excel

  • 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.

Using Cut-Paste Feature to swap cells in Excel

  • 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.

cut the cell value to other cell

  • Then select one cell beneath the intended cell, which is cell D13.
  • Then click on the Ctrl+Shift+”=” or click Ctrl++”.

press buttons to swap values in Excel

  • 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.

Embedding VBA Code to swap cells in Excel

  • 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.

enable macros to run vba

  • 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.

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.

Swap cell values of rows in Excel

  • 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.

drag heavy marker to swap rows

  • 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.

Swap cell values of Columns in Excel

  • 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.

drag heavy marker to swap columns

  • 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.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

2 Comments
  1. =rand formula also swip data

  2. Reply
    Rubayed Razib Suprov Oct 2, 2022 at 12:41 PM

    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.

Leave a reply

ExcelDemy
Logo