How to Remove Formula in Excel but Keep Data?

If you have ever worked in Excel, you know that formulas are a great tool to help you quickly calculate data. However, there may come a time when you need to remove the formula from a spreadsheet but keep the data intact. Removing a formula can be tricky, but with the right steps and a bit of patience, you can easily do it. In this article, we will discuss how to remove a formula in Excel but keep the data so that you can work with your spreadsheet without worrying about the formulas.

How to Remove Formula in Excel but Keep Data?

Open the Excel file you want to edit.
Select the range of data where you want to remove formulas.
Go to Select > Current Region.
Go to Home > Copy > Paste Special.
Choose “Values” in the Paste Special window.
Click OK.
Your formulas will be removed and the data will remain.

How to Remove Formulas from Excel but Keep Data

Removing formulas from Excel but keeping data can be a useful way to keep your spreadsheet clean and organized. Whether you are sharing the spreadsheet with someone else or just need to remove the formulas from the cells, there are a few easy steps to follow. In this article, we’ll explain how to remove formulas from Excel but keep data.

Copy and Paste Values

One of the simplest ways to remove formulas from Excel but keep data is to copy and paste the values. To do this, select the cells containing formulas and then copy them. Next, click the Paste icon, located in the Clipboard group on the Home tab. Select the Paste Values option. This will paste the data without the formulas, leaving only the values behind.

Clear Formats

Another option to remove formulas from Excel but keep the data is to clear the formats. To do this, select the cells containing the formulas. Go to the Home tab and click the Clear icon in the Editing group. Select the Clear Formats option. This will remove any formatting applied to the cells, including the formulas.

Inserting a Table

If you need to keep the formulas from being visible, you can insert a table into your Excel sheet. To do this, select the cells containing the formulas. Go to the Insert tab and select Table. This will create a table with the same data as your original cells, but without the formulas.

Using Find and Replace

Another option to remove formulas from Excel but keep the data is to use the Find and Replace feature. To do this, go to the Home tab and click the Find and Select icon. Select the Replace option. In the Find what box, type in the formula that you want to remove. Leave the Replace with box empty. This will remove all instances of the formula from the cells, leaving only the data behind.

Using a Macro

Finally, you can use a macro to remove formulas from Excel but keep the data. To do this, go to the View tab and select Macros. Enter a name for the macro and click the Create button. In the Visual Basic Editor, enter the following code:

Sub RemoveFormulas()

Dim xCell As Range
For Each xCell In Selection
xCell.Value = xCell.Value
Next xCell

End Sub

This code will loop through all of the selected cells and remove the formulas, leaving the data behind.

Using a Script

Finally, you can use a script to remove formulas from Excel but keep the data. To do this, go to the Insert tab and select Script. Enter a name for the script and click the Create button. In the Script Editor, enter the following code:

function removeFormulas() {

var spreadsheet = SpreadsheetApp.getActive();
var range = spreadsheet.getActiveRange();
for (var i = 0; i Using a Formula

Finally, you can use a formula to remove formulas from Excel but keep the data. To do this, select the cells containing the formulas. Go to the Formulas tab and click the Insert Function icon. Select the IF function. This will allow you to create a formula that will check if a cell contains a formula. If it does, the formula will return the value, otherwise it will return an empty value. To use this formula, enter the following:

=IF(ISTEXT(A1),A1,””)

This formula will check if cell A1 contains text. If it does, it will return the value of A1, otherwise it will return an empty string. This formula can then be copied and pasted across the cells containing formulas, removing them and leaving only the data.