Fast Formulas to Values

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!