How to Remove Formulas But Keep Values in Excel

Why would you need to keep the cell values and remove their formulas?

Especially true for very busy and large worksheets, removing the formulas will speed things up in your file as you may notice how lagged all the actions in the workbook are.

If you’re not willing to disclose the formulas, you also have the option of hiding the formula cells. By doing so, the formula bar will be blank when you select a cell with its formula hidden. Hence it might be your preference to remove the formulas and paste back the values so at least the values show up in the formula when the cell is selected.

You’re trying to edit the value of the cell but are faced with the formula instead. The only way to manually edit the contents of a formula cell would then be to get rid of the formulas so the value left behind can be edited.

Or maybe you don’t need a certain formula anymore and are comfortable taking it down, letting its results be.

For any of the reasons above and more, we have set this tutorial for you to remove formulas from the cells but keep their values in Excel. You will be seeing a lot of copy-pasting today but with a twist. Plain copy-pasting pastes formulas, values, and formats. We’ll focus on just pasting the values today involving easy options using right-click, right-click and drag, the ribbon menu, and a keyboard shortcut.

Let’s get formula-reaping and value-sowing!

How To Remove Formulas But Keep Values In Excel

Using Paste Values from Right-click Context Menu

This method of removing formulas and keeping the cell values is a slight tweak in the regular copy and paste. The copying part is the same Ctrl + C but instead of pasting using Ctrl + V, we will paste the values from the Paste Options in the right-click menu. It’s not hard at all and this is how you can paste values from the right-click menu:

  • Select the cells you want the formulas removed from. In the Formula Bar, you can see the formula of the first cell in the selection.
  • Press the Ctrl + C keys to copy the selected cells. The marching ants line is an indication of the copied cells.

Using Paste Values from Right-click Context Menu

  • Right-click any of the selected cells and click on the Values option under Paste Options.

Using Paste Values from Right-click Context Menu

The values will be pasted from the copied cells to the selected cells (both are the same in this case).

Now if you look at a selected cell in the formula bar, it will show the value instead of the formula:

Using Paste Values from Right-click Context Menu

Using Paste Values from Ribbon Options

This method derives also from copy-paste and the paste bit of it is carried from the ribbon menu. Again, we will utilize the Paste Values option to remove the formulas and retain the values of the cells. Here we have the steps to remove formulas and use Paste Values from the ribbon menu to retain the values:

  • Select the formula cells.
  • Copy the cells in selection by pressing Ctrl + C. As of now, the formula bar shows the formula of the first selected cell.

Using Paste Values from Ribbon 

  • The very first section in the Home tab is the Clipboard Click on the Paste button with the little arrow under the clipboard button. Select Values under the Paste Values division in the menu.

Using Paste Values from Ribbon Options 

The formulas in the selected cells will be overwritten by the pasted values:

Using Paste Values from Ribbon Options 

Using Paste Options with Ctrl + V

Okay so you haven’t been able to get over Ctrl + V. Good news, you don’t have to! After copy-pasting with Ctrl + C and Ctrl + V, you will see a little Ctrl icon with a clipboard flashing at the bottom of the cells in the selection and you’ll have the option to paste the values in its menu. Easy? Let’s see the detailed steps on pasting values using the Paste Options (Ctrl) menu:

  • Select the cells containing the formulas.
  • Press Ctrl + C to copy the cells and then Ctrl + V to paste them. A clipboard icon will have appeared at the base of the selected cells.

Using Paste Options with Ctrl + V

  • Click on the (Ctrl) button flashing at the end of the selection. From the Paste Values section, select the Values

Using Paste Options with Ctrl + V

Paste Values pastes the copied values onto the formulas:

Using Paste Options with Ctrl + V

Using Right-click + Mouse Drag

Values of the cells can be pasted to a different location or the same location using right-click and drag. Let’s explain what we’ll be doing in the steps below. The right-click and drag menu doesn’t come up unless the selected range is dragged to a different location. The way we’ll work around that is to drag the range to a different location and then drag it back to its original location so we can access the right-click and drag menu. Thereon, we can copy the values over the formulas to remove the formulas without removing the values.

Now that you know why we do it this way, here’s how to do it this way:

  • Select the range with the formulas.
  • Hover the cursor over the border of the cell selection until the pointer turns to a four-headed arrow. Now right-click and drag the selection to any cell next to the current selection, all the while holding down the mouse’s right button.

Using Right-click + Mouse Drag

  • While still holding down the right button on the mouse, drag the selection back to its original place and release the button of the mouse.

Using Right-click + Mouse Drag

  • When you release the right-click, a context menu will appear.
  • Select the Copy Here as Values Only option from the context menu.

Using Right-click + Mouse Drag

This option copies the selected values onto the chosen location. Since we are pasting the values to the same location, the formulas will be replaced by the values:

Using Right-click + Mouse Drag

Using Keyboard Shortcut

If you’d rather hit the keyboard than the mouse, we also have a keyboard shortcut method of pasting values while removing formulas. The keyboard shortcut for removing formulas and copying and pasting values is:

Enter the keys below in succession with the formula cells selected and copied:

Alt + E + S + V + Enter

Alt + E + S keys in succession launch the Paste Special dialog box.

(Ctrl + Alt + V will also open the Paste Special dialog box but instead of pressing them in succession, you will hold down the Ctrl + Alt keys and then press the V key.)

V selects the Values radio button from the Paste section in the dialog box.

Enter selects the OK command button.

This is what you need to do to apply the keyboard shortcut for pasting values and removing formulas:

  • Select the cells containing the formulas you want to be removed.
  • Copy the cells in selection by pressing Ctrl + C.

Using Keyboard Shortcut

  • Press the Alt + E + S keys one after the other. The Paste Special dialog box will open.
  • Press the V key to select the Values radio button and then press the Enter key to select the OK button.

Using Keyboard Shortcut

This keyboard shortcut will apply the Paste Values feature, removing the formulas from the selected cells:

Using Keyboard Shortcut

Those were all the quick and easy ways to remove formulas but keep values in Excel. We hope all that copying and pasting got you out of your sticky situation. We’ll be back with more formulations to get you out of more Excel pickles. Copy that!

About Ankit Kaul

Ankit is a die-hard fan of Microsoft Excel and has been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'.