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:
- Copy the cells with formulas (CTRL+C)
- 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…
- Copy the cells with formulas (CTRL+C)
- Press menu key and then v
- Done!
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:
- 5 keyboard shortcuts to write better formulas
- 10 keyboard shortcuts I can’t live without
- Comprehensive list of Excel keyboard shortcuts
- Do not forget mouse shortcuts!
- Game: How fast can you finish this Excel challenge with keyboard?
56 Responses to “Replace formulas with values using this shortcut [quick tip]”
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
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 🙂
I should note that this feature works in Excel 2010 or above.
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
In Excel 2007, I use the (Ctrl+C) to copy and then press 'Menu' key then 'S' and then 'V'
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.
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
Nice tip.
For the Dutch Excel version replace V by W.
Dear Chandoo,
I found one more shortcut through internet for paste special as values
CTRL+ALT+V+V
Thank You,
SK
+1.. this should work in all versions of Excel
In Office 13 and Windows 8 (no menu key), you can also use CTRL+v for basic paste, then wait a sec, then press CTRL+V for values or CTRL+F for formulae or CTRL+R for formatting.
Thanks.
CTRL+ALT+V is a good one, brings up the paste special box. Will be a lifesaver for me from now on.
Just moved to Windows 8 and the missing menu key is a pain in Excel. The lack of num pad on the new laptop is another one.
Thanks Suresh it works
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)
PureText will work with any program, not just Excel.
http://stevemiller.net/puretext/
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!
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.
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.
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
@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
Right click copy, right click paste-special values. I do it all day with fixing names on lists...
I'm with you... mouse is the easiest and I do it everyday!
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.
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!
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)
It does not work in Excel 2007
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?
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!
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
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:)
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.
[…] Replace formulas with values using this shortcut [quick tip] | Chandoo.org – Learn Microsoft E… […]
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!!
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.
Great and a fantastic short cut which I've found after all these years. Unfortunately, my Logitech MX Keyboard, doesn't have the dedicated shortcut key.
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.
To replace formula with value in a single cell I use [Edit] [Calc] i.e. [F2] [F9] function keys - no mouse clicks required!
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.
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
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 🙂
Thanks y'all very helpful tips and contributions..
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.
How is it a shortcut if you are using mouse?
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!
[…] 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 […]
i doesn't work in excel 2007 🙁
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.
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. 🙂
[…] learned this one from Chandoo. He is a well-known Excel guru and an inspiration for […]
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.
[…] 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 […]
[…] 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 […]
[…] 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. […]
How to use duplicate in workbook ???????
Please suggest...
I just added copy and pastevalues to the Quick Access Toolbar.
To copy and pastevalues I now press Alt+1 and then Alt+2.
Very simple!!
thx
How to remove formula without removing filter