Excel: Make Paste Special Default
I was pasting data from a web page into an Excel spreadsheet, and I wanted to make everything have a consistent style. The function I used to do this is right-click -> Paste Special -> Text. This “inserts the contents of the Clipboard as text without any formatting” — exactly what I wanted. But it was a waste of time and effort to go through the Paste Special dialog box every time. There had to be a faster, better way… but it wasn’t obvious. After a quick Google search, I found this excellent way to do it:
First, prep for the paste. Go and copy some data, and select a cell in the spreadsheet. Then, go to:
Tools -> Macro -> Macros…
Give it the info it wants, and make the shortcut ctrl+v, if you want it to replace your usual paste shortcut. Hit record, and then use the menus to do a Paste Special as you normally would. Stop the recording.
Done! Now you can paste as usual, but without any of the extraneous ugly formatting.
Thanks for the tip. It works very well, even if I’d hoped for a setting in Excel to change this behaviour.
Beware when using this tip that there is no undo for macros, so if you paste over something, it’s gone unless you close the file without saving your changes. Paste->Special->Values should really be the default, but that’s what we get for using Excel to do numerical analysis.
I find using the hotkeys to be a really good alternative. Next to right control there is a “right click” key, I press that, then “c”, then “v”. It will do the trick of paste special values and still have the undo option, and once you get used to it its basically just as fast as hitting “ctrl+v”.
I can’t recreate this result. I just a “v” if i do this. Can you please explain in more detail?
spacebas,
I don’t know what Peter did to achieve his results, but when I tried it his way I came up with the same results. So I fiddled with the hotkeys a bit and here’s what I had to do to paste only values:
Select cell to copy
Hit the “Right Click” key– c — Enter
Select cell to paste to
Hit “Right Click” key– s — v — Enter
Not as fast as his post suggests, but still better than all the mouse clicking plus keys. I imagine you could get pretty fast at it if you used it all the time. Hope this helps.
I personally find PureText (Windows only) to be the best solution for this. It strips out all formatting and formulas, replicating the Paste Special–>Values feature. It can be accessed from any HotKey, which I have set as Windows-v.
It works via the clipboard, so it works in all apps – I find it very useful in Word.
http://www.stevemiller.net/puretext/