Random Excel Tip: Always Available Paste Special…Values

By on in with 6 Comments

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).

Similar Posts:

6 Comments


  1. That’s a great tip for people who like to use keyboard shortcuts. The mouse users could also add a Paste Values button to a toolbar:
    Choose Tools>Customize
    On the Commands tab, click the Edit Category
    In the list of Commands, scroll down a bit, and click on Paste Values
    Drag Paste Values to one of the Excel toolbars. I usually put it beside the Paste button
    Close the Customize dialog box.

  2. Great point, Debra! I am a bigger keyboard shortcut user than toolbar user, which I fully recognize is my personal preference. Thanks for pointing out the toolbar-based way to do the same thing!

  3. Pingback Gilligan on Data by Tim Wilson » Shortest Excel Tip Ever: <F4> and <Ctrl>-Y

  4. Random Excel Tip: Always Available Paste Special…Values

    As Mari said last year – Exactly what I was looking for.

    Thanks so much!

  5. 3 years after you wrote this and after scouring through a couple of variations of this code,this is still relevant! Yours was exactly what I wanted and your instructions on how to set it up were great, exactly the same process can be followed for even for Excel 2010.

    p.s. like the use of ‘V’ – don’t have to stretch the brain cells to remember what the shortcut is.

Leave your Comment


« »