Cut and paste versus copy and paste in Excel
The difference between cut and paste and copy and paste in Excel is best illustrated by an example. Suppose we have the scenario presented in the screenshot below, where cell B2 references cell A1 – you can see that cell B2 uses the formula “=A1”, which just means that the value in cell A1 will also appear in cell B2:
Copying and pasting a cell will change the cell references in the formula
If we copy cell B2 and paste it into another cell, then the cell references used in the formula will actually change – but, only in the new cell (so the formula in cell B2 remains exactly the same). Excel will assume that you want the relative relationship between the referenced (in this case A1) and referencing (in this case cell B2) cell to remain the same. This can be a somewhat confusing concept so take a look at the example below. Let’s say that cell B2 still references cell A1 – just as it was in our earlier screenshot.
But now, let’s say we copy cell B2 and then paste it over to cell C4 – pay special attention to the value of the cell reference in the formula bar below:
As you can see, the value in the formula bar for cell C4 is “=B3”. And, because there is nothing in cell B3, cell C4 just displays a 0. The change to the formula (from “=A1” to “=B3”) was automatically made by Excel when cell B2 was copied over to cell C4. So, the question is why does Excel make C4 point to cell B3? Why doesn’t cell C4 just point to cell A1 like the cell B2 that was used to copy it?
Why does Excel change the formula when using copy and paste?
Well, Excel will actually assume that you want to maintain the relative relationship between the cells. So, Excel will basically say that since B2 references A1, and because A1 is 1 cell left of and 1 cell higher than B2, then if B2 is copied to another cell (in this case, cell C4) then that new cell should also reference a cell that is 1 to the left and 1 cell higher (which in this case is B3). So, for that reason the formula in cell C4 will reference B3 when cell B2 is copied to another cell. Hopefully it’s clear to you now: if a cell is copied to another cell (B2 to C4), and there is a reference in the original cell (in this case “=A1”), then the new reference (in this case “=B3”) will point to the cell that is in the same relative position as the original reference was to the original cell. And, remember that Excel does this automatically.
What happens when a cell is cut and pasted instead of copied and pasted?
The first difference between cut/paste and copy/paste is that when a cell is cut and pasted into another cell, a copy of the original cell is not created. With a cut and paste, the original cell is actually moved to wherever the cell is pasted on the spreadsheet.
Cut and paste does not change the cell references in the formula
The second major difference between cut/paste and copy/paste is that if a cell is cut (not copied) and then pasted somewhere else, the original cell references in the formula bar will remain exactly the same, and stay intact. Again, an example of this will help clarify exactly how it works. Suppose we are back to our original spreadsheet, where cell B2 references cell A1 – as you can see in the formula bar:
Now, let’s say that we cut cell B2 and then paste it into cell C4. This is what we get – pay special attention to what shows up in the formula bar for cell C4:
As you can see in the formula bar in the screenshot above, cell C4 still references cell A1 – even after cell B2 was cut and pasted into cell C4.
Hopefully, you now understand the major differences between cut and paste and copy and paste in Excel.