Excel 2020: Quickly Convert Formulas to Values
July 06, 2020 - by Bill Jelen
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 below.
For Those Who Prefer Using the Mouse
If you prefer to use the mouse, nothing is faster than this trick 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.
For Those Who Prefer Using 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. Its official name is the Application key. I’ve heard it called the Program key, the Menu key, the Context Menu key, and 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.
Title Photo: Jp Valery at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.