**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:

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

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

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

- 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:

- Copy the cells –
**Control + C** - 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:

- Open the VBA editor in Excel (by holding the ALT key and then pressing the F11 key)
- Insert a new module by clicking on the Insert option in the menu and then clicking on the Module option
- Copy and paste the above VBA code into the module code window.
- Close the VBA editor.
- In Excel, select the range of cells that contains the formulas you wish to remove.
- 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

yOU HAVE JUST SAVED ME HOURS OF WORK. tHANK YOU.

Glad the article helped ðŸ™‚