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 to Values!
I'll be podcasting this entire book, click the “i” on the top-right hand corner to get to the playlist for all of the videos!
Hey, welcome back to the MrExcel netcast, 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, select the 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, and 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 right-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 drag it 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, Ctrl, the flying Windows, key the Alt, then a spacebar. And then on the right side do you have Alt and Ctrl, 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+F10 is not as good as that key. Simply select the data, it’s Ctrl+C to copy, press, that right-click key and then let go and then press V, and that converts formulas to values. This started in Excel 2010, it's amazing how fast this is, Ctrl+C, the Application key, and V, will win every time.
Alright, these tips are just some of the tips in this book, buy the entire book, $10 as an e-book, $25 as a print book, get all of these amazing tips. Alright recap: You need to convert formulas to values. With the mouse, right-drag the border to the right, back to the left, let go, Copy Here As 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 V, the Shift+F10 will replace that right-click key. There are several other methods shown in the outtake, 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.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Alright, here's the outtake, 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 there, alright. The method I used for 20 years, Ctrl+C, Alt E S V Enter! A similar method, Ctrl+C, and then Ctrl+Alt+V which opens the Paste Special, V again and then Enter. This next one I had a cross out with strikethrough, that's F5, Ctrl+C, Ctrl+V, Ctrl, let go, V, that's now broken because when we're supposed to get Paste Options dropdown here, but in 2016 we're getting this stupid icon, the Quick Analysis, which is not very good.
Normally, opening Ctrl would open that and press V, so we don't have that one, you might still have that one. Ctrl+C, press the Alt key, press the H key, press the V key, press the V key, that's the new keyboard shortcut. 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 6 other ways, I know there's 9, so there's one that I missed, if you know what it is, go ahead, throw it down in the YouTube comments!
Download the sample file here: Podcast2019.xlsm
Title Photo: schuger-2 / Pixabay