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