Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. 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.
Written by Allen Wyatt (last updated November 20, 2020)
This tip applies to Excel 2007, 2010, 2013, and 2016
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 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:
Figure 1. The Quick Access Toolbar area of the Excel Options dialog box.
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. (Or you can use Alt, H, V, V, in newer versions of Excel for the same results.) 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:
Figure 2. The Macro Options 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. Of course, the one drawback to this method is that using the macro removes the use of the undo function; so if you make a mistake you'll either have to change it manually or return to your last saved version.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6283) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Creating a Shortcut for Pasting Values.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When working with large numbers, you may need a way to quickly divide a range of those numbers by a specific value. ...
Discover MoreIf you need to update a bunch of values in a worksheet by a standard amount, you can use the Paste Special feature of ...
Discover MoreIf you want to turn a range of cells by 90 degrees within a worksheet, you need to understand how Excel can handle the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-07-06 05:05:13
SteveJez
@ Larry, have you checked - ALL COMMANDS - VALUES (Paste Values) ?
2024-07-05 14:49:55
Larry Schwartz
I'm using Win 11 on a PC with Office Home and Student 2021. The Paste Values option for the QAT is not there under ALL COMMANDS or COMMANDS NOT IN THE RIBBON.
2024-07-05 07:06:45
Saskia Jacobsen
There is a standaard shortcuts for paste valies.
Ctrl+shift+v
2024-07-04 17:50:13
Ben Altman
I forgot to add that for Excel you can similarly add to the Quick Access Tool bar, and if you select "Show Quick Access Toolbar" checkbox, then you can also use Alt-1, Alt-2, etc. to access them.
2024-07-04 17:42:22
Ben Altman
I add to the quick access toolbar in MS Word 2021 "Merge Formatting [Paste and Merge Formatting] (PasteMergeFormatting)" and ""Keep Text Only [Paste and Keep Text Only] (PasteTextOnly)" and I move them to the 1st and second positions. I can then use Alt-1 and Alt-2 to access them automatically. I am not sure which version of MS Word this was introduced.
2022-03-02 10:24:01
J. Woolley
@rjp
Also, look for "Formatting (Paste Formatting)".
2022-03-02 10:21:36
J. Woolley
@rjp
Look for "Values (Paste Values)".
2022-03-01 21:37:11
rjp
I had to reinstall Office 2019, and I no longer see "Paste Value" and "Paste Format" in the All Commands list for the Quick Access Toolbar. Did they disappear?
2021-10-06 19:26:11
Gregg
Using Excel v.2111, most of the Paste options, such as Paste Formulas, Paste Values, Paste Formats, are apparently no longer included on the All Commands list for Quick Access Toolbar customization.
2020-11-20 05:45:15
Si Moss
There are 3 'paste values' options to add to your quick access toolbar in v2019 & 2016...
2020-11-20 05:42:21
Russell Stainer
Just confirming that the legacy "Alt+E, S, V, Enter" keystroke combination, still works in Office 365, as of this date.
2020-04-22 11:39:35
Daniel Gonzalez
Thanks a lot for showing all the available options in a step by step way.
2018-05-06 04:38:51
Danish
Thanks
2018-02-15 11:40:27
David Gray
Scrolling down to the bottom, I just now noticed the comment by Steve Jez about accessing the buttons on the QAT by number. This tip might even merit a nugget of its own.
2018-02-14 21:09:47
Tom
Correction. To be uber efficient. Add a Copy command and Paste command to the Quick Access bar, then just use Alt+5,6.... Boom done!!! Of course, you would change the numbers to the number of the command on your quick access. Your welcome. :)
2018-02-14 20:57:38
Tom
If you add the Paste Values to the quick access at the top then pressing the Alt shortcut gives you direct access to the Paste values, for example in my case: Prec Ctrl+C to copy and Alt+5 to apply the paste value. This is the shortest combo.
2017-08-13 20:14:25
onewhojesussaved
Allen, you rock! Macro to pasteval helps me alot.
2017-07-10 17:51:40
mngreen
Might there be a way of protecting formatting, such that the data/formulas paste, but the "formatting" end of the pasting is effectively disabled because the formatting is "locked"?
thanks again ...
2017-07-10 17:48:25
mngreen
Unfortunately, not having a Ctrl+Z (or alternate) "undo" isn't viable. Restoring manually would be for too time consuming and, in some cases, "impossible" (for all intents and purposes).
I've seen a variety of alternate "paste" methodologies, but the issue I'm facing is multiple users who, almost invariably resort to their own habitual default. There is no way I can reset ***their*** defaults - particularly since they are not the ones that have to deal with the tedious and time consuming restoration of the formats they have blown-up.
Is there any way, through additional code in the otherwise-perfect macro, through an additional macro, or some other arcane and mystical fix, to supplement the provided solution in a way that Ctrl+Z (and/or other "undo" options) can be made to work?
Thanks.
mng
2016-12-21 16:59:49
Sub PasteVal()
Selection.PasteSpecial Paste:=xlValues
End Sub
...does not work in Office 365 Pro Excel 2016.
As well, my macros (Record Macro) that worked in 2010 do not work in 2016. Always result in "Runtime error "1004" Pastespecial method of Range class failed."
2016-12-20 01:00:30
David Gray
Terrance's and Stephen's suggestions work well for pasting data between cells, but the topic at hand was general purpose pasting, e. g., from a Web page or the File Explorer, which requires my slightly longer shortcut into the Paste Special dialog box.
2016-12-19 04:10:02
Terence
I like Alt , E, S, V for paste value and
Alt, E, S, T for formats.
You can also do similar to access the quick access toolbar if you added it there Alt and a number key (can assign macros there too)
2016-12-18 14:20:48
David Gray
There is a much shorter path to Paste Special that leverages the Excel 97/2000/2002/2003 keyboard accelerators, many of which still work through at least Excel 2013, and probably 2016 as well.
Unicode Text: ALT E, S, U
Text: ALT E, S, T
2016-12-17 19:11:39
MW
Combine Lanier & Paul's tip:
Right click the cell destination
Mouse over Paste Special
Press V
2016-12-17 17:40:07
Lanier Dodson
In Excel 2013 I just right click the cell where I want to paste and I get a pop-up menu where there are a choice of paste options, including Values. About as simple as it gets.
2016-12-17 13:59:37
Paul Seaman
I use
CTRL + V
CTRL (on its own, this activates the Paste Options menu)
V for Values
So CTRL+V, CTRL, V
2016-12-17 08:14:20
Willy Vanhaelen
Alt,h,v,v
is as far as I know the shortest.
2016-12-17 07:19:33
Steve Jez
If you add the shortcut to the Quick Access Toolbar & move it to the left hand side of the toolbar all you have to do is hit Alt & the number of the button. ie. if you move it to the first button then you just need to hit Alt then 1.
2016-12-17 05:32:52
Stephen Bungard
Alt-E, S, V always works for me.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments