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.

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.

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

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).

2. 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 📋

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

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 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.

2. Press “Ctrl + C” to copy the cell values.

3. Press the following keys in a sequential order: Alt > E > S > V

4. Alternatively, after you’ve copied the cell content, use the sequential key combination: Alt > H > V > V

Both the above are shortcuts for Paste Special > Values and will replace formulas with simple 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) 🛒

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.

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:

3. Go to the formula bar > delete the entire formula.

4. 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

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

Remove the formula from a table column

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

This table has the SUM 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.

3. Press Ctrl + C to have it copied
4. Right Click > Go to Paste Special > Paste Values

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

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.

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.

2. Go to any sheet from the selected ones, say the first one.
3. Select the cells containing the formulas.

4. Press “Ctrl + C” to copy the cell values.
5. Paste them as values by right-clicking on the same cells > Paste Options > Values (V).

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

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.

2. Press the Alt key + F11 key to launch the VBA Editor
3. From the Ribbon on top, go to the Insert tab > Module.
4. Paste the VBA code you copied above in this Module pane.

5. Close the editor and press the Alt key together with the F8 key.
6. Run the “RemoveFormulasFromTheWholeWorkBook” named 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.

3. Press the F9 key. Excel will replace the formula with its calculated value as below.

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.

3. Press the F9 key. Excel will only replace the selected part of the formula with its calculated value as below.

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.

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

See here:

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.

• Do your formulas stay visible in the cells? Are the results not showing? Here’s how to fix it
• Are your formulas not updating? Here’s how to enable automatic calculation
• How to fix Excel formulas not calculating (Refresh Formulas)
• How to Show Formulas in Excel Sheet (+Shortcut Key)

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!