40

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?

  • This seems like a very specific implementation of a sort, and if its not already present in the advanced sorting options, I'd be surprised if you can find it elsewhere, as a non-VBA solution – drapkin11 Mar 14 '11 at 16:29
47

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:

enter image description here

  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.

enter image description here

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

  • 8
    This should be the selected anser to me. Thank you! – Nam G VU Feb 27 '14 at 6:45
  • 2
    It doesn't work for me. It just insert empty cells between those 2 cells – phuclv Feb 18 '16 at 10:09
  • 1
    This only works for adjacent cells which is not what the original question asked for. – jpierson Jul 14 '17 at 1:22
  • This doesn't work at all for me on Excel 2010 - it quickly flashes, but the contents of the cells remain unchanged. – Hashim Oct 12 '17 at 22:05
  • @phuclv I had the same issue as you before I realised that, for step 2, I was putting the mouse cursor on the bottom right border rather than the middle right border. – Richard Apr 16 at 12:46
16

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.

  • 1
    Excellent. This is exactly what I was looking for. Being able to use keyboard shortcuts and being able to operate on multiple cells is so much faster. – Anthony Geoghegan Nov 2 '15 at 9:36
  • This doesn't work for me. And the cells are adjacent to each other. I don't know why this doesn't work. Is there any particular explanation for this command? – Smart Humanism Nov 30 '18 at 20:07
14

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     
  • 4
    I'm not a VBA expert and have neither the time nor the inclination to become one. I also frequently use many different computers. It would be nice to have swapping available on every computer I use for Excel, without having to look-up the code for the VBA macro that does it, so that I can bind it to a hotkey. – Dan Moulding Mar 14 '11 at 19:16
  • 3
    @Dan It would be nice if everyone could have their pet functionality built into Excel, but then it would become even more a bloated mess than it already is. There is a reason why Excel has a macro language, to automate repetitive tasks like this. I posted code found through a google search. You don't have to be an expert. – ghoppe Mar 14 '11 at 19:29
  • +1 vba is the way excel allows you to add your pet functions. there are also ways of making that vba avaliable on many computers, to just yourself or to all (eg personal workbook, or addins) – chris neilsen Mar 18 '11 at 23:34
  • 9
    I would hardly call swapping the content of two cells / rows /columns to be a pet function. – Nicolai Feb 17 '12 at 11:35
  • 5
    @ghoppe Maybe not essential, but if you do a quick search on "swap the content of two cells in excel" (which you already have) it is quite obvious that Dan and I are hardly the only ones that care about this feature. – Nicolai Feb 21 '12 at 13:57
5

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.

  • this statement is incorrect. See the answer from user190216. – Karl Rookey Sep 10 '15 at 18:08
  • @KarlRookey Originally that answer stated (and it kind of still does) that the cells must be adjacent. It sounds like this may no longer be true for more recent versions of Excel. If that's the case, then, yes, there is now a better answer. Thanks! – Dan Moulding Sep 10 '15 at 18:20
  • Thanks for saying this. All the examples here have a side effect of swapping two cells (if they are adjacent) but doesn't "swap cells", it "shifts" cells. – Peter Ritchie Jun 15 at 19:10
2

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
    This works only if cells are adjacent. Otherwise the whole stuff is shifted near target cells. – tumchaaditya Jan 5 '14 at 19:45
0

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

  • 2
    Is this a standard Mac feature? The author of the question didn't specify what platform they are on. I'm not sure whether the standard clipboard in all versions of Windows holds more than one item, but free utilities are readily available to provide that functionality (and the keyboard shortcuts may vary). Could you expand your answer to make it more comprehensive for this approach? – fixer1234 Dec 5 '14 at 18:14
  • I tried this on Windows but couldn't figure it out. I even tried the ctrl+shift+p option that normally works in Visual Studio to paste out of the clipboard ring. – jpierson Jul 14 '17 at 1:21
0

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
0

Select the bottom cell you want to swap Press Ctrl + x and go to cell you want to swap with Press Ctrl + Shift + = the swap will be executed

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.