CEO needaspreadsheet.com
Columnist
Share this content
Tags:

Excel Tip: Beware - Cut and Paste IS NOT the same as Copy and Paste

2nd Apr 2013
CEO needaspreadsheet.com
Columnist
Share this content

Cut and PasteFrom 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 toother 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".

Tags:

You might also be interested in

Replies (5)

Please login or register to join the discussion.

By A mum and an accountant
02nd Apr 2013 14:57

Done it many times
Thank god for the undo button if I realise it early enough or if I'm just testing it out!

Thanks (0)
avatar
By jonbryce
19th Apr 2013 15:25

Why would you want to disable cut and paste?

If you want to move something to a different location on the worksheet, but still have it displaying the same result as before, you use cut and paste.  An example would be where you have a formula result at the side of the page, but there isn't enough room there to print it, so you move it to the bottom where there is space.

Thanks (0)
avatar
By brian.barrett
20th Apr 2013 14:07

Point Well Made

It should also be made that this difference in Move and Copy also works with Named Ranges - Mostly!

If you move an individual cell that has been given a name, then the name will move with the individual cell.

If you move an individual cell that is part of a range of cells that has been given a name, then the named range will remain in its original location (i.e. move B5 when range B5:D5 has been given a name)

If you move the whole of a named ranged in 1 go, then the named range will move with the cells that you are moving - this will happen even if you more cells than are in the named range provided ALL the named range is contained within the cells you are moving.

Thanks (0)
avatar
By N.Krishnaswamy
20th Apr 2013 16:45

CUT and Paste

The more easier way is to select the formula from the Formula Bar and cut and paste it any where. The formula remains the same, even without dollar sign.

Thanks (0)
avatar
By brian.barrett
20th Apr 2013 17:21

Understanding

Cutting and pasting from the formula bar will certainly do that.

I feel the main point about this thread is a) to be aware of that there is a difference between cut and copy and, b) to understand what is going on.

Cut will effectively move the cell to another location - so as much as possible will stay the same.

Copy will copy the attributes of the cell - ie formula (remember most addresses are relative so a copy will keep the same relative address stucture, but when displayed in A1 format the formula will stay the same unless you are using absolute references), format etc.  Clearly you may decide using the rightclick option, just to copy formula/value/format etc.

Copy using copy from formula bar is effectively simply doing the typing for you into the new cell - typing, say, B6 in any cell will still be B6 regardless of whether you intend it to be a relative or absolute address.

Thanks (0)