Remove Formulas but Keep the Values in Excel (6 Ways)

by

To freeze results so they don’t change, or to send a file without your formulas, replace the formulas with their values. The quickest way in Microsoft 365 is Ctrl + C, then Ctrl + Shift + V.

Excel 365 shortcutCtrl + Shift + V
Any versionAlt, H, V, V
UndoCtrl + Z works immediately after

The data

Columns D and E contain formulas. The goal is to keep the numbers and lose the formulas.

Data with formulas in columns D and E

1. Paste as values

Copy the cells, right-click the first cell of the same range, and choose the Values icon (123) under Paste Options.

Paste Options with the Values icon
Formulas replaced by values

2. Keyboard shortcuts

Shortcut Works in
Ctrl + C, then Ctrl + Shift + V Microsoft 365 (2022 and later)
Ctrl + C, then Alt, H, V, V Excel 2010 and later
Ctrl + C, then Alt, E, S, V, Enter All versions (legacy menu keys)
Paste as values with a keyboard shortcut

3. Right-drag trick

Select the cells, right-click the selection border, drag slightly away and back, release, and choose Copy Here as Values Only.

Copy Here as Values Only menu

4. Quick Access Toolbar button

Right-click the Quick Access Toolbar → Customize, choose All Commands, add Paste Values, and click OK. It’s one click from then on, and Alt + a number selects it from the keyboard.

Adding Paste Values to the Quick Access Toolbar
Paste Values button active on the toolbar

5. Macro

This converts every formula on the active sheet to its value without using the clipboard:

Sub FormulasToValues()
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

More versions, such as selection only or every sheet, are on the convert formulas to values macro page.

Macro converting formulas to values

6. Keep the formulas, hide them

If you only want to stop others seeing the formulas, don’t delete them. Hide them and protect the sheet instead: see hide formulas.

Before you paste values

  • Save a copy first. After saving and closing, the formulas can’t be recovered.
  • Dynamic array results (spills) can be converted the same way. Select the whole spill range first.
  • To check which cells still have formulas, use Ctrl + {`} to show formulas.

Related