provide an option to set Excel's default paste behavior
When you do an ordinary paste like Ctrl+V, Excel pastes the cell values, formatting, validation, everything. However, many users usually paste only values when working, and the extra handling to paste special is not productive.
Word and OneNote have a setting that allow the user to choose the default paste type, and that should also be available in Excel.
I know there are add-ins to workaround that and also the shortcut AppKey+V to paste values, but I think that should be a native Excel setting.
This is a great suggestion, and we want to keep the interest going. Thanks to everyone who has already voted and commented on the suggestion to have an option for the default paste behavior in Excel.
Sorry that we’ve been slow to comment and update the status on this one.
Steve K [Excel]
Tim Bell commented
Futski and Trisch: It seems to be relative to what's being pasted - if you're copying and pasting text from the web into a cell, you don't get "Paste Values" as a hotbar option (so Ctrl-V, Ctrl, V doesn't work but Ctrl-V, Ctrl, M does) regardless of what options you have set.
Tim, I also use O365.
You may want to check the setting that Roy found.
File|Options|Advanced|Cut, copy, and paste | Show Paste Options button when content is pasted should be checked.
Tim match destination formatting is different from paste as values. I use 365 at work and Ctrl V, Ctrl, V worked for me.
Lloyd Chircop commented
Excellent tip Tim, thank you.
Tim Bell commented
For Excel 365, the current workaround hotkey is no longer "Ctrl-V, Ctrl, V" as "Paste Values" isn't in the hotbar. You now have to use "Ctrl-V, Ctrl, M" for Match Destination Formatting.
Roy, Glad it worked for you. And good find on the Options / Advanced / Cut Copy Paste / Show Paste Options. Mine is indeed checked.
And you are absolutely correct when you you say some options should be carried thru at the spreadsheet level. Default PSV being one of them.
And like you, Alt-ESV just flows out my fingertips every time I copy/paste. But I / we shouldn't need to resort to that. MS really needs fix this and add a worksheet level default. That way we won't need to go back and fix everybody elses carelessness (I had another word there, but thought it'd get censored :-) .
Roy, Not a cup of water. :-D
It's not Ctrl-V, Ctrl-V. It's 3 distinct keystrokes
You'll see that after the initial Ctrl-V paste, pressing Ctrl all by itself pops up a paste dialog box. Then V for Values only.
You can also use other options instead of V, like F (Formulas) or B (All formatting except Borders) etc.
If you usually work on your own workbooks, add Paste Values and Paste Formulas to the Quick Access Toolbar. This way one can paste as whish with a single Click. (best option I found).
On the other hand... if you develop Workbooks for others to use, then the default in any protected sheet should be paste as Formulas. Much more usefull than adding a lock to the Tab Sheet Name, as MS did recently on latest updates (why?). There simply is no practical way to avoid your users to F-up the cell formats, and you cannot "educate" them all to not use CTRL-V.
This thread is running for almost 5 years and MS doesn't seem to listen. There are many others threads with different suggestions, but the underlying problem is the same.
Excel is a great tool. Please MS listen to your users and developpers.
We actually do need a reliable way to protect the formatting from wild pastes.
Ctrl-V Ctrl V is just a workaround, just good enought for advanced users, willing to preserve the original formatting.
Please note it's a 3 steps shortcut: 1) Ctrl-V, 2) Ctrl, 3) V
Terribly sorry but I should have mentioned that PureText also has a direct download in 32 and 64 bit versions and does not need an installer at all so it can be downloaded and just run from anywhere if the Windows Store option is not available for whatever reason.
Lloyd Chircop commented
Many corporations disable the Microsoft Store. So downloading an app like PureText is not an option.
CTRL+V, CTRL+V does not work for me either.
I deal with spreadsheets that contain a massive amount of raw data and some of that data is manipulated with Formula's to get the content I need, then I use that to create pivot tables for reporting. Having to deal with alternative methods to copy / paste this data is slowing me down. This issue not being addressed by Microsoft is causing loss of many hours of productivity.
I found an acceptable (to me) workaround for this using some software called PureText 6.2 by Steve Miller. It is available in the Windows app store. I can now paste without formatting by setting a hotkey that I want to use so I set it to ctrl-shift-v and now I can paste directly without having the formatting included. I would much rather see this as a an optional default behavior but I at least have a workable solution to this BS.
The alternative keyboard shortcuts to paste as values doesn't help when you have someone else working in the sheet that either isn't experienced enough to know to paste as values, or just simply doesn't give a ****. It's the most painful part of training is trying to ingrain paste as values into them. This is why a default would be so much better! Especially if you can force it to the be the default on the sheet itself.
Anonymous, That's very interesting. I learned something new today. It's interesting how the initial Ctrl+V pastes with all the formatting but then the subsequent Ctrl, V strips all the pasted formatting thus restoring the cells original formatting, including Conditional formatting.
I've always used Alt+E, S, V to get the same result. But Ctrl+V, Ctrl, V does the same in a little easier to remember method.
Thanks for the tip
The easiest way to paste by values is: Ctrl V, Ctrl, V
Please, I paste data from one sheet to another that has conditional formatting, I need to set the default paste behavior to values only so it won't overwrite. It adds so much time to not be able to quickly copy and paste without needing to select the value option.
Mike Pengelly commented
I find this extremely frustrating to deal with
Please add this ASAP
No point to paste formating - say you are making an adress list from data listed all over the internet. You copy and paste email adresses, phone numbers and websites. Your table looks like **** and you have to format it afterwards.
Needed a lot
Agreed fully on this suggestion. Please at least add an option to paste unformatted text by default. As an avid shortcut key user, this would help to speed up some of the work I do by half.