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”.
3 comments
Comments feed for this article
Trackback link: http://www.intelliot.com/blog/2006/03/excel-make-paste-special-default/trackback/