Forum Discussion
difference between copy and cut in excel
Suppose like I have a function in a cell C1: A1-B1 ; If I cut a value (not a function, a numerical value) from another cell to A1, then function in C1 is not working, showing #REF!, function showing =#REF!-B1 while A1 is a numerical value.
But if I do copy instead of cut, it works. Why?
6 Replies
If you know how to use defined Names to reference cells and their contents, the behaviour is more intuitive. If you have two cells named 'newCell' and 'oldCell', then copying the value from 'newCell' to 'oldCell' changes the value in 'oldCell' but a formula
= 100 * oldCell
will still evaluate, albeit to a different value.
If, on the other hand, you cut and paste 'newCell' to 'oldCell', the range name 'newCell' is moved so that it overwrites the 'oldCell' which is destroyed in the process. Any formula involving 'oldCell' will error, but a formula
= 200 * newCell
will remain unchanged; it just happens to be picking up its value from a different location on the worksheet.
That's a good question Why and the answer depends on how deep we would like to go in consideration of Excel engine and history of its creation.
If on top. Excel returns #REF! if the cell was referenced is deleted or pasted over How to correct a #REF! error .
Excel treats differently copy/paste and cut/paste. With copy/paste Excel copies the cell value and cell properties, we may paste all of them or only some using Paste Special.
Cut takes ALL attributes of the cell and paste all of them into new place, Paste Special doesn't exists for the pasting after the cut. Actually with cut/paste you delete "physical" cell and put another in this place. With copy/paste "physical" cell is kept, we only replace it's value an related properties.
By the way, drag & drop (take any cell and drag it by mouse on A1 place in our sample) is equivalent of cut/paste.
In other applications, such as Word, there is not need to support referencing mechanism as for the sheets, thus there is no difference between pasting after the copy and after the cut, they are working exactly the same way.
Thanks much, I got the answer. I was wondered because when I cut a float number (not a function) from a cell to a new cell, the pasted number should be still a float number, and a function should have a result. As an example: if I cut a float number from C1 and paste that on A1 cell, a function of A1+B1 should give a number. If I understand correctly, A1 cell is still A1 cell, just value changed to C1 value with other attributes. So, If C1 is a float number, now A1 should be a float number too. as a result, A1+B1 function suppose to give a number.
jabed I guess you didn't understand SergeiBaklan 's very clear explanation of the issue. Imagine that "cut and paste" tells Excel to first "delete" the target cell and immediately "insert" a new one in its place with the new value. The initial deletion of the old A1, breaks all formulae that refer to it and will produce the #REF! error. Their relationship with the old A1 is lost forever. After pasting there will be a new A1.
"Old A1" is not the same as "New A1". Hence, all formulae that pointed to old A1 remain broken.
"Copy and paste", on the other hand, just "opens the door" to A1, kicks-out the old value and puts in the new one. A1 is still the same cell as before, but just with another content. All formulae that refer to A1 remain intact.
Why? It's a good question, and my answer would be this: It prevents you from making unintentional errors. If you play around a bit more with that scenario, I think you'll see that it will give you that same result if you cut and paste a formula (not just a value)
Play further, with copy and paste, when it's a formula you're copying and pasting, and you might see this error message, which is letting you do the copy/paste but alerting you to potential problems:
Bottom line: you should be grateful for that #REF! error message. If in fact your goal was simply to move a value from one cell to another, as part of a desire to redesign the layout, use copy and paste, and then go back and delete the original cell.
Now, I'm saying that simply as a long time user of Excel, not as an official representative of Microsoft or as somebody with inside knowledge of the rules behind the scene....so there may well be a deeper reason. My main point would be not to chafe under what might appear to be an unreasonable barrier.
Or, as Obi-Wan-Kenobi so memorably said, "Trust the Force, Luke."
,
mathetes I understand that and I know the difficulties with the function. But I don't understand why cut doesn't work with float numbers. If I cut a cell and paste it, it removes the number from the old cell and pastes that on the new cell, but any function containing that new cell doesn't work. though I can read the number on the new cell. (Again I am not talking about cut/copying a function.)
