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.