Site icon Professor Excel

Exchange Cells: Excel Hack of How to Swap Two Cells!

Sounds simple: Exchange cells in Excel. And actually it is. Here is what to regard in you a) want to exchange all references as well or b) keep the references as they are when you swap two cells.

Before we start: How to deal with cell references when you exchange cells?

Before we start, we have to talk about cell references. Do you want cell references to adapt as well? Or should they stay with the original cells?

In order to make it easier to demonstrate, please take a look at the following example: You want to exchange cell A and B (located in B2 and E2). Cell B6 refers to cell A.

Exchange cells: Should references to the swapped adapt (and also be exchanged) or stay with the original cell position?

Method A: Cell B6 should continue to refer to cell A. So the cell reference adapts from =B2 to =E2.

Method B: The cell references don’t adapt. Cell B6 continues to refer to cell B6.

A. Adapt cell references with the cells when you exchange two cells

If you want to adapt the cell refer to cell A, no matter where it is located, simple cut the cell:

The fastest way to do it manually: Cut and paste the two cells.
  1. Press Ctrl + X on the keyboard to cut cell A.
  2. Paste it into an empty cell somewhere else (outside your used range) by pressing Ctrl + V on the keyboard.
  3. Now, do the same with cell B and put it into the original position of cell A.
  4. And again, cut and paste cell A from the outside position into the final position (here: E2)

Special case: Exchange adjacent cells (next or on top of each other)

Much faster: If cells are next to each other or on top of each other. In such case you can use two very simple keyboard shortcuts to swap them:

Even faster: Exchange cells next to each other by pressing Ctrl + X on “second” cell, then select the first cell Ctrl + “+”.
  1. Cut the cell on the right (or the lower cell) pressing Ctrl + X on the keyboard.
  2. Select the other cell and press Ctrl + “+”. Excel then moves the other cell to the right or down.
Same for cells on top of each other: Cut the second (lower) cell and instead of pasting it, insert it “on top of the first cell” by pressing Ctrl + “+” on the keyboard.

B. Leave the cell references as they are

If you want your references to stay on the same cell location and don’t change with the exchange of the two cells (in our case cell B6 keeps referring to cell B2), then it is getting a bit more complicated.

You could be lucky, though, if the two cells you are going to swap don’t have references to other cells (for example within formulas or functions). In such case, this works quite fine:

If cell references should remain on the same position, use the copy (not cut) and paste functions to exchange cells.
  1. Copy cell A (Ctrl + C) and paste it somewhere outside (Ctrl + V).
  2. Now, do the same with cell B: Copy cell B (Ctrl + C) and paste into the spot where cell A has been (and actually still is – so you override it).
  3. Copy cell A (which is now somewhere outside of your used range) and paste it to the original cell B.
  4. After that you might want to clear the temporary copy of cell A.

So far, so easy, right?

If the cells that you want to exchange have formulas as well and you want to keep their references, it’s not that simple. With only two cells, I would recommend the following:

Do basically the same as before, but instead of copying and pasting complete cells, just copy and paste the cell contents:

Exchange cells: Keep “outside” references to the same position (B6 refers to B2) but keep cell references with the two swapped cells the same.

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!

How to do it with just one click

Would you believe me if I say that you could do all this with just one mouse click? There is! Besides many, many other time saving functions, our Excel add-in “Professor Excel Tools” has a function to exchange two cells. Simply select the two cells and click on “Swap Two Cells”.

Use Professor Excel Tools to exchange two cells with just one click (click on “Swap Two Cells” on the Professor Excel ribbon).

Hold on, how to differentiate if cell references should be adapted? Also very easy:

Within the settings of Professor Excel Tools you can choose the swapping mode: Should cell references adapt or stay where they are?

  1. Go to the Professor Excel ribbon and click on “Tools Settings” (located on the right-hand side).
  2. Go to the Options tab.
  3. Select if cell references should be adapted or not.
  4. Confirm with “Save and Close”.

Image by Gerd Altmann from Pixabay

Exit mobile version