Have you ever wondered if there’s an easy way to swap cells in Excel?
Well, you’re in luck!
This article is all about swapping cells in Excel without any hassle or complicated methods.
We’ll walk you through the process step-by-step, making it as simple as a drag and drop.
This tutorial shows three methods for swapping cells in Excel.
Method #1: Use Drag and Drop to Swap Adjacent Cells in Excel
This is the best way to swap two cells (especially if you’re trying to swap two adjacent cells)
In the below dataset, values in cells A4 and B4 have been interchanged due to a data entry error.
We can use the drag and drop to swap cells A4 and B4 and correct the data entry error.
Below are the steps to do this:
- Select cell A4, point to the cell’s right border, and notice the four-headed arrow.
- Press and hold down the “Shift” key, drag to the right border of cell B4, and notice a display of a thick green bracket icon ( 工). While holding the “Shift” key, release the mouse button.
The cells A4 and B4 are swapped as shown below:
Note: You can also use this method to swap adjacent rows or columns.
Also read: How to Select Visible Cells Only in Excel?
Method #2: Use Cut and Insert Cut Cells to Swap Adjacent Cells in Excel
Let’s consider the below dataset where values in cells A4 and B4 have been interchanged due to a data entry error.
We can use the “Cut” and “Insert Cut Cells” commands to swap cells A4 and B4 and correct the data entry error.
Below are the steps to do this:
- Right-click cell B4 and choose “Cut” on the context menu.
Alternatively, select cell B4 and press Ctrl + X.
- Right-click cell A4 and choose “Insert Cut Cells” on the shortcut menu.
Alternatively, select cell A4 and press the Ctrl + Shift + “+” shortcut.
Cells A4 and B4 are swapped as shown below:
Note: You can also use this method to swap only adjacent columns or rows.
Also read: How to Rearrange Rows In Excel
Method #3: Use Excel VBA Code to Swap Non-Adjacent Cells in Excel
Suppose we want to swap the columns for “Capital City” and “State” in the dataset below to arrange the data in a more logical order.
We can use Excel VBA code to accomplish the task.
Below are the steps to do this:
- Press Alt + F11 to open the Visual Basic Editor.
- Open the “Insert” menu and choose “Module” to insert a module.
- Copy the following code and paste it into the module:
Sub Swap2NonAdjacentRanges() Dim Rng1 As Range Dim Rng2 As Range Dim arr1 As Variant Dim arr2 As Variant Set Rng1 = Application.Selection Set Rng1 = Application.InputBox("Range1:", , Rng1.Address, Type:=8) Set Rng2 = Application.InputBox("Range2:", , Type:=8) Application.ScreenUpdating = False arr1 = Rng1.Value arr2 = Rng2.Value Rng1.Value = arr2 Rng2.Value = arr1 Application.ScreenUpdating = True End Sub
- Save the file as an Excel Macro-Enabled Workbook.
- Press Alt + F11 to switch to the active worksheet containing the dataset.
- Select the first cell range that you want to be swapped. In this example, we select the cell range A1:A6.
- Press Alt + F8 to activate the “Macro” dialog box, select the “Swap2NonAdjacentRanges” macro on the “Macro name” list box, and click “Run.”
- Click “OK” on the first “Input” dialog box, displaying the reference of the first cell range we want to be swapped.
- Select the second cell range that you want to be swapped. In this example, we select the cell range C1:C6. Notice that the cell reference is entered in the second “Input” dialog box.
- Click “OK” on the second “Input” dialog box.
The “Capital City” and “State” cell ranges are interchanged as shown below:
Note: Always keep the target ranges equal in size when using this method.
Explanation of the Code
Let’s go over the code step by step to understand how it performs the swap operation:
1. Variable Declarations:
- ‘Dim Rng1 As Range’ and ‘Dim Rng2 As Range’: Declare two variables, “Rng1” for the first data range and “Rng2” for the second cell range.
- ‘Dim arr1 As Variant’ and ‘Dim arr2 As Variant’: Declare two array variables named “arr1” and “arr2” to store values from their respective ranges. The variables are of “Variant” data type, so they can handle data of unknown or mixed types.
2. Range Selection:
- ‘Set Rng1 = Application.Selection’: Sets the “Rng1” variable to the currently selected range, allowing the user to choose a range before running the macro.
- ‘Set Rng1 = Application.InputBox(“Range1:”, , Rng1.Address, Type:=8)’: Displays an input box to the user with the prompt “Range1:” and initially sets the input value to the address of “Rng1.” This line allows the user to change or specify the first range by manually entering or selecting it. The “Type:=8” parameter indicates that the input should be treated as a range.
- ‘Set Rng2 = Application.InputBox(“Range2:”, , Type:=8)’: Displays another input box to the user with the prompt “Range2:” and allows the user to specify the second range by manually entering or selecting it. The “Type:=8” parameter indicates that the input should be treated as a range.
3. Turning off Screen Updating:
- ‘Application.ScreenUpdating = False’: Disables screen updating to prevent flickering and improve performance while the macro executes.
4. Value Assignment:
- ‘arr1 = Rng1.Value’: Stores the values from the first range (“Rng1”) in the “arr1” array variable.
- ‘arr2 = Rng2.Value’: Stores the values from the second range (“Rng2”) in the “arr2” array variable.
5. Swapping Values:
- ‘Rng1.Value = arr2’: Assigns the values from “arr2” (second range) to the cells in “Rng1” (first range), effectively swapping the values.
- ‘Rng2.Value = arr1’: Assigns the values from “arr1” (first range) to the cells in “Rng2” (second range), completing the swap operation.
6. Turning on Screen Updating:
- ‘Application.ScreenUpdating = True’: Re-enables screen updating, allowing Excel to display changes made by the macro.
This tutorial showed three methods for swapping cells in Excel. We hope you found the tutorial helpful.
Also read: How to Swap Columns in Excel?
Situations where Swapping Cells in Excel is Useful
Swapping cells in Excel can be helpful when you need to rearrange or reorganize data.
Here are four scenarios where you may need to swap cells:
- To correct data entry errors: In case of data entry mistakes where you must interchange the content of two cells to rectify the error, swapping cells can be a convenient solution that saves time and effort.
- To put data in desired or more logical order: When importing data from external sources, the structure or order may not be as expected. Swapping columns or rows can help you to match your needs.
- To change priorities or categories: In the case of managing projects or analyzing data, you can modify the priority level of a task by switching cells from “High” to “Low.” You can also delegate a project to a different team or category or rearrange the items on a to-do list according to changing priorities.
- Comparing Data: If you want to compare two sets of data side by side, you may need to swap rows or columns to align corresponding data points. This process is often necessary when reconciling data.
Instead of cutting and pasting or retyping the values, various methods for swapping cells can save you time and make the process more efficient.
Other articles you may also like: