From the Not Just Numbers blog:
I hope those who celebrate it have enjoyed the Easter break.
I just have a short, but very important, post this week as we ease back into work.
This is a warning about the very significant difference between “cut and paste” (or “drag and drop”) and “copy and paste”, when it comes to Excel.
In most other applications, e.g. Word, the difference is negligible. Cutting or dragging moves the information (removing it from its old position) and copying does the same thing but leaves the original information in place too.
There is a far greater difference between these two methods in Excel and the implications of not understanding this difference can cause havoc with your spreadsheets.
The reason for the difference is the potential interconnected nature of the information being copied or moved in Excel. The cell being copied or moved may have formulae referring to it elsewhere in the spreadsheet, and/or may refer to other cells itself – a scenario that does not occur in most other applications.
When this is the case, Excel treats cutting and pasting very differently to copying and pasting.
Let us look at Copying and Pasting first as this is the simpler one. This has no impact on any cells referring to the copied cell. They still point at the original copy. However, when the copy is pasted elsewhere in the sheet, any cell references are treated as relative to the cell’s position. – so if the original formula referred to the cell above it, the copied formula would refer to the cell above the its new position. This can be overridden by using the dollar sign in the original formula.
Cutting and pasting, which is exactly the same as dragging and dropping, works very differently. This time, any cells referring to the moved cell are amended to refer to the cell in its new position. Also, all cell references to other cells are treated as absolute (whether or not the dollar signs are used).
Here is a simple table for ease of reference:
| Copy and Paste | Cut and Paste (or Drag and Drop) | |
| References to other cells | Treated as relative unless dollar sign is used on the original reference, as this forces Excel to treat the reference as absolute. | All references treated as absolute. |
| Cells referring tothe moved cell | No change. These still refer to theoriginal location of the cell. | All formulae referencing the moved cell are amended to refer to the cell in itsnew position. |
One of the biggest risks caused by not understanding this difference is users cutting and pasting, or dragging data within a data entry table, when formulae are looking along the row. For example, if a user drags the data in row 3 down to row 4 (maybe to make room to enter some new data in row 3), then all of the formulae referring to row 3 will now refer to row 4 (as will the formulae on row 4 that already refer to that row), however none of the formulae will refer to row 3.
Unfortunately, Excel does not provide an option to disable cut and paste, so users must be encouraged to always copy and paste instead (the contents of the original cells can then be deleted).
Be careful out there!
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report “The 5 Excel features that you NEED to know”.