How to swap cells in Excel

How to swap cells in Excel

Today’s tutorial will show how to swap cells in Excel (adjacent and non-adjacent) using a few clicks or a small VBA macro.

Swapping cells is a fast and effective time-saving trick; you need only two clicks instead of using the usual copy-pasting or move operations. This guide is a part of our definitive guide about data cleansing in Excel.

Steps to swap cells in Excel

Follow these steps to swap adjacent cells in Excel:

  1. Click on the first cell you’d like to swap
  2. Press and hold the Shift key, and put the cursor on the right border of the target cell.
  3. Once you see the “工” symbol, release the mouse.
  4. The two cell contents are swapped.
how-to-swap-cells-in-Excel

Swap two cells with the formatting style

In most cases, we need to copy the formatting style, too. This method keeps the formatting style untouched. So, if you swap two cells with different backgrounds and number format styles, Excel will change the cell content and formats.

swap-cells-with-formatting-style

The steps are the same as in the example mentioned above. The first cell contains a blue background and a value with a percentage format. The second cell has a number value with an orange background. After the swap, all formatting styles remain the same.

Working with non-adjacent cells or ranges

If you are familiar with Visual Basic for Application (VBA) programming language, you can write a short macro to replace non-adjacent cells or ranges.

You can implement the code using the steps below:

  1. Press the Alt+F11 keyboard shortcut to open the VBA code window
  2. Use the Alt, I, M shortcut to insert a new module
  3. Save the Workbook in xlsm format

You can download the practice file that contains the ready-to-use function.

Here is the code:

Sub SwapRanges()

Dim rng As Range
Dim tmpRng As Variant
Dim aCount As Long
Dim aRows, aCols As Long
Dim s1, s2 As Integer

Set rng = Selection
aCount = rng.Areas.Count

'Error handling #1: Selection
If aCount < 2 Then
    MsgBox "Please select two ranges."
    Exit Sub
End If

'Error handling #2: Range
aRows = rng.Areas(1).Rows.Count
aCols = rng.Areas(1).Columns.Count
For s1 = 2 To aCount
    If rng.Areas(s1).Rows.Count <> aRows Or _
        rng.Areas(s1).Columns.Count <> aCols Then
        MsgBox "Columns or row numbers do not equal."
        Exit Sub
    End If
Next s1

'Error handling #3: Overlap
For s2 = 1 To aCount - 1
    For s1 = 1 + s2 To aCount
        If Not Intersect(rng.Areas(s1), rng.Areas(s2)) Is Nothing Then
            MsgBox "Selected areas must not overlap."
        End If
    Next s1
Next s2

tmpRng = rng.Areas(aCount).Cells.Formula
For s1 = aCount To 2 Step -1
rng.Areas(s1).Cells.Formula = rng.Areas(s1 - 1).Cells.Formula
Next s1
rng.Areas(1).Cells.Formula = tmpRng

End Sub

Note: this solution can replace only values!