How to Remove Formulas in Excel (and keep the data)

Watch Video – How to Remove Formulas in Excel

Sometimes you may want to remove formulas from the worksheets but keep the data.

It is also recommended to remove formulas in case you don’t need these. This may also help you speed up your workbook.

In this short Excel tutorial, I will show you how to remove the formulas in Excel but keep the data.

Remove Formulas in Excel (and Keep the Data)

Suppose you have a dataset as shown below (where you have formulas in column B), and you want to remove the formulas but keep the data.

Below are the steps to remove the formula while keeping the data:

1. Select the cells in column C that have the formula
2. Right-click on any of the selected cells and click on Copy (or use Control + C)

3. Again click on any cell in the selection and right-click.
4. Click on the Paste Special option. This will open the paste special dialog box

4. In the ‘Paste Special’ dialog box, select the ‘Values’ option.

5. Click OK

The above steps would remove the formula from the selected cells and keep the values.

Note: Another way to do this without opening the Paste Special dialog box is to use the Paste Special option, which is right there in the right-click menu. To use this, copy the range from which you want to remove the formulas, right-click anywhere in the copied range, then go to the Paste Special option (click on the more options arrow), and in the options that appear, click on the Paste Values option.

Also read: How to Paste without Formatting in Excel (Shortcuts)

Remove Formulas and Keep the Data (Keyboard Shortcut)

In case you prefer using the keyboard, you can also use the following shortcuts:

1. Copy the cells – Control + C
2. Paste as Values – ALT + E + S + V + Enter (press one after the other)

The above shortcut also uses the Paste Special dialog box, where ALT + E + S opens the Paste Special dialog box, then press the V key to select the value option.

Note: If you’re using a Mac, you can use the shortcut Command + Option + V to open the Paste Special dialog box

In the above example, we have selected the range of cells from which we wanted to remove the formula.

In case you want to remove it from all the cells in the worksheet, you can select all the cells and use the above steps)

In case you want to remove the formulas as well as the data, simply select the cells that have the formula and hit the Delete key,

I hope you found this Excel tutorial useful.

Also read: Excel Showing Formula Instead of Result (How to FIX!)

VBA Code to Remove Formulas

If you’re more comfortable using VBA, or you are using VBA to get stuff done and want to remove the formula while retaining the values, you can use the code below.

'Code developed by Steve Scott from https://spreadsheetplanet.com

Sub RemoveFormulasKeepData()

    Dim rng As Range

    ' Assign the currently selected range to the rng variable
    Set rng = Selection

    ' Copy the current selection
    rng.Copy

    ' Paste only the values (i.e., the results of the formulas) on the current selection
    rng.PasteSpecial Paste:=xlPasteValues

    ' Deselect the copied range and remove the "marching ants" border
    Application.CutCopyMode = False

End Sub

To use this code, follow the steps below:

1. Open the VBA editor in Excel (by holding the ALT key and then pressing the F11 key)
2. Insert a new module by clicking on the Insert option in the menu and then clicking on the Module option
3. Copy and paste the above VBA code into the module code window.
4. Close the VBA editor.
5. In Excel, select the range of cells that contains the formulas you wish to remove.
6. Press ALT + F8, choose RemoveFormulasKeepData from the list, and click Run.

If you want to remove formulas from the entire worksheet, select all the cells in the worksheet (using Control + A + A) and then run the code.

In case you want to remove all the formulas from all the worksheets in the open workbook, you can use the below VBA code:

'Code developed by Steve Scott from https://spreadsheetplanet.com

Sub RemoveFormulasKeepDataAllSheets()

    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Set rng to all the used cells in the worksheet
        Set rng = ws.UsedRange
        
        ' Loop through each cell in the rng
        For Each cell In rng
            ' If the cell has a formula, replace it with its value
            If cell.HasFormula Then
                cell.Value = cell.Value
            End If
        Next cell
    Next ws

End Sub

The above VBA code uses a For Each loop to go to each worksheet, then selects all the cells in the worksheet, and then removes the formulas by pasting the values over the cells.

One important thing to keep in mind when using the VBA code is that the changes done by it are irreversible. You can’t undo the changes done by the VBA code. So make sure you have a backup copy of the dataset just in case you may need it in the future.

You may also like the following Excel tutorials:

• How to Find Duplicates in Excel (Conditional Formatting/ Count If/ Filter)
• What does $ (dollar sign) mean in Excel Formulas?
• How to Remove Dashes (-) in Excel?
• What does $ (dollar sign) mean in Excel Formulas?
• How to Remove Macros from Excel?
• How to Break Links To External References in Excel?
• How To Hide Formulas In Excel Without Protecting the Sheet?
• Highlight Cells with Formulas in Excel