Creating a Shortcut for Pasting Values
Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Creating a Shortcut for Pasting Values.
One of the most often-used commands in Excel is the Paste Special option from the Edit menu, where you can figure out exactly how you want information pasted into a worksheet. On the Paste Special dialog box, the Values selection is undoubtedly the one used the most. Since pasting only values in this manner is used so often, you might think that Microsoft would provide a shortcut key to, well, just paste values.
Unfortunately, they don't provide one. There are ways around this, however. One way is to just add a tool to the Quick Access Toolbar that pastes values for you. All you need to do is follow these steps:
- Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 display the File tab of the ribbon and then click Options.)
- At the left side of the dialog box click Customize (Excel 2007) or Quick Access Toolbar (Excel 2010). (See Figure 1.)
Figure 1. The Customize area of the Excel Options dialog box.
- Use the Choose Commands From drop-down list to choose All Commands.
- In the list of commands, choose Paste Values.
- Click the Add button. The command is copied to the right side of the screen.
- Click OK.
Now, whenever you want to paste just the values, you can click on the new tool.
If you don't want to use the mouse to paste values, then you can use the tried-and-true keyboard sequence to paste values: Alt, H, V, S, V, Enter. This sequence selects the menus and dialog box options necessary to paste values.
If you want a shorter keyboard shortcut, the best way to do it is to create a macro that does the pasting for you, and then make sure that you assign a keyboard shortcut to the macro. For instance, create the following simple macro:
Now, follow these steps:
- Press Alt+F8 to display the Macro dialog box.
- From the list of available macros, select the PasteVal macro you just created.
- Click on Options. Excel displays the Macro Options dialog box. (See Figure 2.)
Figure 2. The Macro Options dialog box.
- In the Shortcut Key area, indicate the key you want used with the Ctrl key as your shortcut. For instance, if you want Ctrl+G to execute the macro, then enter a G in the Shortcut Key area.
- Click on OK to close the Macro Options dialog box.
- Click on Cancel to close the Macro dialog box.
Now, whenever you want to paste values, all you need to do is press Ctrl+G, the macro is run, and the values in the Clipboard are pasted to the selected cell.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6283) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Creating a Shortcut for Pasting Values.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Comments for this tip:
Cad 12 Feb 2015, 07:03
Update on this. Depending on what you're trying to paste, you need to use one of TWO methods to paste it (!). So here is an updated Sub which will cope with pretty much anything:
On Error GoTo TryText
On Error GoTo 0
On Error GoTo 0
ActiveSheet.PasteSpecial Format:="Unicode Text"
Cad 10 Feb 2015, 08:36
Unfortunately, the code:
failed in my copy of Excel 2010.
I therefore did a quick Record Macro and as a result, I use this instead:
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
No doubt some of the parameters can be removed (as usual with recorded macros), but I'll leave that as an exercise for those who can be bothered to work it out!
Cheryll 09 Sep 2014, 13:42
Thanks! I use this so often on my own computer, but I needed to add it to someone else's that I also use from time to time.
Frances 17 Jul 2014, 16:26
@Willy - Good tip! Love the old keyboard shortcuts!
@Antonio: Didn't realize that MS had buried Paste Values in the "Commands not in the Ribbon". Have added it now to my custom group. Much thanks!
Willy Vanhaelen 05 Jul 2014, 06:55
There is a another tried-and-true keyboard sequence to paste values. Instead of
Alt, H, V, S, V, Enter
you can use
Alt, H, V, V
It's not only shorter but also easier.
Feenie Von Karma 04 Jul 2014, 06:36
SO good! I spent so much time pasting as values via the right click menu. Just having a single button to click makes it so much faster. Thank you!
@David - Thankfully I realised this early on - I can't understand why there isn't an ordinary, Excel-default paste as values shortcut - like Ctrl+Shift+V or something.
David 19 Mar 2014, 05:37
You should note that, if using a macro, you will NOT be able to UNDO if you make an error. You will need to go back to your last saved version.
Antonio 02 Apr 2013, 10:19
@Brandon: When customizing the ribbon choose from the drop-down list [Choose commands from] the item [Commands Not in the Ribbon] and scroll down to find Paste Values (and many other Paste Options)
Brandon 05 Dec 2012, 12:07
It's weird that I can add Paste Values (and choose from a plethora of other paste types too) for the Quick Access Toolbar, but not for the ribbon.
Leave your own comment: