How do you get rid of a formula in an Excel cell?

Before you do anything, save a new version. You have probably spent hours building all the formulas. Although you don’t want other people to see them, do you really want to have to recreate them? It is very easy to accidently remove all the formula and then overwrite the original. Start by saving a new version and call it something like VALUES ONLY.

So for your own sanity, if you want to delete formula in excel without deleting data make sure it is not on the original.

Remove the formulas within a sheet (but leave the data)

All you need to do is

• highlight the area (or the whole sheet)
• Copy it (CTRL + C or whatever way you use to Copy cells)
• Click on Paste Special (you don’t have to choose where it will go- it will overwrite what it copied)
• Choose the paste as Values icon.

When you look at the cells again you will see that the cell that previously had a formula (cell G12 below) now only shows the result.

Across many sheets (at the same time)

The above is useful for a single sheet. But what if you have 20 different sheets. You can either do each sheet individually or else do everything in one go.

The first step is to tell Excel that you want it to do the same thing to all the sheets you highlight. To do this,

• Click on the first sheet
• Hold your SHIFT button down
• Click on the last sheet

You will notice that the colour of the sheet names turns whiter and the name of the file includes the word GROUP. This sheets are now grouped so whatever you do to one, happens to all

WARNING: When you do this it is critical that you switch it off as soon as you are finished

Now follow the same steps as before i.e.

• highlight the area (or the whole sheet) in the sheet you are (don’t worry about the other sheets)
• Copy it (CTRL + C or whatever way you use to Copy cells)
• Click on Paste Special
• Choose the paste as Values icon.

Because the sheets are grouped, it will do the exact same thing on all the other sheets i.e.

• It will copy sheet 1 and paste as values onto sheet 1, then
• It will copy sheet 2 and paste as values onto sheet 2, etc

Be careful not to click into any individual cell and make a change. When you click on a cell, it assumes that you want to take the contents of the cell and do something with it, but if you click in the cell, it assumes you want all the grouped cells to assume the same contents.

You will see now that all the sheets only have numbers in them, no formula.

Immediately switch off the grouping feature by right clicking on any one of the sheets and clicking UNGROUP sheets

So now you have removed all the formulas from Excel and left only values.

Want to learn more about Microsoft Excel? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

If you have a Microsoft Excel spreadsheet with a lot of formulas where you don't anticipate the results and inputs will change, it can sometimes be easier to simply replace those formulas with their computed values. You can replace some or all of a formula with a constant value whenever you wish. You can also set formulas not to automatically recompute when you change input values, until you tell Excel to do so.

Remove a Formula in Excel

In some cases, you may use an Excel formula to do a multi-step calculation but you'd prefer to simply replace the results by the actual number rather than by the formula. This can useful, if you want to reorganize a spreadsheet for presentation purposes or you just don't want to worry about what will happen, if you change specific input cells.

For example, if you're using the VLOOKUP formula function that pulls in data from elsewhere in the spreadsheet, you may want to clear that section of the spreadsheet without worrying about VLOOKUP problems elsewhere in your formulas.

Whatever the reason, you can remove a formula in Excel and replace it with its value as easily as copying and pasting. Simply use your mouse to select the area of the spreadsheet that contains the formulas in question and click the "Copy" button. Then, with that area of your spreadsheet still selected, click the "Paste" button. After that, click the arrow next to the "Paste Options" button and click "Values Only." If you prefer, you could manually type the number or other formula result into the cells individually, though this is usually more tedious and prone to human error.

Alternatively, if you are in the midst of editing a cell, you can use the Excel convert formula to value feature by hitting the F9 key on your keyboard to permanently replace the formula with its value.

Replace Part of a Formula

If you only want to replace part of a formula with its value, you can edit the formula code by double clicking on the cell.

Then, select the portion of the formula you want to replace and hit the F9 key to calculate its sub-value. Make sure you include an entire section of the formula, such as a subsection in parentheses including the opening and closing parenthesis or a reference to another cell in its entirety. Press Enter to replace that portion of the formula with its value.

Disable Automatically Computing Formula Values

If you have a lot of formulas in a spreadsheet, it can take a lot of time to recompute the formulas when you change the inputs. If you don't want it to automatically do so, you can reconfigure this setting.

Click the "File" tab, then choose "Options" and "Formulas." Under "Calculation options," change "Workbook Calculation" to "Manual." Then, formulas will automatically recalculate only when you save the spreadsheet or tell Excel to do so. If you also want to prevent it from automatically recalculating when you save the file, uncheck "Recalculate workbook before saving."

When you want to recalculate formulas, click "Calculate Now" on the Formulas tab to recalculate all open sheets or click "Calculate Sheet" to recalculate only the current sheet. Reset the "Workbook Calculation" setting to "Automatic" if you want to go back to automatic recalculation. Remember that if you don't use automatic calculation, your formula resets may not be in sync with your input cells.