How to Remove Formulas and keep data in Excel

This blog post is about 5 methods to remove formulas from an Excel worksheet without altering the results.

In the following dataset we have formulas in the columns D and E. Let’s see how to remove the formulas in the columns D and E while keeping the results intact.

Copy and Paste as Values

To remove the formulas from a data range and keep the results,

Copy the data range containing formulas > right-click on the top left corner of the selection > Select the second option (Values) listed under Paste Options:

And the formulas in the selected cells are gone.

Keyboard Shortcut to Paste as Values

Alt + E + S + V + Enter is the keyboard shortcut to Paste as Values in Excel

Select the data range containing Formulas > press Ctrl + C to Copy > press Alt + E + S + V + Enter to Paste as Values

Rarely used trick to Paste as Values

This one is a less known Excel trick to Paste the copied data as Values.

Select the data > right-click on the border of the selection > holding the right mouse button, drag the selection to left or right > bring it back and release the mouse button for a menu with a few options to move and copy the selected data

select Copy Here as Values only to Paste the copied data as Values.

Macro to Paste as Values

The following code when executed will remove formulas from every cell of an Excel Worksheet.

Sub PasteAsValuesSameSheet()
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = True
End Sub

Adding the Paste as Values option to Quick Access Toolbar

To add the Paste as Values option to Quick Access Toolbar (QAT) of Excel,

right-click on the QAT > Customize Quick Access Toolbar…

In the Excel Options dialog, select All Commands from the drop menu under the heading Choose commands from: > select Values [Paste Values] from the list of commands > Add > Click on OK

Paste Values is added to the QAT. Right now the option is greyed out as we haven’t copied anything.

As soon we copy a cell or cells, Paste Values option will become active.

Click on Paste Values to paste the copied data as Values.