Random Excel Tip: Always Available Paste Special…Values

For a variety of reasons, I find myself using Excel (2003) through remote desktop fairly regularly these days, and I just haven’t gotten around to setting up some of the basics that I’ve got set up on my main system. The big one is my setup of <Ctrl>-<Shift>-<V> as Paste Special»Values.There are scads of “Excel Tips”-type sites and blogs, and I’m not putting myself out there as an expert. Really, just looking to share one of my handy favorites with my readers, who are mostly somewhere within a standard deviation or two of my Excel skill level and might find this useful.

Backing up just a little bit. Paste Special is really, really nice to have when you need it. Specifically, pasting values, formats, formulas…and occasionally Transpose. But…mostly (for me) pasting values. When I’ve got a well-formatted table of data and need to move some data around, it’s just annoying to need to then go and fix the formatting. So, pasting formulas only or values only avoids all that. The problem is that the fastest way to do this is:

1. Copy the cell(s) you want to relocate (<Ctrl>-<C>)
2. Right-click anon the cell in the new location
3. Select Paste Special
4. Select Values
5. Click OK

All in all, not too painful…unless you find yourself needing to do it two or three times in a row (between separate workbooks, for instance).

This got annoying enough to me a several years ago that I recorded a macro and dropped it in Personal.xls so that I’d have a faster way to do this. It’s now the first thing I set up on any new computer I get.

The Result: After copying cells (this doesn’t work with cutting data), simply click on the cell where you want the values pasted and press <Ctrl>-<Shift>-<V>. That’s it.

How to Set It Up

This may look like a real hassle. It really isn’t (those four years as a technical writer tend to make my procedure writing a bit…er…detailed). But, it’s a one-time setup, and it really isn’t that bad.

If you’ve read this far and aren’t thinking, “MAN! That would be HANDY!” then just bail now. Otherwise, read on:

1. Launch Excel 2003
2. Select Window»Unhide
3. Select Personal.xls
4. Select Tools»Macros»Visual Basic Editor. This should bring up the VBA editor
5. Select Insert»Module
6. Copy and paste the following into the window:Sub PasteSpecial_Values()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
7. Click on the X to close the Visual Basic Editor (you don’t need to save anything yet). You should be back on the Personal.xls workbook
8. Select Tools»Macro»Macros
9. Select PasteSpecial_Values
10. Click Options
11. Click in the Shortcut key box
12. Press <Shift>-<V>
13. Click OK
14. Click the X to close the Macros window
15. Press <Ctrl>-<S> to save Personal.xls
16. Select Window»Hide to hide Personal.xls
17. Close Excel. If you are prompted to save Personal.xls, do so.

You should be set. Let me know if you give it a shot and find it useful (and if you hit any bumps in implementing it).