Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using Excel 2010. Suppose you have this data in a spreadsheet:

line:   content:
1       data 1
2       data 2
3       data 3
4       data 4
5       data 5

You made a mistake and now need to switch the values in the content column (but not the line column) between rows 2 and 4. It should now look like:

line:   content:
1       data 1
2       data 4
3       data 3
4       data 2
5       data 5

How do I switch a column without changing the data in other columns?

To give another example, if I type


and then realize that I confused the small, sour citrus fruits, I want to select rows 2 and 4 (i.e., I want to select the entire rows),


and then automagically switch some but not all of the cells in those two rows.


Let’s assume that the column subset is constant; for example (as illustrated above), I would always want to leave columns A and B in place, and swap columns C, D, and E.

No matter what table it is, no matter what data is there, no matter data correlation. I need to select two rows (or columns), and swap them mutually.

  • row1: abcdefg
  • row2: hijklmn

Now after switching:

  • row1: hijklmn
  • row2: abcdefg
share|improve this question
You don't seem to want to clarify the terms row and column with the term cell. Perhaps you should read a bit on excel terminology and some take another crack at a question that is a bit more clear, for us simpletons. – Raystafarian Jul 30 '13 at 10:34
@Raystafarian The question is rather clear to me. The OP wants to swap two entire rows or columns, that's all. Rapier, if you would be so kind as to check our formatting help which explains why the post doesn't look like you wanted it (but there's a preview window below for you to check). – slhck Jul 30 '13 at 10:38
@slhck that's what I thought, but OP seems to want to swap cells, not entire rows or columns. For instance in the first example line doesn't change at all, but content does; same for lemon going from green to yellow – Raystafarian Jul 30 '13 at 10:49
Now I see what you mean, @Raystafarian. Yeah, they said "switch some but not all of the cells in those two rows" — the question is how to "automagically" know which cells are to be swapped when entire rows are selected. – slhck Jul 30 '13 at 10:55
Sounds like OP would need VBA that works with selection or asks for user input. – Raystafarian Jul 30 '13 at 11:01

Lazy way to swap cells

Use a VBA macro to swap single cells or congruent rectangle ranges

enter image description here

How to use

  1. Open Excel » Press Alt+F11 » Copy & paste the code to a new module
  2. Go back to Excel and select your two ranges to swap.
    They have to match in size and cells count like shown in the example above
  3. Press Alt+F8 and execute the swap macro.

Sub swap()

    If Selection.Areas.Count <> 2 Then Exit Sub

    Set range1 = Selection.Areas(1)
    Set range2 = Selection.Areas(2)

    If range1.Rows.Count <> range2.Rows.Count Or _
        range1.Columns.Count <> range2.Columns.Count Then Exit Sub

    range1Address = range1.Address
    range2.Insert shift:=xlShiftToRight
    Range(range1Address).Delete shift:=xlToLeft

    range2Address = range2.Address
    Range(range1Address).Insert shift:=xlShiftToRight
    Range(range2Address).Delete shift:=xlToLeft

End Sub

Tip: Assign a shortcut + Place the code in an Excel Add-In for even more comfort. Within an Add-In the new swap functionality is available in all your Excel sheets without the need to copy the code every time.

share|improve this answer
I'm tired and the code can't yet handle entire rows or columns. I'll update it tomorrow or someone else improve the small macro. I would appreciate it – nixda Aug 1 '13 at 23:08
nixda I have tried it, but I don't know how exactly. Never made it before. Pls can you describe step by step ? Thanks. – Rapier Aug 3 '13 at 13:00

I found nixda's answer very helpful to use as a starting point, but for whatever reason it didn't work on my computer. With no VBA experience before today, I was able to come up with this, which definitely works, and also has the advantage of being a lot more comprehensible to someone without VBA experience:

Sub swap()

    If Selection.Areas.Count <> 2 Then Exit Sub

    Dim temp As Variant

    Set range1 = Selection.Areas(1)
    Set range2 = Selection.Areas(2)

        If range1.Rows.Count <> range2.Rows.Count Or _
        range1.Columns.Count <> range2.Columns.Count Then Exit Sub

    temp = range1.Value
    range1.Value = range2.Value
    range2.Value = temp

End Sub
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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