Bottom line: Learn 5 different ways to copy and paste values to remove all formulas and formatting.
Skill level: Beginner
Video Tutorial
What is Paste Values?
Copying and pasting values is probably one of the most common tasks we do in Excel.
Paste Values will paste the values ONLY of the copied range WITHOUT formulas and formatting. This allows us to extract the numbers or text from cells.
There are a TON of reasons to paste values.
One common use is for scenario analysis where we want to “freeze” numbers that are results of formulas and place them in some blank cells.
Another common use is when we want to paste numbers or text into a range that already contains formatting. Pasting values will not change any existing formatting that is applied to the cell/range.
In the image above, the Scenario 1 column already contained both cell formatting (colors) and number formatting. When we paste values, any existing formatting in the paste range will NOT change.
In the first example above, the blank cells have the default General format, and that is why there is no number formatting applied when we paste values.
The Paste Special Menu
Paste Values is one of the many pasting options on the Paste Special menu.
We can also access some of the Paste Special commands from the Home tab and right-click menu in Excel.
The Paste Special… button on those menus opens the full Paste Special Menu.
Keyboard Shortcuts for Paste Values
There are keyboard shortcuts for all of the Paste Special commands. As I mentioned before, the most common we use is Paste Values.
In the video above I share 5 keyboard shortcuts (plus a bonus) to paste values. Here is a list of the shortcuts.
- Alt, E, S, V, Enter (Mac: Ctrl+Cmd+V)
- Alt, H, V, V
- Menu Key + V
- Custom Quick Access Toolbar (QAT) Button: Alt+1
- Ctrl+V, Ctrl, V
- Custom shortcut with The Paste Buddy Add-in.
In the video I also mention my article on the best keyboards for Excel keyboard shortcuts.
If you're more of a mouse user then checkout my article on my favorite mouse for Excel.
How do you Paste Values?
I'd love to know which method you use for pasting values. Please leave a comment below with your favorite. There are other methods too, so please leave a comment below if you use a different shortcut.
Thank you! 🙂
Menu V
I love all of these so much I almost wish I were not retired! It is hard to add new “tricks” to my toolbox when I do not use excel intensively or extensively.
Ctrl+V, Ctrl, V
Thanks for the tips. I think I will use the CTRL+V, CTRL, V and the QAT Button already created) the most.
Thanks Tim! 🙂
Thanks for another great tip!!! I started using computers way before the mouse was around so I prefer using keyboard shortcuts over mouse actions. And I do have a Menu key on my keyboard so I’ll be using the Menu + V. Thanks again!
Awesome! Thanks Kathie! 🙂
Hi
I haven’t gone into the videos yet but I would have loved to know how to paste only the visible cells on my sheet without it pasting into the hidden cell/columns as well. I know how to copy visible cells with the (alt+; & ctrl+c), however if I want to paste onto just the visible cells i find data is pasted into hidden cells as well.
Is there a tutorial you can do or a solution you can give in writing?
Hey Tanya,
Great question! My Paste Buddy Add-in has a feature called Paste Visible that does this. There is a video about half way down this article that explains more about it.
https://www.excelcampus.com/keyboard-shortcuts/copy-paste-select-visible-cells/
It’s not directly possible in Excel, so the add-in is a workaround solution that basically uses a macro to paste into each visible cell.
I hope that helps. Thanks! 🙂
I use the shortcut you didn’t mention. After selecting my range, I click on the right border using the right mouse button and drag it to my new location (I get the icon that shows arrows pointing up, down and to both sides when selecting the range with my right mouse button). Once I drop it, I get options. I select “copy here as values only”.
Thanks for sharing Ron! This is a great mouse shortcut. I think I’ll do a separate post and video on mouse shortcuts for paste values.
This is the method I use even when I want to overwrite the formulas with their displayed values. Right click-Drag the selection with your mouse to the right, don’t let go, then drag it back to it’s original location. The context menu pops up and select “values only.” One step, one handed overwrite of formulas with values.
LOVE YOUR VIDEOS. UNFORTUNATELY I USE A MAC AND MOST OF YOUR TIPS WON’T HELP ME
Hi B! Unfortunately there aren’t as many ways to perform paste values with a keyboard shortcut on the Mac. The only way I know of is through the Paste Special menu using either Ctrl+Cmd+V or Ctrl+Option+V to open the menu.
Once it is open you press V, Enter.
Please let us know if you find any other ways. Thanks!
Thank you so much Jon!
My preferred shortcuts for this purpose are QAT (#4) and ctrl+v ctrl+v (#5).
Jeff, I like the QAT method the best. Never realized that Alt plus the # of the QAT function activates the function. The other way is to right click and move the cross hair and choose paste here as values. All ways you shows are great however. Thanks for the tips. Great stuff.
Thanks so much Rich! I’ll do a follow-up post on mouse shortcuts, including the right-click + drag method. Here is an article on the keyboard shortcuts for the QAT that I forgot to link in the article.
HI Jon,
Thanks for making this available for us Excel users. Very useful, I will be visiting your site often.
I think my favorite shortcut will be the Menu +V. Fewer buttons to push.
Thanks so much for your support, Lowell! 🙂
Good to learn some new tricks. For the Menu+V short cut, I think we can use right mouse+V which will not depend on different keyboards.
The “Ctrl+V, Ctrl+V” is kind of tricky. The second Ctrl and V should not be pressed together, but after the Ctrl key is pressed, I have to wait till the menu shows up, then press V to make it work.
Hi Jianping,
Great suggestion on the right-click+V. If your hand is already on the mouse to select the destination range, then this is probably faster than using the Menu Key.
I agree that Ctrl+V, Ctrl, V can be a bit tricky due to the timing and having to wait a split second…
Thanks again! 🙂
Thanks for sharing the MAC short-cut
Thanks Susan! Sorry there’s only one that I know of. 🙂
I updated the article to show that you can also use Ctrl+Option+V to open the Paste Special menu on Mac.
Ctrl V, Ctrl V. love them all. As always Jon, beutifully presented!
Thanks Simon! I appreciate your support. 🙂
Menu V
I never knew what that key was for!
Me too! I also think I’ll be using Alt+V, Alt, V, as that closely replicates how I currently paste values. Thanks, Jon, your tutorials are always good, very useful and understandable.
I like easy, so the Menu, V would be my preference
Menu + V for me from now on.
Thanks – this has always been one of my bugbears and a 2 key solution is great.
I’ve been pasting values forever (using Shift F10, S, V) and have definitely just learned several new, faster methods. I will probably try to make Ctrl V, Ctrl, V work as your fingers are already on the keys, just have to remember the hold Ctrl & V, and just Ctrl, and then V to make it work. Awesome video. Thanks!
I like Alt E S V personally.
Also transfers nicely into E S T for formats and E S F for formulas
Thanks Terence! I agree. I use E S T and E S F a lot as well.
Menu Key + T also works for Paste Formats.
With formulas you have to use Menu Key + F, Enter because there are multiple accelerator keys for F on the right-click menu.
3 or 5 will be my favorites!! I love your videos, thank you so much for sharing your knowledge
I use the simply two step version, but will look into the Paste Buddy option to program hot keys.
I like option 3 Menu+V option. Thanks for this as usual Jon
QAT option is great. Thanks!
I am gravitating towards the Ctrl+V, Ctrl, V shortcut, as it is the easiest to remember. With so many shortcuts out there for all the programs I use, simple is good. As always, thanks for sharing your expertise with us, Jon.
method 5
Thanks for sharing Jon.
I didn’t know about the Menu+V.
I think I’m going to use that one.
Thank you, Jon, for your great tips; each one keeps adding to the value of your training.
I like the Alt, E, S, V but I want to try a couple of the others, too.
Jon,
I loved it! I’d use Alt H,V,V. But have you tried the Edge and Right Mouse click method? Highlight/Select the cells to copy and place the mouse pointer on the edge of the highlighted area (the marching ants…). A cross with arrows appears. Press and hold down the right mouse button and drag the edge to the new location. When you release the right mouse button, a menu appears that allows you to paste values. No keyboard, no muss, no fuss…
I really enjoy your site!
Many thanks,
Jim T
Thanks Jim! Yes, I’ll do a follow-up video on the right-click+drag method. Seems to be a popular method as well. Thanks again for your support, and have a nice weekend! 🙂
I like 1 and 3. Your videos are great, thank you very much for sharing your knowledge.
Ctrl+V, Ctrl, V is easiest to remember and keeps your fingers on the same two keys. Great to see there are lots of options.
The QAT method also intrigues me.
Thanks for the keyboard reccos. I’m in the market for a new one, so the timing is excellent!
Thanks Caty! Yeah there are a ton of new keyboards on the market with fancy new features, but I keep going back to old faithful (the Logitech K270). I love it’s simplicity.
Have a nice weekend! 🙂
Ctrl+v v option new and very easy to remember. QAT is amazing. Thank you
Dear Jon, thank you very much for 5 Keyboard Shortcuts to Paste Values. I will try to remember all those useful keyboard shortcuts values.
Best Wishesh
Rana
menu button on keyboard is the fastest and i think more easiest for me
great thing.
thank you very much for useful information
Many, many thanks Jon!
Ctrl+V, Ctrl, V just because it’s so simple to remember … although I might give the Menu key + V a whirl since it involves less movement. 🙂
I love your videos Jon, thank you!
It is really interesting and helpful.
PERFECT TIMING JON! Started a new job last year and haven’t needed to paste values till this week and just could not remember my favourite quick drop… ALT+1. Thank you
Awesome! Thanks Jenni. And a belated congrats on the new job! 🙂
#5 is neat for multiple paste operations and refinements and has the bonus that you are exploiting the intelligently-designed pop-up.
Dear Jon, thank you very much for 5 Keyboard Shortcuts to Paste Values. I like to use method 3, menu key + V
Best regards
Hi Jon,
I like the Ctrl + V, Ctrl + V Now.
Excellent
Menu V, but now I think I will have a look at your paste buddy as I can see some viable use for setting up quick keyboard shortcuts.
Cheers Jon.
Haha my Paste Value method is simply clicking on the shortcut in the QAT bar. Considering I’m always Paste Valuing, I think methods 3 or 5 will work best for me… My keyboard isn’t very Alt button friendly… Thanks Jon! (By the way, are there any shortcuts for Format Painter?)
Thanks Carrie! I’ll do a follow-up on the format painter. I don’t believe there is a dedicated keyboard shortcut besides Alt,H,F,P. There are some tips for it though like double-clicking the button to keep it enabled after you paint something…
I use option 4….but don’t do the “Alt 1″…..I just click on the tool bar button that I created (I keep my quick access toolbar below the Main Menu ribbon (not on top as you have done) which makes it more convenient for me to access…..
but then, I guess, you really wouldn’t call my method a “Keyboard shortcut”….but I feel this is the fastest way to past “values”….thanks for all your great tips!!
Could use help with a quick access button to “Paste as Bitmap” option…………also would like a separate button for “Paste as unformatted text”
Hi Barb,
I don’t believe there is a dedicated QAT button for Paste as Bitmap. However, you could create a macro for this and store it in your personal macro workbook. Then create a custom button to run the macro and put it on the QAT. I have a video series on the Personal Macro Workbook that explains this setup in more detail.
And here is the VBA code to paste as bitmap.
Sub PasteBitmap() 'Paste the copied image/shape as a bitmap On Error Resume Next ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False On Error GoTo 0 End SubIt’s really just one line of code but I put some basic error handling there to bypass the error that will be raised if the object that is copied is NOT a shape or image.
I hope that helps. Thanks again and have a nice weekend! 🙂
I use the option 5. Great tip for all the other options.
Great tips!! Menu V is great or Alt 1 🙂
Since I started using Excel many years before the Ribbon came along I have always used Alt,E,S,V. Some of the other suggestions look pretty tempting. I have learned many keyboard shortcuts using the Ribbon so Alt,H,V,V makes sense for me.
So happy to find a use for the menu key!
Hi
My most frequently used paste is via the mouse. Select, copy/cut, go to destination and then RCM select paste. Sometimes I select specific pastes such as “Values”, “Formats”, “Formulas”, etc depending on what result I want. I also frequently use Keystroke techniques to reduce developing mouse laziness.
Your advice is good Jon. It’s certainly good to have an arsenal of tricks up your sleeve to keep interest going.
Jon, thank you for the great tips and training videos and instructions you offer.
I like the Menu Key + V – it’s the easiest to remember.