How To Swap Cells in Excel (3 Methods With Tips)

You might have released the mouse button unintentionally or at the wrong time while selecting text in MS Word, which resulted in text rearrangement. Accidentally swapping text is annoying, but a swap function is still very helpful and, if you consider an application like MS Excel, a swap function is a necessity. When using MS Excel, you may need to swap data between cells or even between rows and columns if you have cells with data in them. Thankfully, MS Excel has a swap function that makes switching dates in cells simple. Here’s how it works.

The value of the cell will be switched with the cell below that line when the cursor changes to a thick horizontal “I” shape at the bottom of the adjacent cell. The value of the cell will be swapped with the cell above the adjacent one if you move it to the top of the adjacent cell and it transforms into a thick horizontal “I” shape at the top.

1. Select the cell you want to swap. In this example, select cell A4.
2. Press Shift key, and put the cursor at the right border.
3. Then drag the cursor to the right border of cell B4.
4. When there displays “工”, release the mouse.
5. And the two cell contents have been swapped.

When is it important to swap cells in Excel?

There may be times when you enter a set of data into Excel and later realize that swapping cells will improve your layout or spreadsheet readability. If you notice a discrepancy or if the data parameters have changed, you might also want to swap cells to maintain the accuracy of your spreadsheet. You can also switch around the cells in your spreadsheet to produce a more aesthetically pleasing design if you intend to share it with others.

You can also save time by switching the values of multiple cells at once rather than having to retype them all. It might be easier to swap the cells than to recreate the values and formatting elsewhere if the cells you want to swap have intricate formulas, specific values, or formatting.

What does it mean to swap cells in Excel?

Excel’s “swapping cells” function allows you to change the values of two cells at once. You may swap cells that contain text, numbers or formulas. Additionally, you can add formatting components like background colors and fonts to your cell swaps. While manually switching the cells by copying and pasting or retyping the values is an option, there are other ways to switch the cells that can help you save time and work more effectively.

How to swap cells in Excel

Here are three methods for swapping cells in Excel:

Swap adjacent cells

The steps listed below make it simple to swap adjacent cells in Excel:

Swap cells using Kutools

Kutools is an Excel add-in that can assist users in streamlining complex procedures. By following the instructions below, you can use this application to swap cells after downloading it:

Swap cells using VBA code

Using the Excel programming language Visual Basic for Applications (VBA), you can swap cells from anywhere in your spreadsheet. To swap cells using VBA code, follow these steps:

Click on the first cell you want to swap first. Although you can skip this step, doing it can make the process later on simpler. This is so that Excel can automatically fill in the location of your first cell when you run the program and input the cells you want to switch.

Press the keyboard shortcut “ALT + F11” to bring up the VBA code window. Next, go to “Insert” and select “Module. This launches a new window into which you can copy and paste the code given below:

Sub SwapTwoRange()

Updateby20131120

Dim Rng1 As Range, Rng2 As Range

Dim arr1 As Variant, arr2 As Variant

xTitleId = “KutoolsforExcel”

Set Rng1 = Application.Selection

Set Rng1 = Application.InputBox(“Range1:”, xTitleId, Rng1.Address, Type:=8)

Set Rng2 = Application.InputBox(“Range2:”, xTitleId, Type:=8)

Application.ScreenUpdating = False

arr1 = Rng1.Value

arr2 = Rng2.Value

Rng1.Value = arr2

Rng2.Value = arr1

Application.ScreenUpdating = True

End Sub

Click the tiny green forward arrow icon in the “Module” toolbar to execute the code. You can also execute the code by pressing “F5” on your keyboard, clicking “Run” in the toolbar at the top of the window, and then choosing “Run Sub/User Form.” This opens a pop-up window with the labels “Range1” and “KutoolsforExcel.” The range may already contain the code “$[Letter]$[Number],” where [Letter] and [Number] identify the first cell you selected to swap, if you selected your first cell before opening the VBA window.

At this point, you can also change the cell by manually adding or changing the identifying information, returning to your workbook, and selecting the first cell you want to switch. After verifying that it references the correct cell, press “Ok. This opens a second pop-up window that requests “Range2.” You can click on the second cell in your spreadsheet and manually enter the identifying information for it. Next, press “Ok” again. This swaps the two cells.

Tips for swapping cells in Excel

Here are some pointers that could help clarify more complex Excel swapping techniques:

Try to swap adjacent rows or columns

To swap rows or columns in Excel, use the same technique as described above for swapping adjacent cells. To do this, click the first cell in the row or column you want to swap, then drag the mouse across or down the row or column while holding down the mouse button to select a group of cells. When you do this, a green box surrounds the cells you’ve chosen.

When the bracket icon is lined up with the opposite wall of the cells you want to swap with your chosen cells while holding down the “Shift” key, click and drag the green border with your cursor. Finally, release the cursor to swap the row or column.

Consider swapping cell ranges anywhere on the spreadsheet

To swap a range of cells from anywhere on your spreadsheet, you can use either Kutools or VBA code. When the process asks you to choose the range you want to swap, choose a range of cells rather than a single cell to accomplish this. Additionally, you can manually enter a range by using the proper code. The upper-left corner of your range and the bottom-right corner of your range must be listed in the code, separated by a colon.

Using the identifying codes “$A$1:$B$3” as the range identifier for Range 1 and “$C$1:$D$3” as the range identifier for Range 2, for instance, you could indicate a swap for the range of cells A1, A2, A3, B1, B2 and B3 with the range C1, C2, C3, D1, D2 and D3.

Please note that Indeed is not affiliated with any of the businesses mentioned in this article.

Quickly Swap the Position of Two Values in Excel

FAQ

Can you swap two cells in Excel?

The steps listed below make it simple to swap adjacent cells in Excel: Click on the first cell you want to swap Press and hold down the “Shift” key on your keyboard. When the mouse changes from a white plus (+) sign to a plus sign made of black arrows, place your cursor on the right-hand cell border.

How do you swap cells in a spreadsheet?

Let’s say you want to swap the values in cells A1 and B1 Cell A1 should be moved to the right border of cell B1 while holding down the SHIFT key on the keyboard. When the mouse cursor changes to the symbol, release it. Cells A1 and B1 are now swapped.

How do you swap two rows in Excel?

When the border between the two adjacent rows turns into a cross-arrow icon, move your mouse over it. When you see a gray line appear underneath the row you want to switch the data with, click, hold “Shift,” and then hold down the mouse button. When you release the mouse button, the data will move around.

How do I swap data in two columns in Excel?

1. Select the column.
2. Click at the edge.
3. Click and drag the selected column.
4. Swapped using drag and drop.
5. swapping columns by cut and paste.
6. Swapped columns using Cut and Paste.
7. Swapping multiple columns.
8. Multiple columns swapped.