How to Swap Cells in Excel (3 Easy Ways)

by

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.

Cells to swap

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:

  1. Select cell A4, point to the cell’s right border, and notice the four-headed arrow.
Select the cell you want to swap
  1. 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:

Drag the cell to swap

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.

Cells to swap

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:

  1. Right-click cell B4 and choose “Cut” on the context menu.
Cut the cell

Alternatively, select cell B4 and press Ctrl + X.

  1. Right-click cell A4 and choose “Insert Cut Cells” on the shortcut menu.
Insert Cut cells

Alternatively, select cell A4 and press the Ctrl + Shift + “+” shortcut.

Cells A4 and B4 are swapped as shown below:

Cells have been swapped

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.

Dataset to swap columns

We can use Excel VBA code to accomplish the task.

Below are the steps to do this:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Open the “Insert” menu and choose “Module” to insert a module.
Insert a new module
  1. 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
  1. Save the file as an Excel Macro-Enabled Workbook.
  2. Press Alt + F11 to switch to the active worksheet containing the dataset.
  3. Select the first cell range that you want to be swapped. In this example, we select the cell range A1:A6.
Select the first cell range that you want to be swapped
  1. Press Alt + F8 to activate the “Macro” dialog box, select the “Swap2NonAdjacentRanges” macro on the “Macro name” list box, and click “Run.”
Run the macro
  1. Click “OK” on the first “Input” dialog box, displaying the reference of the first cell range we want to be swapped.
Click OK
  1. 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.
Select the second range
  1.  Click “OK” on the second “Input” dialog box.

The “Capital City” and “State” cell ranges are interchanged as shown below:

Columns have been swapped

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment