Paste Values Shortcut For Excel

After I create formulas in Excel, sometimes I want to lock in the results of those formulas, so I use the Paste Values command. For example, I might use the RAND function to create a set of random numbers. After creating them, I don't want them to change every time the sheet calculates.

Here's how I'd use the Ribbon commands to replace formulas with values.

  1. Select the cells that contain the formulas
  2. On the Ribbon's Home tab, click the Copy command
  3. On the Ribbon's Home tab, click the lower half of the Paste command, to open the list of options
  4. Click Paste Values

PasteValuesRibbon

The steps are similar in earlier versions of Excel, but the commands are on the Standard toolbar, and the arrow is to the right of the Paste button.

PasteValues2003

Use a Shortcut Menu

An even quicker way to paste values is by using a mouse shortcut:

  1. Point to the border of the selected range, and the pointer should change to a four-headed arrow.
  2. Press the right mouse button, and drag the cells slightly to the right.
    PasteValuesMouse01
  3. Keep pressing the right mouse button, and drag the cells back to their original location.
  4. Release the right mouse button and a shortcut menu will appear.
  5. Click on Copy Here as Values Only.

PasteValuesMouse02

The formulas are replaced by the values of their results.

See the Steps In a Video

This technique is a bit tough to explain, so if the written explanation didn't make sense, here's a very short video.


____________________

25 comments to Paste Values Shortcut For Excel

  • J.E. McGimpsey

    Of course, if you're using MacXL, you can also assign a keyboard shortcut to the Paste Values command.

    One setup I use is CMD-v for Paste, CTRL-v for Paste Values, CMD-OPT-v for Paste Function, and CTRL-OPT-v for Paste Formatting, and CMD-Shift-v for Paste Special...

  • sam

    I have the the following attached to Ctrl+m (make values)

    Sub MakeValues()
    Dim cRng as Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    If Selection.Areas.Count > 1 Then
    For Each cRng In Selection
    cRng.Value = cRng.Value
    Next cRng
    Else
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub

  • I can't decide which is quicker:

    1) Pressing CTRL+C, then move the mouse up to the paste icon in the toolbar, click the down arrow next to it, then click Values, -OR-

    2) Pressing CTRL+C, then right-click the selected area, click Paste Special, click the Values radio button, then click OK.

    Moving the mouse all the way to the top toolbar seems to take too much time compared to using the Paste Special window that's next to my selected data.

    You can tell I don't like moving the pointer all over the screen to do a paste values...

    Still, love the tips!

  • Wait! I figured out what you meant by right-clicking on the selected cells, then "wiggling" the four-headed arrow to the right and then back to the left (the original selection) and clicking Copy Here as Values Only.

    Oh, slick!

  • For efficiency, it's hard to beat Ctrl+C, followed by Alt+ESV and Enter. Those are the hotkeys for Edit – Paste Special – Values. I must have entered those keystrokes 100,000 times in my life.

    It still works in Excel 2007, but Alt+HVV (no Enter) is even more efficient. However, I will probably never break the habit.

  • J.E., Sam, and John, thanks for adding your shortcuts. I use Ctrl+C and Ctrl+V to copy and paste, but haven't tried keyboard shortcuts for paste values.

    And Bill, this tip is hard to explain, so I added a little video to show how it works. Your description of a "wiggle" is pretty accurate!

  • JP

    Nice tip, I learned this one from Bob Umlas at the Excel User Conference last year.

  • Primo

    If you use the mouse, then putting PasteValues on the right-click menu works well, (you can miss out the "wiggle"). You can run the VBA code once and it stays in your menu. (I don't know if this will work for Excel2007, it works in my Excel2000). I also have PasteSpecial and PasteFormats on that menu, but I can't remember the button IDs.

    Sub AddPasteValues()
    Application.CommandBars("Cell").Controls.Add Type:=msoControlButton, ID _
    :=370, Before:=5
    Application.CommandBars("Row").Controls.Add Type:=msoControlButton, ID _
    :=370, Before:=5
    Application.CommandBars("Column").Controls.Add Type:=msoControlButton, ID _
    :=370, Before:=5
    End Sub

    I found this tip in a comment from "James" on DDoE:
    http://www.dailydoseofexcel.com/archives/2005/10/15/mouse-shortcuts/

  • AlexJ

    As in the video, double click on the right bottom corner of the cell to auto fill the column is new to me – and brilliant. Debra, the added value from the videos is excellent. Keep them coming, please.

    I have a Paste Values key on my personalized menus. Like the Paste buton, it goes grey when cutcopymode is off, meaning nothing to paste.

  • Suleman Khalil

    2 WAYS in OFFICE 2007

    FIRST
    Alt + H + V + V

    SECOND

    1. Select the cells that contain the formulas
    2. On the Ribbon's Home tab, click the Copy command
    3. On the Ribbon's Home tab, click the lower half of the Paste command, to open the list of options
    4. Click Paste Values

  • shivraat yadav

    this is website is very best

  • CJL

    This works as hell. Have assigned it to Ctrl Shift V.

    Makes it all very easy
    ctrl x = cut
    ctrl c = copy
    ctrl v = paste
    ctrl-shift v = paste values

    A simple macro; just 'recorded' by preperforming the Alt ESV trick...

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub

  • Ted

    Using Office 2003. I have always used Ctrl C to copy and Ctrl V to paste....But within the last week, Ctrl V has defaulted to "Paste Value." This is a problem for me, because I cut & paste a LOT of formulas....And I'm so fast/habitual with Ctrl C / Ctrl V, that I often have pasted multiple times before I realzie I have pasted as Values, instead of formulas....Help.

  • conor

    How do I assign the paste values to a short cut?

  • Making a macro and assigning it a shortcut key is by far the easiest way to do this, but it still really, really sucks because there's no undo for a macro.

    With this, and the addition of about 10 more clicks just to add real error bars to a graph, I think Microsoft is really just trying to send Excel users a message that they're only for pretty pictures for bullshit meetings, and scientific and technical users need to just go learn R already.

  • Dustin

    Alt + HVV works great in 2007, I'll be using that from now on. thx for tip (yes I'm aware this post is over a year old :) )

  • Dustin, glad you finally found this Excel keyboard shortcut -- even if it took more than a year. ;-)

  • Lynda

    That is a sweet little trick!

  • Chris

    Is there a formula that I can use to copy the resulting value of another cell's formula?
    I don't want a macro, but I need to automate the process of capturing (and locking in) the value of the initial formula.

    Example: =if(a1="X",NOW(),"") is the formula that establishes a date upon the user entering X into cell a1. I need to capture and preserve that date in another cell so that it never changes whenever the spreadsheet is re-opened.

    Is there a way to do this with another formula? Like I said, VBS/macros are not an option, it has to be done with formulas.

    Thanks.

  • Harimau

    I prefer:
    Right-click Source Cell
    Press C
    Right-click Destination Cell
    Press V

  • if column a is 5 then column b show 1-5 how

  • Admin

    Dear Sirs

    I want to paste a formula.

    I copy the cell go to paste tab and in my drop down menu i do not have the choice of formula ???

    Does any one know why ?

    Thank you

  • George Brookes

    I have saved this code in my PERSONAL workbook and attached it to a custom button so that its always available - it works for me.

    Sub Copy_Paste()
    ' Copy_Paste Macro
    With Selection
    .Value = .Value
    End With

    End Sub

  • LoveExcelShortcuts

    Thanks for these tips. Had created a macro and attached it to a button (like George Brookes) that I placed on the Quick Access Toolbar - which worked just fine - but I prefer to keep my hands on the keyboard, and Alt+H,V,V does it for me.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>