Switching chunks of cells

When I discussed the updated formulas for extracting two ISBNs in a single column to two other columns, I mentioned that if they weren’t in order and you ended up with the ISBN-13 in the ISBN-10 column, it was easy enough to switch them.

It was then pointed out that “easy enough” for me isn’t “easy enough” for everyone else. After all, I’ll bet there’s tons of people who think it’s “easy enough” run a storytime, cook a meal that involves more than one pot, or decide whether the fluids in your car need topping off, none of which I would attempt without careful preparation and/or some sort of instructions.

So today I’m going to talk about how to switch columns/rows/sets of cells in a spreadsheet, and some of the potential dangers in doing so.

There’s a relatively famous game called Towers of Hanoi, and if you’ve ever successfully played it, you’re already familiar with the method I’m going to tell you about.

Essentially, assume you have three columns: A, B, and C. A and B both have something in them, but you want the contents of A in B, and the contents of B in A. So what you do is move the contents of B to C, the contents of A to B, and the contents of C to B.

It’s kind of like kids in a van. If kid 1 is in the front seat (seat A), and kid 2 is in a back seat (seat B), and you need to switch them, there’s a few ways you could try to do it, but not all of them will work. (If you’re not sure about the seat layout I’m describing, check out this picture.)

– If you try to just have them both move at once to each other’s seats, there will be a traffic jam somewhere in the middle.

– If you try to have the kid in seat B move to the front seat (A) without moving kid that’s already there, the kid in A will be squalling because there’s now someone sitting on top of them. The same is true in reverse if you move the kid in A without moving the kid in B.

– If you have the kid in B step aside into the space between the seat and the door (hereby designated space C), and then have the kid in A move back to seat B, then the kid who was previously in B can move to the seat A.

So, if you’re in a spreadsheet and want to switch the contents of one space with another, here’s how you do it.
1) Move the contents of cell/column/row A to a blank cell/row/column.
2) Move the contents of cell/column/row B to the corresponding area A.
3) Move the contents of area C to area B.
4) Clear/remove area C.

Note 1: The blank area – You can use a previously existing blank area, or you can insert one. If you insert one I highly recommend two things.

1.1) Insert it next to one of the areas you’re switching. If you put it off to the side somewhere, it’s easier to get lost and past things in the wrong place.

1.2) Insert an entire row/column. Even if you’re just switching a1:a3 with b1:b3, go ahead and insert an entire column. When you’re done with it you’ll want to remove it, and it’s far easier to spot and remove and entire column with only three cells in it than it is to remove only three cells. As an illustration:

Before switching A2:A4 with B2:B4:

1 A1

B1 C1 D1
2 A2

B2 C1 D2
3 A3

B3 C1 D3
4 A4

B4 C1 D4
5 A5

B5 C1 D5

After switching A2:A4 with B2:B4, before removing area C…

…where C is just the necessary cells where C is a full column
A B C D E vs. A B C D E
1 A1

B1 C1 D1 1 A1

B1 C1 D1
2 B2

A2 A1 C2 D1 2 B2

A2 A2 C1 D2
3 B3

A3 A1 C3 D1 3 B3

A3 A3 C1 D3
4 B4

A4 A1 C4 D1 4 B4

A4 A4 C1 D4
5 A5

B5 C1 D5 5 A5

B5 C1 D5

Note 2: Copy & Paste vs. Cut & Paste – If you’re transferring values, you can use either copy (ctrl-v) or cut (ctrl-x) without worrying about it. But if you’re transferring formulas, you would be much safer to use cut. This is because if you copy a formula, unless you’ve told it otherwise, the row & column are considered relative. This means that they will update as you move it around. For example, if the formula in B1 is =A1, and you copy it to C1, then the formula in C1 would then be =B1. That can be useful when filling in a column where the formula for any given row is supposed to refer to the values in that row, but it’s a big problem when trying to move things without changing them. For example:


1 Hi! =A1 =A1

B and C switched with copy:

1 Hi! =#REF =B1

B1 has a missing reference error because it was moved one column to the right, and is therefore trying to find the column one to the right of A…which doesn’t exist.

Even if you’re not moving formulas around, if there are any formulas that refer to the cells you’re moving, you need to use cut. If cell B1 contains =A1, and you copy A1 and paste it into C1, then clear A1, B1 will still be =A1, and therefore blank. If you cut A1 and paste it into C1, the reference in B1 will move with it, and become =C1.