How to Delete Cell Formulas in Excel (While Retaining Its Values)Step by Step Instructions with Screenshots

Written By:
Adiste Mae
Reviewed By:
FundsNet Staff

The formula in cells is essential.

But when we do not know how and when to use it, it will eventually become cumbersome and result in wrong outputs that would waste our time fixing it.

In Excel, there are cases where deleting cell formulas and just retaining their data values is essential and can actually make us accomplish the tasks easier and faster. 

This Excel tutorial will illustrate how to delete formulas while retaining their data values.

Delete Excel Formulas and Keep the Values (At the Same Time)

In the table shown below, you will see our given datasets.

Here, we will aim to delete column B formulas while retaining their values at the same time. 

 

Here is the step-by-step process:

  1. Click the cell/s that contain/s the formula/s. Refer to column D.
  2. After selecting, right-click. A dialog box will pop up. Click the copy button. If you want to use the shortcut for copy, simply press the CTRL key + C.
  3. Moving dash lines will then surround the cell selections. Right-click for the second time.
  4. See the ‘Paste Special’ option in the list. Go ahead and click it. The Paste Special dialog box will now pop up.
  5. Under the ‘Paste’ options, mark “Values” and under the ‘Operation’ options, mark ‘None’.
  6. Lastly, click the OK button on the bottom right area of the dialog box. This method will then delete the existing formulas present in the cell/s or column/s and, on the other hand, will retain the data.

You can also opt to use the copy-and-paste keyboard shortcuts in deleting the formula and retaining its values.

See the table below:

To duplicate cell/s CTRL key + C
To paste/ input as data(ALT key + E + S + V) + Enter key

The example that we performed above is for a certain range of cell/s or column/s in which you wanted to delete the formula and retain its value.

You can also do this in your selection by simply selecting all the cells with data values and then following the step-by-step process shown above. 

However, if you want to remove all the formulas and their value/data you can just simply select or highlight the cells and press the Delete key.

We hope these illustrations will help you with your tasks.