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 to perform it 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:
- Click on the first cell you’d like to swap
- Press and hold the Shift key, and put the cursor on the right border of the target cell.
- Once you see the “工” symbol, release the mouse.
- The two cell contents are swapped.
Swap two cells with the formatting style
In most cases, we need to copy the formatting style too. Good to know that 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.
The steps are the same as in the example mentioned above. The first cell contains 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:
- Press the Alt+F11 keyboard shortcut to open the VBA code window
- Use the Alt, I, M shortcut to insert a new module
- 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
Important: this solution can replace only values!