How Do You Remove a Formula in Excel but Keep the Data?

How Do You Remove a Formula in Excel but Keep the Data?

You have a column of numbers that look finished. A colleague asks for the file, or you need to send it outside your company, or you are about to delete the source sheet those numbers depend on. Then you click a cell and the formula bar shows =VLOOKUP(A2,Prices!A:C,3,FALSE) instead of a number, and you realise the column is not data at all. It is a set of instructions that happen to be pointing at something.

Press Delete and you lose the numbers along with the formulas. Leave the formulas in place and the file breaks the moment anything it references moves. What you actually want is the result without the machinery behind it.

Excel does this in one step, and it has done it the same way for twenty years. The problem is that the step is hidden behind a menu most people never open, so the usual workaround is retyping the numbers by hand.

How Do You Remove a Formula in Excel but Keep the Data?

Select the cells, copy them, then paste them straight back over themselves using Paste Values instead of a normal paste. The formulas are replaced by the numbers they last produced, and nothing else about the cells changes.

The keyboard route is the one worth learning. Select the range, press Ctrl+C, then press Ctrl+Alt+V to open the Paste Special dialog, press V for Values, and press Enter. That sequence works in every desktop version of Excel on Windows, including old ones.

Current Microsoft 365 builds also accept Ctrl+Shift+V as a direct paste-values shortcut, which is faster when it is available. If Ctrl+Shift+V does nothing on your machine, you are on a build that predates it, and the Ctrl+Alt+V route is the one to use.

Microsoft's own guidance on this is blunt about why the extra step exists: when you delete a formula, the result of the formula is deleted with it. There is no "keep the answer" option on the Delete key, so you have to overwrite the cell with its own output first.

Not sure which of your spreadsheet numbers are live formulas and which are frozen values? Get a free SEO and analytics audit at seo-stuff.com/free-audit and we will show you where your reporting is pointing at data that has already moved.

What Actually Happens When You Delete a Formula

Understanding why Paste Values is necessary makes the rest of this far less fiddly, and it explains several of the errors people run into.

A Cell Holds One Thing, Not Two

It is tempting to picture a formula cell as having a formula underneath and a value on top. It does not. The cell contains the formula, and the value you see on the grid is what Excel calculated the last time it recalculated that formula. The displayed number is a rendering, not a second stored item.

That is why Delete takes both. There is only one thing in the cell to delete.

Why a Formula Is Fragile

A formula is a reference to other cells, and references are only as stable as what they point at. Delete a row the formula reads from and you get #REF!. Rename a sheet and lookups that used the old name break. Open the file on a machine without the linked workbook and you get stale values or a prompt about updating links.

None of that is a bug. It is the whole point of a formula: it stays current because it keeps looking. Converting to values trades that currency for stability, which is exactly the trade you want when the number is final.

What "Keeping the Data" Means in Practice

When you paste values over a formula, Excel writes the calculated result into the cell as a literal number, text string or date. The formula is gone. The cell is now indistinguishable from one you typed by hand.

Number formatting is preserved, because Paste Values pastes the value into the existing cell and leaves the cell's format alone. If you want formatting carried along too, use Values and Number Formatting instead, which is one option further down the same menu.

How to Remove Formulas but Keep Values, Step by Step

There are four routes worth knowing. The first covers almost every case; the others solve specific problems the first one cannot.

Method 1: Copy and Paste Values Over Itself

This is the default method and the one to reach for unless you have a reason not to.

1. Select the cells containing the formulas. To take a whole column of them, click the first cell and press Ctrl+Shift+Down.
2. Press Ctrl+C. A marching-ants border appears around the selection.
3. Without moving the selection, press Ctrl+Alt+V, then V, then Enter. In Microsoft 365, Ctrl+Shift+V does the same in one stroke.
4. Click any cell in the range and check the formula bar. It should show a number, not a formula.

The mouse route is the same operation: Home tab, Clipboard group, click the arrow under Paste, and choose Paste Values — the clipboard icon with 123 on it.

Method 2: Right-Click Drag, the Fastest Route of All

This one is almost unknown and it is the quickest way to convert a range in place.

1. Select the range of formulas.
2. Hover over the edge of the selection until the cursor becomes a four-headed move arrow.
3. Hold the right mouse button, drag the selection slightly away, then drag it straight back to where it started, and release.
4. A context menu appears. Choose Copy Here as Values Only.

No clipboard, no dialog, no shortcut to remember. It is the same conversion in one gesture.

Reporting that breaks every time someone edits a source tab is a symptom, not the disease. Request a free audit and we will map which of your dashboards depend on fragile links.

Method 3: F9 to Freeze Part of a Formula

Sometimes you do not want to flatten the whole formula, only one piece of it. A long formula might contain a lookup that is now settled while the arithmetic around it should stay live.

In the formula bar, select just that fragment of the formula and press F9. Excel replaces the selected fragment with its current result, in place, leaving the rest of the formula intact. Press Enter to commit, or Escape to back out without changing anything.

Be careful with this one. F9 on a selection of nothing — or with the whole formula highlighted — converts the entire formula to a value, which is the same as Method 1 but applied to a single cell.

Method 4: Flattening a Whole Sheet or Workbook

To convert everything on a sheet, select all cells with Ctrl+A, then run Method 1. Excel handles tens of thousands of cells without complaint, though very large ranges can take a few seconds.

If you need to do this to many sheets, do them one at a time rather than grouping sheets. Paste Special behaves unpredictably across a multi-sheet selection, and the failure mode is silent.

For a file you intend to hand over as a finished artefact, the safest sequence is: flatten the formulas, then check for remaining external links under Data, Queries and Connections, Edit Links. Flattening cells does not remove a link definition that nothing references any more.

When You Should Convert Formulas to Values

Not every formula should be frozen. The useful question is whether the number is still supposed to change.

Before you send a file outside your organisation. Formulas expose your working: lookup tables, hidden helper columns, sheet names, sometimes the path to a network drive. Values expose only the numbers you meant to share.

When the source data is about to disappear. If you are deleting the raw sheet, archiving last quarter's export, or breaking a connection to a database, flatten first. Otherwise you are left with a grid of #REF! where the report used to be.

When the file has become slow. Volatile functions and large array formulas recalculate constantly. Freezing the parts of a model that are historical and settled often takes a sluggish workbook back to instant, because Excel stops recomputing results that cannot change.

When you need a true snapshot. A month-end figure should read the same in a year's time as it did on the day it was signed off. A formula does not guarantee that; a value does.

When you are about to sort or restructure. Relative references follow cells around in ways that are hard to predict once rows move. Flattening first removes that whole class of surprise.

A number that should have stayed live, frozen by mistake, is the quietest reporting bug there is. Book a free audit and we will check whether your key metrics are being measured or merely remembered.

Limitations and Things That Break

Paste Values is simple, but there are four situations where it does not behave the way people expect.

It is irreversible once you save. Ctrl+Z works within the session, but the formulas are not recoverable from the file afterwards. Save a copy first on anything that matters. This is the single most common regret with this operation.

Array formulas must be selected whole. A legacy array formula entered with Ctrl+Shift+Enter occupies a block of cells that Excel treats as one unit, and you cannot convert part of it. Select a cell inside it, then use Home, Find & Select, Go To, Special, Current array to select the full block before copying.

Dynamic array spill ranges behave differently. A modern function like SORT or FILTER lives in one cell and spills its results into neighbours. Copying the spill range and pasting values works, but you must copy the whole spilled output rather than the single source cell, or you will convert only the top-left result.

Errors are frozen as errors. If a cell currently shows #N/A or #DIV/0!, Paste Values writes that error text into the cell permanently. Fix errors before flattening, because afterwards there is no formula left to debug.

One more worth noting: Paste Values does not touch conditional formatting, data validation or comments. Those are cell properties rather than cell contents, so they survive untouched — which means a flattened file can still carry validation rules pointing at ranges you have since deleted.

Want to know whether your numbers are telling the truth? A free audit checks your tracking and reporting end to end, from data collection to the figure in the summary row.

Paste Values vs the Alternatives

Several other approaches get suggested for this, and most of them are worse. It is worth knowing why.

Retyping the numbers by hand is the method most people default to, and it is the only one that introduces transcription errors. On more than about ten cells it is also slower than the shortcut. There is no case where it wins.

Copying into Notepad and back does flatten the values, but it destroys number formatting, converts dates into whatever text form they were displaying, and can turn long numbers into scientific notation. Use it only when you deliberately want everything reduced to plain text.

Saving as CSV flattens every formula at once, but it also discards every sheet but the first, all formatting and any charts. It converts the file, not a selection.

Power Query is the right tool when the flattening is one step in a repeatable pipeline — load the data, transform it, output a static table that refreshes on demand. It is considerable overkill for freezing one column, and it adds a query to the workbook that someone will later have to understand.

Converting the range to an Excel Table first does not help at all here. Tables change how references are written, not whether a cell holds a formula. If you are weighing up ranges against Tables for a different reason, our guide to moving data from columns to rows in Excel covers where Paste Special, functions and Power Query each fit.

So the hierarchy is straightforward. Paste Values for a selection, the right-drag menu when you want it in one gesture, F9 for part of a formula, and Power Query only when the operation needs to repeat.

Final Thoughts

Removing a formula while keeping its result is a two-second operation that sits behind a menu almost nobody opens. Select, copy, Ctrl+Alt+V, V, Enter. That is the whole technique.

What makes it worth deliberate attention is not the keystrokes but the judgement around them. Every formula you freeze is a number you have decided is finished, and every formula you leave live is a number you are promising to keep accurate. Flattening a report that should have stayed current is just as much a mistake as shipping a file full of #REF! errors.

The practical habit: save a copy before you flatten anything, fix errors before freezing them, and ask of each column whether it is a record or a calculation. Records should be values. Calculations should stay formulas.

Do that consistently and the spreadsheets you hand over stop being fragile, without becoming stale.