How to Swap Non-Adjacent Cells in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to swap non-adjacent cells in Excel, this article is for you. Swapping values can be time-saving for correcting any mistakes of putting wrong values in a certain cell. So, let’s get into the main article to know the easy ways of doing this job.

Table of Contents Expand

What Does It Mean by Swapping Non-Adjacent Cells?

Swapping non-adjacent cells means interchanging the contents- texts, numbers, formulas, etc. of two separated distant cells. There are shortcuts to do this job rather than manually typing the values we want in each of these cells.

Swap Non-Adjacent Cells in Excel: 3 Easy Ways

Here, we have the following dataset containing the sales records of a company for different products in different regions. By using this dataset, we will try to swap values of non-adjacent cells in the Product column.

We have used Microsoft 365 version for creating this article. However, you can use any other version at your convenience.

Method-1: Using Find and Replace Option to Swap Non-Adjacent Cells in Excel

In this section, we will be using the Find and Replace feature of Excel to switch Mango and Apple to their non-adjacent cells.

Steps:

Firstly, we will replace Apple as situated in the second position.

Go to the Home tab >> Editing group >> Find & Select dropdown >> Replace.

After that, the Find and Replace dialog box will pop up.

Type Apple in the Find what box and Mango in the Replace with box.
Click on the Find Next option.

It will find out the cell containing Apple.

Press Replace.

In this way, Apple will be replaced by Mango.

Now, we will change Mango with Apple remains in the first place.

In the Find and Replace dialog box, write down Mango in the Find what box, and Apple in the Replace with box.
Click on the Find Next option.

It will find out the first cell containing Mango.

Press Replace.

Eventually, the replacement of Mango with Apple will be completed and our intention of swapping Mango with Apple will be done.

Method-2: Using VBA with InputBox Method to Swap Values of Non-Adjacent Cells

Using a VBA code we will swap Mango with Apple in this method.

Step-01:

Go to the Developer tab >> Code group >> Visual Basic.

Afterward, the Visual Basic Editor will open up.

Go to the Insert tab >> Module

After that, a new module Module 1 will be created.

Step-02:

Type the following code.

Sub change_values_nonadjacent_1()
Dim cell_value1, cell_value2 As Range
Dim hold_1, hold_2 As Variant
xTitleId = "Exceldemy"
Set cell_value1 = Application.Selection
Set cell_value1 = Application.InputBox("The first cell value is", _
xTitleId, cell_value1.Address, Type:=8)
Set cell_value2 = Application.InputBox("The second cell value is", _
xTitleId, Type:=8)
hold_1 = cell_value1.Value
hold_2 = cell_value2.Value
cell_value1.Value = hold_2
cell_value2.Value = hold_1
Application.ScreenUpdating = True
End Sub

Code Breakdown

We have declared cell_value1, cell_value2 as Range, and hold_1, hold_2 as Variant.
Using the InputBox method, we will take input ranges for the two cells and then assign them to cell_value1, and cell_value2.
hold_1, hold_2 will work as temporary holders of the values in cell_value1, and cell_value2.
After that, we just swapped the values using the values of the temporary holders.

Press F5 to Run the code.

Later, the first input box will appear.

Select the first cell value Mango in cell B5 and press OK.

After that, the second input box will appear.

Select the second cell value Apple in cell B8 and press OK.

Finally, the result of the swapping of Mango with Apple will be visible.

Method-3: Assigning Values to Specific Cells in a VBA Code to Alternate Values

Here, we will be assigning the new values of the specific cells in a code and after running this code the values will be switched.

Steps:

Follow Step-01 of Method-2.
Write down the following code in a new module.

Sub change_values_nonadjacent_2()
Range("B5").Value = "Apple"
Range("B8").Value = "Mango"
End Sub

Here, we have defined the value of cell B5 will be Apple and the value of cell B8 will be Mango.

Press F5 to Run the code.

In this way, we have interchanged Mango with Apple.

Swap Values of Adjacent Cells in Excel

Here, we will be interchanging the values of two adjacent cells, Guava with Kiwi.

Select the first cell of the two adjacent cells.
Follow Step-01 of Method-2.

Write down the following code in a new module.

Sub change_values_adjacent()
Dim value_temp As String
If Selection.Cells.Count = 1 Then
With Selection
value_temp = .Cells(1).Value
.Cells(1).Value = .Cells(2).Value
.Cells(2).Value = value_temp
End With
Else
MsgBox "There is more than One cell"
End If
End Sub

Here, we have used the VBA IF statement to ensure if a cell is selected or not. Then using the WITH statement we have removed the need of typing Selection many times. Then, we swapped the values.

Press F5 to Run the code.

In this way, you will be able to switch Guava with Kiwi.

Practice Section

For doing practice, we have added a Practice portion on each sheet on the right portion.

Download Practice Workbook

Swap Non-Adjacent values.xlsm

Conclusion

In this article, we tried to show the ways to swap non-adjacent cells in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

<< Go Back to Swap Cells | Excel Cells | Learn Excel