How to set up a Paste Values Shortcut in your Excel personal workbook

If you use Excel every day, you will usually end up becoming familiar with the shortcut keys such as CTRL-C (Copy) and CTRL-V (Paste). Another function very frequently used by Excel Ninjas is the Paste-Values function, which replaces the formula in a cell with the value resulting from the formula. For extra efficiency Excel professionals often have this important function set up as a shortcut key via macro. Here’s how:

Setup a macro subroutine

In Excel, Open the Excel Visual Basic window with ALT-F11

If the Project Explorer pane isn’t showing, display it with View, Project Explorer, or CTRL-R.

You should now see the personal Macro workbook (PERSONAL.XLSB) as the top item:

Select the PERSONAL.XLSB workbook and insert a new module with menu Insert, Module.

Then select the new Module in the project Explorer pane. The right-hand and pane will now show a blank code window.

Paste the following code into the window and click save.

Sub PasteSpecialValues()
' Paste Special, Values
   If Application.CutCopyMode = xlCopy Then
      Selection.PasteSpecial Paste:=xlPasteValues
   Else
      MsgBox "Nothing to Paste", vbExclamation, "PasteSpecialValues"
   End If
End Sub

This short code snippet will have the effect of pasting the current selection if any, as values.

You can now close the VBA Editor and return to excel

Setup a keyboard shortcut

To setup a keyboard shortcut you will first have to make sure the Excel Developer menu item is visible. In File, Options, Customize Ribbon, find the checkbox for “Developer” tab on the right hand list of Tabs. Activate the Developer tab if its not activated.

Return the to main worksheet and you will see the extra menu item “Developer” in the top menus.

Under the Developer menu, click Macros to reveal the macro management window. Select the PasteSpecialValues Macro, which should now be visible here.

Click Options to enter a Short-cut key (I like to use Ctrl+d, because its close to c and v)

All done!

Now you can return to the main worksheet and test your new shortcut by Copy-pasting a formula with CRTL-v ; CTRL-d (if d is the key you chose for the shortcut). The result should be that only the current value of the formula remains. Perfect if you want to take a snapshot of current formulas results or convert from formulas to values fast.

Close Menu