How to Remove Formulas in Excel
(and Keep Values)

Excel formulas are set to auto-update themselves as and when any input to them is changed 📋

While this is a plus point in terms of accuracy and speed, it might also become a downside in certain circumstances where you’d want the results to be rather static and stable.

In the tutorial below, I will walk you through such circumstances, when can you expect them, and if you do – how do you deal with them.

We are looking into all the possible ways to remove formulas in Excel with zero harm to your data 🛡

Follow me till the end, and to practice along, download our free sample workbook for this tutorial here.

When to remove a formula in Microsoft Excel?

Reading through the ways to remove a formula in MS Excel would only be useful if you first know, why – and when you need to remove formulas in Excel.

Except for the case where you just made a typo and want to delete it now 😂

Some common instances where you’d need to remove formulas in Excel could be:

  • The Excel file needs to be shared with others who do not need to see what runs behind the face of the sheet. Could be for confidentiality reasons or sometimes just to make the process simple. Other users might just accidentally push a button and mess up the formulas to end up reading the wrong results.
  • You are only concerned with the results of the formulas so you can delete the underlying formulas and keep the calculated values only.
  • To protect your complex or proprietary formulas from being copied (intellectual property as we say).
  • To prevent your spreadsheet from acting sluggish due to large and complex formulas, especially if it’s going to users who might not have an equally advanced version of Excel (in which case some formulas might not even work at their end) or PCs.
  • Avoid unnecessarily large file sizes.

And much more – you name it. An easy way to help all these issues is to get rid of the formulas in your sheet and in this Excel tutorial, I will show you how to do that 👀

How to remove a formula in Excel?

Removing formulas in Excel can take a variety of ways – depending on your situation and need.

Below we are covering them all so make sure you’re plugged in here onwards 🔌

Remove the formula but keep the value

Simply removing a formula in Excel is all about selecting the relevant cell and hitting down the backspace key.

But let me show you how can you get rid of the formula (that keeps auto-recalculating as any cell value moves) but retain the value it produced.

Let’s put together an example using the VLOOKUP function (a function that’s still a mystery to a multitude of Excel users 🤔)

The example below is pretty straightforward – we got some employee codes, their names, and designations.

Data for VLOOKUP

Time to run the VLOOKUP function to find the designation for a given employee code, say 2544.

Running the VLOOKUP function

Cool! VLOOKUP has picked it out right – we have a Senior Manager working against the staff code 2544.

Now, I want to lock this result as a value and remove the VLOOKUP formula running behind it.

Simple enough – follow me:

  1. Select the cell where VLOOKUP is running (G3).
Copy here the VLOOKUP formula
  1. Copy the value.

Pro Tip!

The trick is to copy the result of the VLOOKUP formula and paste it as a value (text) only.

With this, whatever we do to the formula or the underlying array, the result remains unchanged since it’s no longer based on a formula – it’s just text 📋

  1. To paste it as a value, right-click on a cell, and go to Paste Special > Value (the 123 icon).
Paste values option

Taking you to the formula bar to show we have a simple value for the VLOOKUP function now, and the formula has been removed.

Easy, right?

Remove formula but keep data

Remove a formula using only shortcuts

Time to reinforce some keyboard mastery where you can effortlessly delete formulas from a given range of cells without having to reach out for the mouse 🖱

Select the cells containing the formulas.

Select values
  1. Press “Ctrl + C” to copy the cell values.
Keyboard shortcut for copy
  1. Press the following keys in a sequential order: Alt > E > S > V
Press Alt > E > S > V
  1. Alternatively, after you’ve copied the cell content, use the sequential key combination: Alt > H > V > V
Alt > H > V > V

Both the above are shortcuts for Paste Special > Values and will replace formulas with simple values.

Replaced by Values

Remove an array formula

Array formulas make a very exciting topic of Excel – and if, I am honest, confusing too.

Understanding and using array formulas can be a task but, once played, removing them can be an even bigger one.

In the example below, an array formula is used to calculate the product of the quantity and price of a list of items (Column B and Column C) 🛒

An array formula

To identify an array formula, look out for two curly brackets enclosing it. You can turn a formula into an array formula by pressing down a combination of three keys (Shift + Ctrl + Enter) instead of the Enter key only.

Kasper Langmann, Microsoft Office Specialist

Okay, so how to remove an array formula and delete the results?

If you are like: Obviously by pressing by backspace/delete key – Duh. Sorry to burst your bubble but, doing so would prompt the following error in Excel.

Deletion error special dialog box

To delete/remove an array formula (in its entirety):

  1. Select the entire array where the results of the formula sit.
  2. Now press the delete key.

Or you can:

  1. Go to the formula bar > delete the entire formula.
Deleting the formula in the formula bar
  1. Press the combination keys: Ctrl + Shift + Enter

This is to delete the results of an array formula. But what if you want to remove the array formula but not the symmetric array (the values) itself?

  1. Select the entire array (Cell D2:D6).
  2. Copy the values.
  3. Select the first cell of the array.
  4. Right Click > Go to Paste Special > Paste Values
Paste special as values

The formula is gone, but the calculated array stays as values.

Only the array remains

Remove the formula from a table column

Creating data tables in Excel is an excellent way to store and manage data.

An Excel table with data

This table has the SUM formula running in Column C.

Formula running in Column C

To remove these formulas from the entire Column C of this table:

  1. Click on the column C header (Sum).
  2. Press down the Shift + Ctrl + Down Arrow key. This will help you select the entire populated column at once.
Column C selected
  1. Press Ctrl + C to have it copied
  2. Right Click > Go to Paste Special > Paste Values
Paste special as values

Check again! The entire column is now replaced with values – no more formulas running in the background.

column formula deleted

Remove all formulas from an Excel workbook

Let us now talk about the situation where you want to remove all the formulas from an Excel workbook – at once.

Yes, that’s possible, and it’s only a matter of a minute or less ⏱

Excel workbook with identically patterned sheets

If your workbook has static-looking sheets, for example, all the sheets of your book have formulas embedded in the same cells, say Cell C2:C7.

All Excel spreadsheets have the same cells with formulas

In this case, you can group such sheets and remove the formulas at once.

  1. Select the first sheet, hold down the “Shift key” and select the last sheet containing formulas.
Grouping multiple sheets

The sheet tabs that go white are the grouped ones. Whatever action you perform on either of these grouped sheets will be performed on all the grouped sheets for the same cells automatically.

Kasper Langmann, Microsoft Office Specialist
  1. Go to any sheet from the selected ones, say the first one.
  2. Select the cells containing the formulas.
Selected cells in any one sheet
  1. Press “Ctrl + C” to copy the cell values.
  2. Paste them as values by right-clicking on the same cells > Paste Options > Values (V).
Pasting formulas as values

Formulas removed for Sheet 1. Swap through the rest of the sheets and the same is done for them all.

Formulas clear from all the sheets

Excel workbook with sheets in varied patterns

However, if different sheets of your book have formulas scattered across different cells, the above technique won’t really work for you.

We will work this situation out by running a VBA Code.

  1. Copy the one below.
Click to copy
  1. Press the Alt key + F11 key to launch the VBA Editor
  2. From the Ribbon on top, go to the Insert tab > Module.
  3. Paste the VBA code you copied above in this Module pane.
Copy/paste the VBA code
  1. Close the editor and press the Alt key together with the F8 key.
  2. Run the “RemoveFormulasFromTheWholeWorkBook” named Macro.
Running the macro

All the formulas from your entire workbook have now been removed – and replaced by simple values.

You will have your mind blown when you try it 🤩

Remove and replace a part of a formula

A formula like below has multiple parts:

= SUM (A2:A5) + SUM (B2:B5) + SUM (C2:C5)

To remove this entire formula and replace it with its calculated value:

  1. Select the cell that contains this formula.
  2. Double-click the cell to activate the formula, or press the F2 key. Excel formulas when active take a colorful formatting like below.
Activating the formula
  1. Press the F9 key. Excel will replace the formula with its calculated value as below.
Replaced by calculated value

And if you don’t want the entire formula removed and replaced, but only a part of it, do this:

  1. Double-click the cell to activate the formula, or press the F2 key.
  2. Select only the to-be-replaced part of the formula; say SUM (C2:C5) to tell Excel you only want part replacements.
Selecting a part of the formula
  1. Press the F9 key. Excel will only replace the selected part of the formula with its calculated value as below.
Replaced by calculated value

Never knew this before? More surprises are on the way 🎊

Remove/Disable an automatic formula

Excel is smart and that we know. You give it a formula, and it will give you the results for it.

But this doesn’t just stop there. Every time it detects a change that affects the output of a formula, it recalculates it.

To stop this from happening (basically disabling a formula so that it is not automatically recalculated):

  1. Go to the Formulas Tab > Calculation options.
  2. Set it to Manual.
Setting calculations to manual

Now, whatever you do to your sheet, the output for any of your formulas won’t move unless you manually recalculate it.

See here:

Formula with disabled auto-recalculation

Not that Excel has made a mistake adding up 20 and 30 giving back an output of 60.

I changed one of the values after setting the calculation mode to Manual (changed 30 to 20).

Excel just didn’t automatically recalculate the formula; hence the output remains unchanged.

That’s it – Now What?

You’ve just ended up learning almost all the techniques out there to remove formulas in Excel and preserve the resulting values 💡

From doing it cell by cell to having it done for an entire book in a blink. Perfect!

But how good are you at running Excel functions and other formulas?

There are a handful of Excel formulas and functions that you can use to produce the results you need. Particularly, the top 3 winning functions of Excel – SUM, IF, and VLOOKUP functions.

My 3-part free online course offers all the details you need to know to master these 3 – check it out now.

Other Relevant Resources

Excel offers a wide variety of functions to its users. From financial functions to logical functions to so many more.

But you’d often see these functions not going your way or posing annoying errors. I have very comprehensively covered some of the most asked formula-related questions in my guides below.

Check these Excel tips out, and there’s a high chance you’ll find the solution to what’s making your formulas/functions fail. Happy spread-sheeting to you!


Written by Kasper Langmann, co-founder of Spreadsheeto and a certified Microsoft Office Specialist.

This tutorial reflects over 20 hours of dedicated research and writing, based on my 10+ years of professional Microsoft Excel experience.

Last updated on March 4th, 2024.

One last thing before you go: make sure to sign up for my free Microsoft Excel course!