How To Remove Formula in Excel (While Keeping the Data)

Last updated: August 18, 2026.

Formulas are what make a spreadsheet useful, right up until the moment they are not. A workbook you are about to email out should show numbers, not the logic behind them. A file that reopens with 40,000 volatile formulas is slow to the point of unusable. A report that recalculates every time someone touches a cell is a report nobody can trust as a snapshot.

In all three cases the fix is the same: strip the formulas and leave the results. This guide covers the fast method, the shortcut most people never learn, how to do it across an entire workbook, and the two situations where it goes wrong (tables and array formulas).

What is the difference between deleting a formula and converting it to a value?

These sound the same and they are not, which is why so many people wipe out data they meant to keep.

• Deleting a formula means selecting the cell and pressing Delete. The formula goes, and so does the result. The cell is now empty.
• Converting a formula to a value means replacing the formula with the number it currently returns. The cell still shows 4,182. It just no longer knows how it got there.

Rule to remember: Delete removes the answer. Paste as values keeps the answer and removes the question. Almost everyone searching for how to remove a formula in Excel actually wants the second one.

The rest of this guide is about the second one. Column E below is the live example: column C is sales, column D is the previous balance, and column E is a formula adding the two. That formula in E is what we are going to remove.

How do you remove a formula in Excel but keep the value?

Click into any cell in column E and the formula bar shows the formula, not the number. That is the thing we are removing.

Step 1. Select the cells that contain the formulas. Drag across them, or click the first cell and press Ctrl+Shift+Down to grab the rest of the column.

Step 2. Copy. Press Ctrl+C, or use the Copy icon in the Clipboard group on the Home tab.

You will see a marching dotted border around the selection. That border is Excel telling you the range is on the clipboard.

Step 3. Right-click the first cell of the selection and, under Paste Options, choose the clipboard icon marked 123 (Values). Excel pastes the results straight over the formulas.

Pasting on top of the range you copied is the whole trick. Most tutorials tell you to paste somewhere else, which leaves you with two copies to clean up.

The full Paste Special route

The right-click Paste Options menu is a shortcut to a bigger dialog. Use the full dialog when you need more control than plain Values.

Step One: Select the cells in the column you want to clear formulas from.

Step Two: Press Ctrl+C, or right-click and choose Copy. The dotted frame appears around the highlighted cells.

Step Three: Open Paste Special. You can right-click and pick Paste Special from the menu,

or click the arrow under the Paste icon in the Clipboard group on the Home tab.

Step Four: Choose Paste Special, or press Ctrl+Alt+V.

The Paste Special dialog opens.

Step Five: Select Values.

Step Six: Click OK.

Click into any of the cells now and the formula bar shows a number instead of a formula.

That is the confirmation you want. If the formula bar still shows an equals sign, the paste did not land where you thought it did.

Which Paste Special option should you use?

Plain Values is the right answer maybe 70 percent of the time. The other 30 percent is why this table exists.

Rule to remember: If money, dates or percentages are involved, use Values and Number Formats. Plain Values turns a date into a five-digit serial number and there is no undo once you have saved and closed.

Warning about lost formatting

Pasting plain Values over a formatted range strips fills, borders and fonts. If that happens, you can re-apply formatting manually or start over with a fresh copy. See How to Remove Table Formatting in Excel if the formatting itself is what you are trying to get rid of.

What is the keyboard shortcut to remove a formula and keep the value?

There is no single key that does it, which is why this question gets asked so often. There are three sequences, and the one you want depends on your Excel version.

Rule to remember: Alt, E, S, V, Enter is the sequence to learn. It has worked in every version of Excel since the 1990s and it does not care what ribbon layout you are on.

How do you remove a formula in Excel without copy and paste?

There is a trick that skips the clipboard entirely, and almost nobody knows it because it involves dragging with the right mouse button.

Select the column you want to flatten. Click the first cell and press Ctrl+Shift+Down to select to the bottom of the data.

Hover over the border of the selection until the cursor turns into the four-headed move arrow. Now hold down the right mouse button, drag the selection one column over, and drag it straight back to where it started.

Release the right button and a context menu appears. Choose Copy Here as Values Only. The formulas are gone and the numbers stay, with no clipboard involved and no marching ants to cancel.

Two cautions. This method is fast enough that it is easy to drop the range in the wrong place, so watch the grey outline before you release. And if you drag with the left button instead of the right, you will move the cells rather than get the menu.

How do you remove all formulas from an entire sheet or workbook at once?

Doing this column by column on a 30-tab model is not realistic. There are two approaches depending on whether the whole sheet needs flattening or only the formula cells.

The whole sheet

1. Click the triangle in the top-left corner of the grid, or press Ctrl+A twice, to select every cell.
2. Press Ctrl+C.
3. Press Ctrl+Alt+V, then R, then Enter for Values and Number Formats.

Every formula on the sheet is now a static value.

The whole workbook

Right-click any sheet tab and choose Select All Sheets. The title bar will say [Group]. Sheet grouping does not extend to Paste Special, so you still have to run the paste on each tab, but grouping does let you select the same range across every tab at once and check what you are dealing with. When you are done, right-click a tab and choose Ungroup Sheets. Forgetting to ungroup is how people accidentally type the same value into 30 tabs.

Only the formula cells, leaving typed values alone

This is the surgical version, and it is the one to use on a real model.

1. Select the range, or the whole sheet.
2. Press F5, then click Special. Or press Ctrl+G, then Special.
3. Choose Formulas and click OK. Excel now has only the formula cells selected.
4. Press Ctrl+C, then Ctrl+Alt+V, V, Enter.

One warning: Go To Special selects a non-contiguous set of cells, and Excel will refuse to paste values into a multiple-area selection if the areas are not the same shape. If you get That command cannot be used on multiple selections, do it one column at a time instead.

How do you remove formulas but keep the formatting?

Two reliable routes:

• Values and Number Formats. Ctrl+C, then Ctrl+Alt+V, then R, then Enter. Keeps currency symbols, decimal places, date formats and percentages. Does not keep fills, borders or fonts, but if you are pasting on top of the original range those were never removed in the first place.
• The right-drag menu. Copy Here as Values Only, described above, leaves the destination cell formatting completely untouched because it only writes the value.

Rule to remember: Formatting only disappears when you paste into a different range than the one you copied. Paste on top of yourself and there is nothing to lose.

Conditional formatting is the exception. It survives a value paste, because conditional formatting is attached to the cell, not to the formula. If your conditional formatting rules referenced the formula results, they will keep evaluating against the new static numbers, which is usually what you want.

How do you remove formulas from an Excel table?

Excel tables (Insert > Table, or Ctrl+T) fight back, because a table has calculated columns. Type a formula into one cell of a table column and Excel fills the entire column and remembers the rule.

Paste values over a calculated column and Excel will often helpfully re-apply the formula to the whole column a moment later. Here is the sequence that actually sticks:

1. Select the body of the calculated column (click the top of the column once to select the data, not the header).
2. Press Ctrl+C, then Ctrl+Alt+V, V, Enter.
3. If the AutoCorrect lightning-bolt icon appears offering to restore the calculated column, click it and choose Stop Automatically Creating Calculated Columns.

If you no longer need the table behaviour at all, click inside the table, go to Table Design and choose Convert to Range. The structured references such as [@Sales] turn into ordinary cell references, and the calculated column rule disappears. Full walkthrough here: How To Remove a Table in Excel.

How do you remove an array formula and fix “you cannot change part of an array”?

If Excel throws You cannot change part of an array, you have hit a legacy CSE array formula. These were entered with Ctrl+Shift+Enter and they occupy a block of cells as a single unit. Excel will not let you edit, delete or paste over one cell of that block.

The fix is to work on the whole array at once:

1. Click any cell inside the array.
2. Press Ctrl+/ (Ctrl and forward slash) to select the entire array range. On some keyboard layouts this is Ctrl+Shift+Alt+Right.
3. Now copy that whole selection and paste values over it, or press Delete to remove it entirely.

Dynamic arrays in Microsoft 365 behave differently. A spilled range has one master formula in the top-left cell and greyed-out results below it. To flatten a spilled range, select the whole spill (click the top-left cell and press Ctrl+A, or reference it with the # operator), copy, and paste values. Microsoft covers the difference between legacy CSE arrays and dynamic arrays in its guidelines and examples of array formulas.

Rule to remember: An array formula is one object, not a block of cells. You cannot remove part of it any more than you can delete half a word. Select the whole thing or nothing.

If you want the background on how these behave before you flatten them, see Excel Array Formulas: What Is It, And How to Use It.

What breaks when you remove formulas?

This is the part most guides skip, and it is the part that costs people an afternoon.

Dependent formulas elsewhere still point at the cells

Converting E2:E500 to values does not break anything by itself. The cells still hold numbers and anything referencing them still works. The problem starts if you delete rows or columns afterwards, because then every formula pointing at them returns #REF!. That error means the reference no longer exists and Excel cannot guess what you meant. See What Does #REF Mean in Excel for the repair options.

Anything downstream freezes at today's number

If cell H10 was =E10*1.2 and you flatten E10, H10 keeps recalculating but E10 never changes again. The model looks live and is not. Flatten from the bottom of the dependency chain upward, or flatten everything in one pass.

External workbook links

Formulas that pull from another file are a separate case. Pasting values over them works, but the cleaner route is Data > Queries & Connections > Edit Links > Break Link. That converts every linked formula in the workbook to its current value in one action, and it also removes the "this workbook contains links" prompt that greets whoever opens the file next.

Named ranges and defined names survive

Removing a formula does not remove the names it referenced. They sit in Name Manager pointing at cells that now hold static values. Harmless, but worth a cleanup pass if you are handing the file over.

Rule to remember: Save a copy before you flatten anything. Converting formulas to values is a one-way door, and Ctrl+Z stops helping the moment you close the file.

If a specific lookup is what you are trying to kill rather than the whole sheet, we have a dedicated walkthrough: How To Remove a VLOOKUP Formula In Excel. And if the formulas are throwing errors rather than results, Everything You Need to Know About Excel Formula Errors covers what each one means before you flatten it.

How do you delete the data but keep the formula?

The reverse problem, and a common one when you are resetting a template for next month.

1. Select the range that holds both inputs and formulas.
2. Press F5, click Special, choose Constants, and click OK. Excel now has only the hard-typed values selected, and none of the formula cells.
3. Press Delete.

Every typed input is cleared and every formula is untouched, ready for new data. This is how you build a reusable template out of a completed one in about four seconds.

How do you do the same thing in Google Sheets?

Google Sheets uses Ctrl+Shift+V for paste values only, with no dialog and no options. It is faster but less flexible, and there is no equivalent of Values and Number Formats. We cover the differences here: How to Remove Formula And Keep Value in Google Sheets.

Working on messy imported data rather than calculations? The related cleanup job is trimming unwanted characters off the front of every cell, which we cover in How To Remove Characters From the Left in Excel.

Frequently asked questions

How do I remove a formula in Excel and keep the value without copy and paste?

Select the range, hover over its border until you get the move cursor, then hold the right mouse button and drag the selection away and back again. Release and choose Copy Here as Values Only. No clipboard is used.

What is the shortcut to remove formulas and keep values?

Ctrl+C then Ctrl+Alt+V, V, Enter on Windows. In Microsoft 365 you can use Ctrl+C then Ctrl+Shift+V. On Mac it is Cmd+C then Ctrl+Cmd+V, V, Enter.

Why does Excel say I cannot change part of an array?

The cell belongs to a legacy array formula entered with Ctrl+Shift+Enter, which spans several cells as one object. Click inside it, press Ctrl+/ to select the full array, then paste values or delete the whole selection.

Does removing a formula reduce the Excel file size?

Yes, usually noticeably. Formulas store both the expression and the cached result, and volatile functions such as OFFSET, INDIRECT, TODAY and RAND force a full recalculation chain on every edit. Flattening a large model to values often cuts both file size and open time.

How do I remove formulas from every sheet in a workbook?

There is no single command for it. Go tab by tab: Ctrl+A twice to select the sheet, Ctrl+C, then Ctrl+Alt+V, R, Enter. If the formulas pull from other files, use Data > Edit Links > Break Link instead, which handles the entire workbook in one action.

Will I lose my formatting when I paste values?

Only if you paste into a different range than the one you copied. Pasting on top of the original range leaves fills, borders and fonts alone. If you are pasting elsewhere, use Values and Number Formats to keep currency and date formats.

How do I clear the data but keep the formulas for next month?

Select the range, press F5, click Special, choose Constants, click OK, then press Delete. Only the hard-typed inputs are removed. Every formula stays in place.

The wrap up

Removing formulas in Excel comes down to one decision: are you keeping the answer or throwing it away? Paste Special keeps it, Delete does not. Learn Alt, E, S, V, Enter, use Values and Number Formats whenever money or dates are on the sheet, and save a copy before you flatten anything you cannot rebuild.

If you would rather not build the model in the first place, the ready-made workbooks at Simple Sheets come structured, formatted and documented. See The Benefits of Excel Templates.