We all know that keyboard shortcuts are the fastest way to accomplish most tasks in Excel. I already showed you my top 10 keyboard shortcuts, and you can get a free quick reference for them when you join the newsletter. Unfortunately, not every common action in Excel has a keyboard hot-key assigned to it by default. Paste Values, for example, is one of the most common actions when formatting spreadsheets, but the only keyboard access for it is the cumbersome ALT+H+V+V through Excel Key Tips. Here, we’ll show you how to make your own custom keyboard shortcuts with macros by giving Paste Values the convenient CTRL+SHIFT+V it should have had all along…

Enabling the Macros Menu and Developer Tab

To make custom shortcuts, we need to write a bit of VBA code, but fortunately, we don’t need to learn VBA just to duplicate an existing Excel function. Macros are recordings of your actions in Excel that can be duplicated later. The macros menu isn’t turned on by default, so before we begin, we need to enable the Developer tab in Excel settings.

Click on File and choose Excel Options at the bottom of the menu.

In the Excel Options dialog box, choose the Customize Ribbon option on the  left menu, and under the right column labeled Customize Ribbon, check the Developer option. Click OK.

You should see the Developer menu tab appear along the top of the Excel window.

Preparing to Record the Macro

We are going to replicate a basic Paste Values action, but that means we need to get ready for it. Once we press Record, Excel’s Macro Recorder will track every click and every keystroke we make, and we only want it to remember the Paste Values command.

I built a couple cells to demonstrate the effect of Paste Values. This isn’t strictly necessary, but it’s useful to make sure we captured the correct behavior. Cell A1 has been formatted with Bold and colored with a blue background. Cell B1 holds a different value and has no other formatting.

Remember, we don’t want to capture any cell selections or Copy actions. We just want the Paste Values action. This means we need to pre-select a cell. We also need to pre-copy content. Select cell A1 and press CTRL+C. You should see a blinking dotted line around cell A1 to show that it has been copied.

Now pre-select cell B1 that will receive the Paste Values command. Your screen should look similar to this:

Recording the Macro

Click the Developer menu tab and choose Record Macro under the Code menu.

A Record Macro dialog box should appear.

Name your new macro PasteValues.

In the Shortcut Key field press SHIFT+V.

In the Store Macro in: field, choose Personal Macro Workbook.

Your Personal Macro Workbook is a file called PERSONAL.XLSB that is always open when Excel is open. Macros you save here will always be available to you, but they won’t be stored in other files you work on. This is great for keyboard shortcuts and other personalizations you want to make to your copy of Excel.

If you want, you can provide a brief description of what this macro will do. Then, click OK.

Important! Everything you do after clicking OK will be recorded, so don’t select or change cells, formatting, or any other settings!

Click on the Home menu tab, choose the Paste drop down menu, and select Paste Values from the middle section.

Without doing anything else, click on the Developer menu tab, and choose Stop Recording from the Code menu.

That’s it! You just recorded a macro!

Checking the Macro Code

It’s a good idea to make sure that the macro captured what you intended, so you don’t accidentally change other things when you use your new keyboard shortcut. To do that, we need to look into the Personal Macro Workbook. The file is open, but it is hidden by default, so we need to unhide it.

Click the View menu tab and choose Unhide from the Window menu.

In the Unhide dialog box that appears, select PERSONAL.XLSB and click OK.

Now your Personal Macro Workbook should be active in Excel. Click on the Developer menu tab and choose Macros from the Code menu.

In the Macro dialog box that appears, choose PasteValues and click Edit.

This will open the Microsoft Visual Basic for Applications (VBA) window and display the VBA code for your PasteValues keyboard shortcut macro. The code should look like this:

Sub PasteValues()
'
' PasteValues Macro
' Paste only Values from copied cell data.
'
' Keyboard Shortcut: Ctrl+Shift+V
'
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
End Sub

If it has any other lines or actions in it, you may need to delete them to make the function work properly. If you are uncertain whether your code is correct, you can copy the Sub above to your own file and save it.

If you are happy with the result of the macro, it’s time to close out of your Personal Macro Workbook and test your new keyboard shortcut!

Press CTRL+S or File and then Save on the menu to save PERSONAL.XLSB. Close the VBA window and make sure that PERSONAL.XLSB is the active workbook in Excel. Click on the View menu tab and choose Hide from the Window menu.

Test your Keyboard Shortcut

Now that you have finished building your macro, you should have a new keyboard shortcut for Paste Values! Copy some cells with formatting and press CTRL+SHIFT+V to see if the Paste Values command works!

This works great for pasting content from one cell to another, but what if you have copied something from outside Excel that you want to paste without formatting? Try it now.

Uh oh. If you just a Visual Basic Runtime Error, that’s okay. It turns out that Excel treats external paste operations differently than it’s internal cell paste operations. Also, error messages are ugly and we want to make them go away… Time to add features to our new macro.

Catching Macro Errors

First, let’s get rid of the error message that we get when we are pasting content from outside Excel.

Follow the directions in the Checking the Macro Code section above to open PERSONAL.XLSB in the VBA Editor.

Add the following line after the green comments:

On Error Resume Next

This tells Excel to ignore errors and keep on going. Your code should now look like this (with the new line in bold):

Sub PasteValues()
'
' PasteValues Macro
' Paste only Values from copied cell data.
'
' Keyboard Shortcut: Ctrl+Shift+V
'
 On Error Resume Next
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
End Sub

Press CTRL+S or File and then Save on the menu to save PERSONAL.XLSB.

Close the VBA window.

Make sure that PERSONAL.XLSB is the active workbook in Excel. Click on the View menu tab and choose Hide from the Window menu.

Go back to your normal workbook and test the paste outside content feature. No more errors!

Making CTRL+SHIFT+V Work For External Paste Operations

We’re not getting errors anymore, but it would still be nice to have CTRL+SHIFT+V work for external and internal paste operations in Excel. Let’s capture the action for an external Paste Special command and add it to our macro.

We need to set the stage again, so select something in another program You can select this sentence right here if you want. Press CTRL+C to copy the content.

Click the Developer menu tab and choose Record Macro under the Code menu.

A Record Macro dialog box should appear.

Name your new macro PasteUnformatted.

In the Shortcut Key field press SHIFT+U.

In the Store Macro in: field, choose Personal Macro Workbook.

If you want, you can provide a brief description of what this macro will do. Then, click OK.

Important! Everything you do after clicking OK will be recorded, so don’t select or change cells, formatting, or any other settings!

Click on the Home menu tab, choose the Paste drop down menu, and select Paste Special at the bottom.

In the dialog box that appears, under Paste As: choose Text and click OK.

Without doing anything else, click on the Developer menu tab, and choose Stop Recording from the Code menu.

Now we’ve captured the external paste function, but we still need to put them together.

Combining the Macro Code

Click the View menu tab and choose Unhide from the Window menu.

In the Unhide dialog box that appears, select PERSONAL.XLSB and click OK.

Now your Personal Macro Workbook should be active in Excel. Click on the Developer menu tab and choose Macros from the Code menu.

In the Macro dialog box that appears, choose PasteValues and click Edit.

This will open the Microsoft Visual Basic for Applications (VBA) window and display the VBA code for your PasteValues keyboard shortcut macro. PasteUnformatted will also be there.

Look for this line in the PasteUnformatted section:

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
 False

Select it and copy it just above the Sub End line in PasteValues. Your PasteValues code should now look like this:

Sub PasteValues()
'
' PasteValues Macro
' Paste only Values from copied cell data.
'
' Keyboard Shortcut: Ctrl+Shift+V
'
 On Error Resume Next
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
 False
End Sub

Press CTRL+S or File and then Save on the menu to save PERSONAL.XLSB.

Close the VBA window.

Now that we have added the PasteUnformatted behavior to PasteValues, we don’t need PasteUnformatted anymore.

Click on the Developer menu tab and choose Macros from the Code menu.

In the Macro dialog box that appears, choose PasteUnformatted and click Delete.

It will ask you if you are sure – click Yes.

Make sure that PERSONAL.XLSB is the active workbook in Excel. Click on the View menu tab and choose Hide from the Window menu.

Congratulations! Your custom keyboard shortcut now pastes from inside and outside Excel!

You can make similar keyboard shortcuts for other popular multi-click actions to save time when you are building future spreadsheets. the steps are the same. I recommend choosing CTRL+SHIFT shortcuts because otherwise you may overwrite existing Excel shortcuts that you might want to use later.