How to remove formula in Excel?

An Excel worksheet can contain multiple formulas applied to its data. These formulas help perform various calculations and manipulate the data. Excel users can apply more than one formula to data.

According to user needs, they require to delete all the formulas applied on an Excel worksheet. Excel allows to do that. Generally, when you remove a formula, Excel data also reflects and the resultant value gets deleted with it. Thus, Excel offers a special feature and allows to remove a formula without deleting the resultant value.

Disadvantage of Excel sheet with formulas

There can be many key disadvantages of an Excel worksheet with a lot of formulas.

1. When an Excel sheet contains a lot of formulas applied to it, the size of the file also increases.
2. Excel file become too heavy in size to load and process. It takes to open and send to others as well.
3. In small files, you may not face this issue. But large file that also contains many formulas applied on data; you may face this problem while sending the file through email due to its large size.
4. It can slow down the speed of accessing the Excel file.

Remove formula keep data

Generally, when a formula is removed from an Excel worksheet, its associated resultant values also get deleted automatically. But sometimes, the Excel users only want to remove the formula but keep the data. Excel has a feature to remove the formula and keep the data. It can be done using Paste Special option.

When we copy and simple paste the data from one cell to another, formula also go with it.

A tool named Kutools Plus also allows to do this and comes with several other features. You can download and add to your Excel to use these features. One of the features is to remove formulas from Excel sheets without deleting their resultant data.

Step to remove formula (keep the data)

Suppose we have an Excel dataset in which formula is applied on D column (Total price). It does not require any complex steps to get this process done. See those steps below:

Step 1: Select column D cells that is containing formulas (=Price*Quantity) on it.

Step 2: Right-click any of these selected cells and copy the data by clicking the Copy option in the list or use the Ctrl+C shortcut command.

Note: When you copy the data and paste it to the special mode by choosing Values option, all the formulas of selected data get removed and values kept as it is.

Step 3: Once again, right-click on any selected cells, and then select the Paste Special option.

A dialogue box for the paste special will open.

Step 4: In this dialogue box, mark the Values radio button and click OK. This option is selected to keep the value even after removing the formula.

Step 5: You can now see that the formulas have been removed from the column D cells and values are kept and remained the same.

Now, no formula is here. You can check by clicking any of the cells in column D.

Note: Once the formula is removed and the data is kept, further automatic updation is not possible in it as the formula is removed.

Remove the formulas using Pasting as Value method

Besides this, you can do it in one more way to remove the formulas from an Excel worksheet and keep the data. Before this, we will use an Excel feature to find the cells with formula. For this, you have to follow the steps given below:

Step 1: Press the Ctrl+G to open the Go To panel, where click the Special button.

Step 2: Mark the Formula option and click the OK to select the cells containing some formula on it automatically.

Step 3: Now, copy the selected cells using Ctrl+C, as showing in the below screenshot.

Step 4: Inside the Home tab, click on the Paste dropdown button present at the first place in the ribbon and choose the Values (V) option.

Step 5: All the formulas applied to the selected cells have been removed and values are kept the same. You can see that no formula is there but values only.

Data is not lost and remained as it is.

You may face this type of error while pasting the data.

This may occur because of two reasons:

• Your selection is at multiple places in the same Excel worksheet.
• When your selection is not in the same row or in the same column. For example – A2:C8 is not valid, while D2:D16 is valid because D2:D16 selection is in the same column.

What happens after removing formula

1. Your Excel file gets boosted up the speed and fast in process.
2. Size of the Excel file will reduce.
3. The resultant values did not remove with formulas using the following methods. So, data is safe.
4. This is one biggest disadvantage of removing formula from Excel data that – you cannot analyze how you got that value.
5. Any changes or updates in values used in formula, will not reflect on resultant values now.