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.
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:
- Press Ctrl + X on the keyboard to cut cell A.
- Paste it into an empty cell somewhere else (outside your used range) by pressing Ctrl + V on the keyboard.
- Now, do the same with cell B and put it into the original position of cell A.
- 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:
- Cut the cell on the right (or the lower cell) pressing Ctrl + X on the keyboard.
- Select the other cell and press Ctrl + “+”. Excel then moves the other cell to the right or down.
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:
- Copy cell A (Ctrl + C) and paste it somewhere outside (Ctrl + V).
- 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).
- Copy cell A (which is now somewhere outside of your used range) and paste it to the original cell B.
- 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:
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”.
Hold on, how to differentiate if cell references should be adapted? Also very easy:
- Go to the Professor Excel ribbon and click on “Tools Settings” (located on the right-hand side).
- Go to the Options tab.
- Select if cell references should be adapted or not.
- Confirm with “Save and Close”.
Image by Gerd Altmann from Pixabay
