Swap cell contents in Excel?

Question

Is there an easy way to swap the contents of two cells in Microsoft Excel?

By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. In other words, I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents. Surely, there has got to be a way to do this?

Answer 1

From: http://www.extendoffice.com/documents/excel/860-excel-swap-contents-of-two-cells.html

Sometimes, there are two adjoining cells need to be swapped. We can manually do it easily. Look at the following screenshot, I want to swap cell A4 and B4, please do as follows:

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.

With this method, we can also swap two adjoining rows or columns.

Answer 2

For the specific case of adjacent same-size rectangular ranges, you can use the method described in this answer to a similar question.

1. Select the right or bottom range
2. Press Ctrl+X
3. Select the adjacent range (i.e., directly above or to the left)
4. Press Ctrl++ (the + is usually above the = key so this translates to Ctrl+Shift+=)

Note you can use the same procedure to swap whole adjacent rows or columns.

Answer 3

By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents.

Why impose this restriction? Creating a macro makes this trivial. As far as I know, it can't be done any other way. You can assign the macro to a button or hotkey.

Sub Swap()     
    If Selection.Count <> 2 Then     
         MsgBox "Select 2 cells (only) to swap."     
         Exit Sub     
    End If     
    Set trange = Selection     
    If trange.Areas.Count = 2 Then     
         temp = trange.Areas(2)     
         trange.Areas(2) = trange.Areas(1)     
         trange.Areas(1) = temp     
    Else     
         temp = trange(1)     
         trange(1) = trange(2)     
         trange(2) = temp     
    End If     
End Sub     

Answer 4

No. There is no way to swap the contents of two cells in Excel, without writing your own macro to do it.

EDIT: It sounds like there may now be an easy way to swap cell contents in more recent versions of Excel, so this answer is probably now out of date.

Answer 5

Select first set of cells to be swapped and hit ctrl+x:

Select the cells BESIDE the ones you want to swap with and hit ctrl++.

Answer 6

You can paste up to 25 items to the clipboard, so they are easy to swap using ctr+tab or cmd+tab mac

Answer 7

I read this post but actually needed a macro to swap full ranges. In addition, I needed to swap the colors. Modded the originally posted macro slightly, this might be useful for someone.

Sub Swap()

    If Selection.Areas.Count <> 2 Then
         MsgBox "Select 2 cell ranges (only) to swap."
         Exit Sub
    End If
    
    
    If Selection.Areas(1).Count <> Selection.Areas(2).Count Then
         MsgBox "The two areas must be of equal size"
         Exit Sub
    End If
    
    'With this for loop we run through each cell 1 by 1
    For i = 1 To Selection.Areas(1).Count
        'Swapping values
        temp = Selection.Areas(1)(i)
        Selection.Areas(1)(i) = Selection.Areas(2)(i)
        Selection.Areas(2)(i) = temp
        
        'Swapping color
        tempColor = Selection.Areas(1)(i).DisplayFormat.Interior.ColorIndex
        Selection.Areas(1)(i).Interior.ColorIndex = Selection.Areas(2)(i).DisplayFormat.Interior.ColorIndex
        Selection.Areas(2)(i).Interior.ColorIndex = tempColor
    Next i
    
End Sub