MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Quickly Convert Formulas to Values


July 03, 2019 - by Bill Jelen

Excel Quickly Convert Formulas to Values. Photo Credit: Jp Valery at Unsplash.com

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.

Cells D2:D14 have a formula. The range is selected.

You are probably using one of the ways shown below.


Here are some ways to paste values: 1. Ctrl+C, open the Paste dropdown, click on the icon for Values. 2. Ctrl+C, Alt+E S V Enter. 3. Ctrl+C, Ctrl+Alt+V, V, Enter. 4. Ctrl+C, Ctrl+V, Press and release Ctrl, then press V. 5. Ctrl+C, Alt, H, V, V. 6. Right-click, choose Copy. Right-click, choose Values from the Paste Options row.

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:

  1. Select the data.
  2. Go to the right edge of the selection box.
  3. Hold down the right mouse button while you drag the box to the right.

    The mouse pointer is shown dragging an outline of the range to the right.
  4. Keep holding down the right mouse button while you drag the box back to the original location.
  5. 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.

When you release the right mouse button, there are 11 choices: Move Here, Copy Here, Copy Here As Values Only, Copy Here as Formats Only, Link Here, Create Hyperlink Here, Shift Down & Copy, Shift Right and Copy, Shift Down and Move, Shift Right and Move, or Cancel.

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:

A photograph of the Application key on the keyboard. It looks like a mouse pointer over a context menu.

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.

Title Photo: Jp Valery at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.