How to swap cells in Excel

While working in Excel, it often becomes necessary to change the order of cells, for example, you need to swap some of them. How to do this in different ways, we will analyze in this article.

Procedure for moving cells

There is no separate function that allows you to perform this procedure in Excel. And when using standard tools, the rest of the cells will inevitably shift, which must then be returned to their place, which will result in additional actions. However, there are methods to accomplish the task, and they will be discussed below.

Method 1: Copy

This is perhaps the easiest way, which involves copying the elements to another place with the replacement of the initial data. The procedure is as follows:

1. We get up in the first cell (select it), which we plan to move. On the main tab of the program, click on the button “Copy” (tool group “Clipboard”). You can also just press the key combination Ctrl + C.
2. Go to any free cell on the sheet and press the button “Insert” in the same tab and tool group. Or you can use the hotkeys again – Ctrl + V.
3. Now select the second cell with which we want to swap the first, and also copy it.
4. We get up in the first cell and press the button “Insert” (or Ctrl + V).
5. Now select the cell into which the value from the first cell was copied and copy it.
6. Go to the second cell where you want to insert data, and press the corresponding button on the ribbon.
7. The selected items have been successfully swapped. The cell that temporarily held the copied data is no longer needed. Right-click on it and select the command from the menu that opens “Delete”.
8. Depending on whether there are filled elements next to this cell on the right / bottom or not, select the appropriate deletion option and click the button OK.
9. That’s all that needed to be done in order to swap cells.

Despite the fact that to implement this method, you need to perform a lot of additional steps, nevertheless, it is used by the largest number of users.

Method 2: drag and drop

This method is also used to swap cells, however, in this case, cells will be shifted. So, we perform the following actions:

1. Select the cell that we plan to move to a new location. We move the mouse cursor over its border, and as soon as it changes view to the usual pointer (with 4 arrows in different directions at the end), pressing and holding the key Shift, move the cell to a new location with the left mouse button pressed.
2. Most often, this method is used to swap adjacent cells, since shifting elements in this case will not violate the structure of the table.
3. If we decide to move a cell through several others, this will change the position of all other elements.
4. After that, you will have to restore order.

Method 3: Using Macros

We mentioned at the beginning of the article that in Excel, alas, there is no special tool that allows you to quickly “swap” cells in places (with the exception of the method above, which is effective only for adjacent elements). However, this can be done using macros:

1. First you need to make sure that the so-called “developer mode” is activated in the application (off by default). For this:
   • go to the menu “file” and select from the list on the left “Parameters”.
   • in the program options, click on the subsection “Customize Ribbon”, on the right side, put a tick in front of the item “Developer” and click OK.
2. Switch to tab “Developer”, where click on the icon “Visual Basic” (tool group “the code”).
3. In the editor, by clicking on the button “View Code”, paste the code below in the window that appears:

   Sub ПеремещениеЯчеек()

   Dim ra As Range: Set ra = Selection

   msg1 = "Произведите выделение ДВУХ диапазонов идентичного размера"

   msg2 = "Произведите выделение двух диапазонов ИДЕНТИЧНОГО размера"

   If ra.Areas.Count <> 2 Then MsgBox msg1, vbCritical, "Проблема": Exit Sub

   If ra.Areas(1).Count <> ra.Areas(2).Count Then MsgBox msg2, vbCritical, "Проблема": Exit Sub

   Application.ScreenUpdating = False

   arr2 = ra.Areas(2).Value

   ra.Areas(2).Value = ra.Areas(1).Value

   ra.Areas(1).Value = arr2

   End Sub

4. Close the editor window by clicking the usual button in the form of a cross in the upper right corner.
5. Holding down a key Ctrl on the keyboard, select two cells or two areas with the same number of elements that we plan to swap. Then we press the button “Macro” (tab “Developer”, Group “the code”).
6. A window will appear in which we see the previously created macro. Select it and click “Run”.
7. As a result of the work, the macro will swap the contents of the selected cells.

Note: when the document is closed, the macro will be deleted, so next time it will need to be created again (if necessary). But, if you expect that in the future you will often have to perform such operations, the file can be saved with macro support.

Conclusion

Working with cells in an Excel table involves not only entering, editing or deleting data. Sometimes you need to move or swap cells that contain certain values. Despite the fact that there is no separate tool in the Excel functionality for solving this task, it can be done by copying and then pasting values, moving a cell, or using macros.