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:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Comments for this tip:

Bong    19 Jun 2015, 02:04
CTRL + ALT + V, V, ENTER. Thats how I paste VALUES in excel 2013 :)

It's like CTRL V also but you just have to press alt. Then Voila! :D
C.R.RANGANATHA    09 Apr 2015, 21:43
The simplest and the easiest way to paste values is to first paste the data thro Ctrl + V key combination. Then select the pasted data. Holding down the right mouse button drag down the botton edge of the selected data and drag it up to its original position. Now release the right mouse button. A context menu pops from which you can select paste values only. There are other option in the context menu which you can use
Alex     09 Apr 2015, 01:55
I normally use Alt, E, S, V
I remember it as Edit-Special-Values from the old times when the Paste Special command was on the Edit menu.
frank mccraw    08 Apr 2015, 07:30
or Alt-E-S-V if you want to use only your left hand. While "v" pastes "V"alues, you can also paste formats, cell widths, and other qualities with a single third keystroke in this sequence.
Bill    08 Apr 2015, 07:29
Ctrl+Alt+V, V, Enter
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:

Sub ExecutePasteValues()

    On Error GoTo TryText
    Selection.PasteSpecial Paste:=xlPasteValues
    On Error GoTo 0
    Exit Sub

TryText:
    On Error GoTo 0
    ActiveSheet.PasteSpecial Format:="Unicode Text"

End Sub
Cad     10 Feb 2015, 08:36
Unfortunately, the code:

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

failed in my copy of Excel 2010.

I therefore did a quick Record Macro and as a result, I use this instead:

Sub ExecutePasteValues()

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

End Sub

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:

*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 4+5 (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 © 2015 Sharon Parq Associates, Inc.