Every day of my life I copy something in Excel, either from a cell or from the formula bar. Half the time when I go to paste it, the Clipboard is empty and nothing happens.
I’m aware that Excel treats the cell contents differently from what’s in the formula bar, and you can’t necessarily go copying and pasting between them. But it seems like Excel is being overprotective by deleting the Clipboard contents every chance it gets. Just now I copied the contents of a cell, keeping in mind very carefully what I’d copied, then did some filtering/unfiltering operations, went to paste in another cell, and it’s gone. You twitch your eyelid, and the Clipboard is cleared.
This happens multiple times per day and costs some time. I’m sure I’m doing it wrong, and it’s very likely the problem is unfixable, but if I understood the developers’ reasoning a bit, perhaps I’d be less irritated and maybe even learn something to improve my workflow.
The difference is what you are copying. If you copy only text from a cell or text in the formula bar, it is creating a snapshot of the text in the clipboard, which persists even if you hit Esc or type in another cell. But if you copy the entire cell or a range, it’s essentially a dynamic reference to the range and will be cleared as soon as you type in another cell.
Copying a cell or range not only copies the text of the cell, but also its formatting, formula, data validation rules, etc. It’s essentially copying the live state of the cell. Edits to the worksheet could theoretically change that state, so Excel “helpfully” clears the copy mode to prevent pasting stale data.
You can usually still paste it by opening the Windows clipboard (Win-W), or the Office clipboard (click the little diagonal arrow at the bottom right of the Clipboard section of the Home tab). But at that point, it’s usually quicker to just re-copy it.
Troutman is correct. When you copy a cell in Excel, it copies the entire state of that cell, not just the text content. You lost the clipboard contents when you performed operations between copying and pasting. I’d generally advise against that practice in general. If you just want the text content, copy and then paste it into Notepad before doing any operations in Excel. Then, when you are ready, copy the content from Notepad and paste it into the new desired location in Excel.
I haven’t used Excel for years (thanks to retiring), but aren’t there, when right-clicking a cell, options for “copy as text” or “copy with formatting” or some such? I really can’t remember, but there should be. Doesn’t help you with CRTL-C, but it would be an alternative.
ETA: @EinsteinsHund & mostly ninja-ed by @TroutMan while I was typing.
Sort of. What the OP seems to want is a copy that stays on the clipboard as-was despite any dynamic changes. So they can later paste the as-was version.
The various forms of Excel’s “paste special” feature paste aspects of a cell, such as formatting, or formula value vs formula itself, etc. but of course that will only succeed if the info is still on the clipboard to be pasted.
Preventing inadvertent pasting of stale info is a greater goal that requires clearing the clipboard of stale data.
For the OP @Briny_Deep: When you do a copy operation, you’ll notice the cell or cells you’ve copied now have a swirling dashed outline. That means those cells are being actively monitored. If you try to paste while they’re swirling, you’ll get what you expect. If you do some other other operation that invalidates the copy, that swirling outline goes away. Which means your copy is not in mid-flight anymore and the clipboard is empty.
Bottom line: For Excel, copy is more of a mode, than the simple action of sticking data on the clipboard. While you’re in “copy mode” with live data in hand you can paste it multiple times in multiple places and do so using different versions of the paste special feature. But once you “exit copy mode” by doing something other than navigating and/or pasting, then you have nothing to paste.
It’s really a very different action than copy in any other app. It’s best not to even think that the clipboard is involved. Just think “Copying enters copy mode, editing ends copy mode, and as long as I’m in copy mode I can paste. But no other time.” and your mental model will align more closely with what Excel is “thinking”.
I hesitate to mention this because I think it risks causing problems with unexpected behavior. But depending on what you’re trying to do, maybe it’s useful.
If you open the Office Clipboard as described above, it opens in its own pane that persists until you close it. From it, you can paste previously copied cells or ranges even if the “marching ants” outline has been cleared. So if you are copying/pasting frequently while also editing cells, maybe this is a workaround.
BUT: the reason I don’t like this is because the paste behavior is different than anywhere else. If the cell contained a formula, it pastes the static value, not the formula. But it still pastes the cell’s formatting. So it’s not quite “paste all” and it’s not quite “paste values”. For me, the risk of forgetting what I’m pasting isn’t worth it, but if your sheets don’t contain formulas, maybe it will work for you.
It is, thanks. My sheets contain plenty of formulas, but I know what I’m doing when c/p-ing them. But I do a lot of data massage with occasional touch-ups by hand; say I’m standardizing a column filled with slightly different comments. It’s that sort of action where I’ve been getting tripped up with “my Clipboard” (cold dead hands, etc., and thanks again to @LSLGuy) not behaving like I expect. I love the Clipboard pane option.
On a related note, why does excel treat all of your spreadsheets under one umbrella when undoing something when the rest of the office suite treats each document separately? Meaning if I have two open word docs & make some changes in Doc 1 & then some changes in Doc 2, if I go back to Doc 1 & press {Ctrl+Z} it will only undo those changes made in Doc 1; however, in excel, if I make entries in Spreadsheet 1 & then entries in Spreadsheet 2 if I go back into Spreadsheet 1 & press the same {Ctrl+Z} it’ll undo the most recent change in excel, even if that’s not in the current spreadsheet in front of me.
That’s because Doc1 and Doc 2 are different files, and Sheet 1 and Sheet 2 are within the same file. if you were to truly duplicate the same behavior as a word doc, you’d make a change in Sheet 1 of Workbook 1 in excel, A change in Sheet 1 of Workbook 2 in Excel (a different file), and go back to Workbook 1 and hit Ctrl-Z, it will undo your last change in Workbook 1 (Workbook 2 will be untouched).
No, that’s not right. Like Spiderman says, the Excel “undo” action is across all open workbooks. It doesn’t matter which is active at the time, it will undo the last action in any workbook.
As for why it does this, my charitable guess is that they figure workbooks more often have references to other workbooks compared to Word docs, so they wanted to treat them all as a group instead of separate files.
My less charitable guess is the two teams didn’t talk to each other.