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

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.


Download Workbook


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.


3 Ways to Swap Non-Adjacent Cells in Excel

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.

swap non adjacent cells in excel

We have used Microsoft Excel 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.

using find and replace option to swap non adjacent cells in excel

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
  • Click on the Find Next

find

It will find out the cell containing Apple.

  • Press Replace.

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
  • Click on the Find Next

find again

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.

result due to swap non adjacent cells in excel


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.

InputBox method to swap non adjacent cells in excel

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

VBA code to swap non adjacent cells in excel

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.

first input box

After that, the second input box will appear.

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

second input box to swap non adjacent cells in excel

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.

assigning values to specific cells to swap non adjacent cells in excel

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.

swap values of adjacent cells

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

select cell

  • 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 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.

practice section to swap non adjacent cells in excel


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.

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo