Fast Formulas to Values
September 05, 2017 - by Bill Jelen
How to quickly convert formulas to Values. Everyone has a favorite way to do this, but my method is faster than yours.
I always say there are five ways to do anything in Excel. Converting live formulas to values is a task that has far more than five ways. But I will bet that I can teach you two ways that are faster than what you are using now.
The goal is to convert the formulas in column D to values.
You are probably using one of the ways shown here:
You Prefer using the Mouse
If you prefer to use the mouse, nothing is faster than this trick that I learned from Dave in Columbus, Indiana. You don’t even have to copy the cells using this technique:
- Select the data.
- Go to the right edge of the selection box.
Hold down the right mouse button while you drag the box to the right.
- Keep holding down the right mouse button while you drag the box back to the original location.
- When you release the right mouse button, in the menu that pops up select Copy Here As Values Only.
How does anyone ever randomly discover right-click, drag right, drag left, let go? It is not something that you would ever accidentally do.
It turns out the menu is called the Alternate Drag and Drop Menu. You get this menu any time you right-drag a selection somewhere.
In this case, you want the values to cover the original formulas, so you have to drag right and then back to the left.
You Prefer Keyboard Shortcuts
I love keyboard shortcuts. I can Ctrl + C, Alt + E, S, V, Enter faster than you can blink. But starting in Excel 2010, there is a faster way. Look at the bottom row of your keyboard. To the left of the spacebar, you usually have Ctrl, Windows, Alt. To the right of the spacebar is Alt, Something, and Ctrl.
What is that key between the right Alt and the right Ctrl? It has a picture of a mouse pointer and a pop-up menu. I’ve heard it called the Program key. I’ve heard it called the Application key. I’ve heard it called the Right-Click Key. I don’t care what you call it, but here is a picture of it:
Here is the fastest keyboard shortcut for copying and pasting values. Press Ctrl + C. Press and release the Program/Application/Right-Click key. Press V.
Again, this only works in Excel 2010 or newer.
And, if you have a Lenovo laptop, it is likely that you don’t even have this key. On a keyboard without this key, you can press Shift + F10 instead.
Thanks to Ed Bott, Ken McLean, Melih Met, and Bryony Stewart-Seume for suggesting this feature.
- You need to convert formulas to values
- Mouse: Right-drag the border of the range to the right, back to the left, let go
- Choose Copy Here as Values Only from the Alternate Drag-and-Drop menu
- Keyboard: If you have the right-click key, Ctrl+C, Right-Click Key, V
- If you don't have Program key, you can use Shift + F10 instead
- Several other methods shown in the outtake
- Learn Excel from MrExcel Podcast
- episode 2019 fast formulas of values
- I'll be podcasting this entire book
- click the eye on the top right hand
- corner to get to the playlist for all of
- the videos hey welcome back to the mr.
- XL neck has 2 i'm Bill Jelen today we're
- talking about how to convert formulas to
- values and while there's a lot of
- different ways that i'll show you in the
- outtakes here are my two favorite if
- you're a person who likes to use the
- mouse nothing better than this go to the
- right selected data don't copy the data
- go to the right edge of the data hold
- down the right mouse button drag to the
- right drag back to the left let go in
- this amazing menu this is called the
- alternate drag and drop menu pops out
- with copy here as values only now it
- turns out that you get this menu anytime
- you have a selection and you write drag
- that selection somewhere alright with
- all these beautiful choices lots of good
- stuff in here but since we need the
- values to replace the current formulas
- that's why you have to drag somewhere
- and dragged back to do that it's very
- easy very quick once you get the hang of
- it if you prefer to use the mouse
- fastest way to go alright if you are a
- keyboard shortcut person look on your
- keyboard lower left-hand side control
- the flying windows key the alt meta
- spacebar and then on the right side do
- you have alt and control but between the
- two of those this key I don't know what
- it's called a right-click key I've heard
- it called a program key an application
- key it's a tiny little mouse pointer
- pointing to a right click arrow if you
- have this key and by the way if you
- don't it's shift f10 but shift at 10
- it's not as good as that key simply
- select the data its ctrl-c to copy Press
- that right click key and then let go and
- then press v and that converts formulas
- the values this started in Excel 2010
- it's amazing how fast this is control
- see the application key and V will win
- every time alright these tips are just
- some of the tips in this book by the
- entire book ten dollars as an e-book $25
- has a print book get all of these
- amazing tips alright recap you need to
- formulas values with the mouse right
- drag the border to the right back to the
- left let go copy here is values only if
- you have the right click key and you
- prefer to use the keyboard ctrl C press
- and release the right click key and then
- press the shift f10 will replace that
- right click key there are several other
- methods shown in the altec I don't want
- to bore you with those i'm sure you
- already know those feel free to keep
- watching if you want to see the other
- methods ok i want to thank you for
- stopping by we'll see you next time for
- another netcast from MrExcel all right
- here's the I'll take some other ways to
- paste values first select the data come
- up here use the copy icon the paste icon
- is actually two icons go to the bottom
- half open it up and choose paste values
- they're all right the method I used for
- 20 years control C alt e' all e sv enter
- a similar method control c and then ctrl
- alt v which opens the pay special v
- again and then enter this next one I had
- a cross out with strikethrough that's f5
- ctrl-c ctrl-v control let go v that's
- now broken because when we're supposed
- to get c ctrl-c ctrl-v we're supposed to
- get a paste options drop-down here but
- in 2016 we're getting this stupid icon
- the quick analysis which is not very
- good normally opening control would open
- that and press v so we don't have that
- one you might still have that one
- control c press the alt key press the H
- key press the V key press the V key
- that's the new keyboard shortcuts or if
- you prefer to right click select the
- data right click and copy right click
- and choose that one there alright so
- that's one two three four five six other
- ways i know there's nine so there's one
- that i missed if you know what it is go
- ahead throw it down
Download the sample file here: Podcast2019.xlsm
Title Photo: schuger-2 / Pixabay