Excel 2024: Quickly Convert Formulas to Values

June 13, 2024 - by Bill Jelen

Converting live formulas to values is a task that can be done many 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. Note that Ctrl+Shift+V is new since 2022.

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.

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. Starting in 2022, Ctrl+Shift+V will paste values. Back 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. 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.

Bonus Tip: Skip Blanks While Pasting

A mysterious part of the Paste Special dialog is the Skip Blanks feature. What does it do? Say that you have a list of existing values. In another column, you have updates for some of those values but not all of them. In the next figure, select D2:D10 and Copy.

There are numbers in B2:B10. You want to update four of the numbers, so you type replacement values in D2, D4, D7, and D10. Select the entire range D2:D10 and Ctrl+C to copy.

Select the original values in B2:B10. Do a Paste Special and select Skip Blanks.

In the Paste Special dialog, choose the box for Skip Blanks.

The 87 in D2 overwrites the 92 in B2, but Excel does not clear out the 51 in B3.

The four numbers are updated. But anything next to a blank cell remains untouched.

Thanks to Ed Bott, Ken McLean, Melih Met, and Bryony Stewart-Seume for suggesting this feature. Laura Lewis suggested the Skip Blanks trick.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by ilgmyzin on Unsplash