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?

1

7 Answers

From:

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.

6

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.

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 

6

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.

3

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

1

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

2

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.Color Selection.Areas(1)(i).Interior.Color = Selection.Areas(2)(i).DisplayFormat.Interior.Color Selection.Areas(2)(i).Interior.Color = tempColor Next i
End Sub

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy