Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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
    range1.Cut
    range2.Insert shift:=xlShiftToRight
    Range(range1Address).Delete shift:=xlToLeft

    range2Address = range2.Address
    range2.Cut
    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 solved it with this elegant step by step guide.

How to swap rows or columns in Excel?

share|improve this answer
1  
Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. Also, please don't ask new questions in answers. Thanks –  slhck Aug 1 '13 at 20:41
    
pastebin.com/BQzCSxLa –  Rapier Aug 2 '13 at 19:07
1  
I read your message, and I can understand it might be hard getting used to the Stack Exchange Q&A format, however please note that we are not a forum. Questions are expected to be specific and clear enough, and answers should only be used as answers, not to request further information. Our About page summarizes this as well. –  slhck Aug 2 '13 at 19:12
    
In a case I won't get ubann, my excel issues are gone. –  Rapier Aug 2 '13 at 19:24

Your Answer

 
discard

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.