Loading
ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

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:

  1. 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.)
  2. At the left side of the dialog box click Customize (Excel 2007) or Quick Access Toolbar (Excel 2010). (See Figure 1.)
  3. Figure 1. The Customize area of the Excel Options dialog box.

  4. Use the Choose Commands From drop-down list to choose All Commands.
  5. In the list of commands, choose Paste Values.
  6. Click the Add button. The command is copied to the right side of the screen.
  7. 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:

Sub PasteVal()
    Selection.PasteSpecial Paste:=xlValues
End Sub

Now, follow these steps:

  1. Press Alt+F8 to display the Macro dialog box.
  2. From the list of available macros, select the PasteVal macro you just created.
  3. Click on Options. Excel displays the Macro Options dialog box. (See Figure 2.)
  4. Figure 2. The Macro Options dialog box.

  5. 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.
  6. Click on OK to close the Macro Options dialog box.
  7. 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.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Comments for this tip:

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:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.