Celebrate 'The VLOOKUP Book' birthday with us. Get 50% discount on the e-book today & tomorrow (30 & 31 October only).

Click here for details

Replace formulas with values using this shortcut [quick tip]

Posted on November 11th, 2013 in Excel Howtos - 52 comments

Often in my work, I need to replace a bunch of formulas with values. Blame it on old habits, but this is what I used to do:

  1. Copy the cells with formulas (CTRL+C)
  2. Press ALT+ESV and then enter.

While this is ok, it does take quite a bit of time and key strokes.

Here is a shorter way I recently learned…

  1. Copy the cells with formulas (CTRL+C)
  2. Press menu key and then v
  3. Done!

How to replace formulas with values in Excel?

You can also right click after step 1 and press v. works just the same.

What about you? How do you save time when working with Excel?

Using keyboard shortcuts is one of the best ways to save time when working with Excel. When you combine this with strong knowledge of formulas (related: top 10 formulas for analysts) you will be on your way to Excel ninja status.

What about you? what techniques you use to save time when working with Excel? Please share your tips & ideas using comments.

More on keyboard shortcuts:

If keyboard shortcuts are your thing, go thru below to pick up a few more:

 

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

52 Responses to “Replace formulas with values using this shortcut [quick tip]”

  1. Vitalie says:

    I put a Paste Values shortcut on the quick access toolbar, so it’s Alt+3 for me (since it’s the third shortcut in there). And my current keyboard has no Menu key

  2. JLeno says:

    Which Excel version are you using? In my (English) Excel 2007 this trick doesn’t work.

    Like Vitalie I use the QAT. I have paste values, formulas and formats under Alt+1,2,3 respectively. I think that is as efficient as it gets, though a major pitfall for me is that I also use these shortcuts on other laptops. So I have accidentaly saved files when I wanted to paste some values with Atl+1 :)

  3. Chandoo says:

    I should note that this feature works in Excel 2010 or above.

    • Michael (Micky) Avidan says:

      However, the right mouse click & moving, the range, back and forth, works well in versions: 2003-2013.
      (I assume that it will work also in 2000/2002 but have none of them installed in order to check this out).

      Michael (Micky) Avidan
      “Microsoft® Answers” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2014)
      ISRAEL

    • Binu Rajan says:

      In Excel 2007, I use the (Ctrl+C) to copy and then press ‘Menu’ key then ‘S’ and then ‘V’

  4. kfpot says:

    I ‘am using excel 2010 and select cell or range, then hold right mouse
    button , move en go back to same place on the sheet and choose paste values.

  5. Michael (Micky) Avidan says:

    Select the range and point to one of 4 borders of the selected range.
    The mouse pointer should change to a four-headed arrow.
    At this point, press and hold the right mouse button, and drag the whole range to the right, or left (out of the original cells) and then – without releasing the mouse button – drag the range back to its original cells.
    Release, now, the mouse button and in the appearing pop-up menu, click on ‘Copy Here as Values’.

    Michael (Micky) Avidan
    “Microsoft® Answers” – Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2014)
    ISRAEL

  6. Leon says:

    Nice tip.
    For the Dutch Excel version replace V by W.

  7. Suresh Kumar says:

    Dear Chandoo,

    I found one more shortcut through internet for paste special as values

    CTRL+ALT+V+V

    Thank You,
    SK

  8. Xiq says:

    I’m using the CTRL + ALT + V followed by V or T or E a lot!
    (I press the ALT with my thumb of my left hand)

    Also CTRL + 1
    (Using the base of my pinkie for pressing the CTRL)

  9. mattmaison says:

    PureText will work with any program, not just Excel.

    http://stevemiller.net/puretext/

  10. Gijs says:

    I use 2007 so this doesn’t work for me…

    But I have a nifty utility called “PureText” that strips all the formatting and leaves pastes anything as just raw text. I use it all the time with hotkey Ctrl-D, for eg pasting from web pages or word docs. Recommended!

  11. Rob says:

    I use Paste Values a LOT, so I’ve got a short macro assigned to a keyboard combinations (Ctrl-Shift-V), which will copy the selected range and paste values in it’s place.

    Private Sub CopyPasteValues()

    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    End Sub

    Prior to that, I used Alt-H-V-V to paste values.

  12. Anwaar says:

    For folks who don’t like to waste time reaching out to the mouse and prefer to use keyboard but do not have the the menu key on your keyboard, you can do the following,

    CTRL+C
    SHIFT + F10
    Then V
    Done.

    SHIFT F10 populates the menu.

  13. Daniel Lamarche says:

    Hi all. I too sometimes replace a formula with its value. What I do is highlight the formula in the Formula Bar then hit F9 on the keyboard. F9 is usually recalculate it in essence this is what it does in this context.
    Another formidable use of F9 in the formula bar is to highlight a portion of a formula by making sure the brackets are perfectly balanced and hit F9. This will recalculate only the selection and leave the rest of the formula intact.
    When I see a really complex formula and I want to understand it I use this technique to return the value of various segment of the formula. It helps me to figure it out much faster.
    Daniel

    • Michael (Micky) Avidan says:

      @Daniel,
      This is a very old & known procedure BUT(!) it does work on a single cell only – while we are dealing, here, with range of cells.

      Michael (Micky) Avidan
      “Microsoft® Answers” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2014)
      ISRAEL

  14. Tina Kurfurst says:

    Right click copy, right click paste-special values. I do it all day with fixing names on lists…

  15. Pat says:

    Try Ctl-Alt-V

    The reason it’s so powerful is that is works everywhere within the Office suite of prouducts including Outlook for pasting in whatever contextual ways are available within a specific application.

  16. Rob T says:

    Another vote for the Quick Access Toolbar from me. Paste values is my Alt+1 shortcut.

    The sequence of Ctrl+C Alt+1 has become as ingrained in my muscle memory as much as the good old “copy-from-one-application-to-another” (Ctrl+C > Alt+Tab > Ctrl+V)!

    For some reason, it’s never occurred to me to have the Formulas and Formats options as 2nd and 3rd on the QAT. I think I’ll do that now. Thanks JLeno!

    • JLeno says:

      For some reason, Paste Values and Paste Formulas have a funny-looking green dot as a symbol, but other than that it works like a charm!
      Perhaps a good topic for a new ‘quick tip’/survey? “What icons do you use in the QAT”
      For me it’s AutoFilter (the Ctrl+Shift+L shortcut is replaced with a macro), Send file as email, Toggle gridlines, and the Camera tool (which might be replaced or sent back on the QAT if I stumble on new potential QAT members)

  17. Alok Joshi says:

    It does not work in Excel 2007

  18. Kiev says:

    Convert formula to a value is also a frequent used action for me, but i prefer to use both hand figures, so i use

    Ctrl + C (Left hand figures)
    Menu Key (Right hand figure) +S+V (Left hand figures)

    Does anyone have this same habit as me?

  19. Rudra says:

    Chandoo atleast in this trick I was ahead of you. :)
    I have been using this key for ‘Right Click’ purposes. This also proves that not everyone knows everything about excel. All these days I was impressed with your knowledge but now I am impressed with your honesty too!

  20. Tina says:

    This may be a stupid question, but you reference ALT+ESV above. Did you mean ALT+ESC or ALT +E+S+V? That one caught me off guard.

    Thanks

    • JLeno says:

      It’s Alt, then E, then S for opening the Paste Special dialog (which is the old command for the Paste special menu in Excel 2003, that Microsoft also included in later versions), then V for Values.

      So no need to press Alt, E, S and V all at once, it’s sequential:)

  21. Greg says:

    This one for me is very quick and easy, only 2010 and above.

    ctrl + c to copy, then rightclick in cell you want to paste then press v that’s it.

  22. […] Replace formulas with values using this shortcut [quick tip] | Chandoo.org – Learn Microsoft E… […]

  23. Sue says:

    Hello!! As for a time-saving tip… my colleague came to me and needed to know where a list certain values fell in an array of value ranges. I saw your post about the obvious formula being too long I.e if(and(a2>b2),(a2<c2), "yes","no"), etc…. – especially if it needed further nested ifs. It mentioned using MEDIAN (as per Daniel Ferry) – which I could not get to work (but I am not awesome in Excel – yet). Anyway I do work with someone awesome in Excel – and he said just do a vlookup leave the last parameter blank like so – VLOOKUP(A1,Sheet2!A:C,3), where A & B contain the value ranges, and C is value to populate, if value falls between A & B. It works beautifully!! But, are there any downfalls to this method? Thank you!!

  24. Cason says:

    The easiest is:

    1. CTRL + V
    2. CTRL
    3. V

    Done.

    Pressing the ctrl by itself after a paste will open up the clickable menu box that appears after you paste. Pressing V will select the “Values” button.

  25. Ronnie says:

    I think some people seem to be missing the point of these shortcuts. They are meant be “keyboard” shortcuts. So anytime you mention “click” you have defeated the purpose of keeping your hands on the keyboard. When training on different software, I often mention that the mouse is not your friend. While I don’t always agree with myself on that, I believe there are many keyboard shortcuts that would improve people’s productivity. Look for the underlined letters in toolbars. Hold down the Alt key in Excel (and other apps) and see what pops up. Use these shortcuts to your advantage.

  26. TK says:

    To replace formula with value in a single cell I use [Edit] [Calc] i.e. [F2] [F9] function keys – no mouse clicks required!

  27. David says:

    Highlight the cell or cell range that you want converted to values. Move the mouse to the edge of your cell/range to get the Click and Drag Cursor. Press and hold the right mouse button, drag the cell/range slightly to the side and back to its original position, and let go of the right mouse button. From the Click and Drag popup menu, select Copy Here as Values Only. It sounds complicated, but once practiced, it takes very little time.

  28. David says:

    I also have a macro that will convert any contiguous/noncontiguous range of cells from formulas to values

    Dim rngCell As Range
    Dim rngSelection As Range
    Set rngSelection = Selection
    For Each rngCell In rngSelection
    rngCell.Value = rngCell.Value
    Next rngCell

  29. Dustin says:

    I use CTRL+ALT+V, V to paste formula values and CTRL+ALT+V, T to paste text without any formatting. The MENU, V works for both and it will save me a keystroke! Thanks :)

  30. Coxy says:

    Thanks y’all very helpful tips and contributions..

  31. upendra says:

    There are many ways to do this.
    1) If you are looking at a single cell, after entering the formula in cell , Just press F2 & F9. The value will be pasted in the cell instead of value.
    2) With the large chunk of data best option is to put Paste Values on Quick Access Tool bar and execute it with Alt+sequence number of Paste values command (For me it works as Alt+9 as paste values is placed as ninth icon on QAT)
    3) Select the Cells having formula, hold the mouse right button, move the data towards left and immediately towards the same cell, a menu with commands will appear. Select Paste Values.
    4) Lastly use key board comnination Alt + ESV -> Ok.

  32. Holly W. says:

    The best short cut/time saver I discovered a few years ago is copying a worksheet with full formatting in tact:

    select and hold CTRL (left hand) and with the right hand,
    L click the mouse on the sheet you want to copy, hold and drag to the right of that sheet ( drag it into sheet 2).

    Presto – an exact duplicate is made of the worksheet you were just on. This was such a time saver for me doing the monthly budget … I just rename the new sheet and wipe out what I don’t need to start input for that next month!

  33. […] Do you ever use the Window key on your computer keyboard? Chandoo shows how to use it as a quick way to paste as values […]

  34. Manpreet Singh says:

    i doesn’t work in excel 2007 :(

  35. hmjjbe says:

    Ctrl+C then Alt+3 on the QAT. doesn’t get any better.

    now if someone could just let me know how I can use the format painter on the QAT (Ctrl+C then Alt+2 for me) in an area more than 1 cell away from where I Ctrl+C that would be amazing. seems the only way to do this is to grab the mouse but that seems to defeat the purpose.

  36. lumographer says:

    Also, in case you use ASAP Utilities (which is an awesome, awesome addin), using Selecting cells with formulae and using the shortcut Ctrl+Alt+P will not only paste values, but also paste values of only visible cells in case of an Autofilter, so you don’t need to unfilter cells, paste special and filter again. :)

  37. […] learned this one from Chandoo. He is a well-known Excel guru and an inspiration for […]

  38. nauman says:

    is there a way to copy and paste special a range in which there are a bunch of formulas and the range is filtered, I mean few rows in range are hidden due to filter while others are shown. I have tried go to special to select visible cells then used copy and paste special values but this function didn’t perform. pls share any shortcut method to do this.

  39. […] pointed me to a recent post right here called Replace formulas with values using this shortcut that covers this very tip, but somehow I missed […]

  40. […] pointed me to a recent post right here called Replace formulas with values using this shortcut that covers this very tip, but somehow I missed […]

  41. […] Acabo de leer en el blog de Chandoo (uno de los grandes en esto de Excel), un truco que no conocía que me será muy útil a partir de ahora, Utilizar la tecla Menú + V para pegar valores. […]

Leave a Reply