Formula Errors

How to Remove a Formula in Excel Without Deleting Values

Emma Brown Oct 10, 2026 869 views

Question details

The user wants to remove an AutoSum or array formula from a spreadsheet while retaining the calculated results as static numbers or text.

Product: Excel
Device & OS: not provided
Scenario: Finalizing a data sheet where dynamic calculations are no longer needed, and the user needs to lock in the visible results before clearing the underlying formulas.
Observed behavior: Deleting the formula normally erases the cell's calculated value, forcing the user to find a way to preserve the displayed data.

Before you start

Before replacing your formulas with static values, ensure you no longer need the data to update automatically, as this process cannot be reversed once the file is saved.

Solution 1Recommended

Use Paste Special to Replace Formulas with Values

The most effective method to remove formulas but keep the data is to copy the cells and paste them back over themselves as static values.

This method works for standard formulas, AutoSum results, and array formulas. By using the Paste Special feature, Excel overwrites the dynamic formula with the exact value currently displayed in the cell.

Select the formula cells

Highlight the range of cells containing the formulas you want to remove.

Copy the selection

Press Ctrl+C on your keyboard, or right-click the selected cells and choose 'Copy'.

Open Paste Special

Without deselecting the cells, right-click anywhere inside the highlighted range and select 'Paste Special' from the context menu.

Paste as Values

In the Paste Special dialog box, select the 'Values' option and click 'OK'. The formulas are now permanently replaced by their calculated results.

Solution 2

Select and Clear Entire Array Formulas

If you need to delete an array formula entirely (including its values), you must select the full array range first to avoid Excel errors.

WPS Spreadsheet

Easily Manage Formulas and Static Values with WPS Office

WPS Spreadsheet offers a highly intuitive and familiar interface for managing your data. Converting complex formulas into static values takes just a few clicks, ensuring your data is ready for sharing or reporting without calculation errors.

1. Select the data: Open your spreadsheet in WPS Office and highlight the cells containing the formulas you want to convert.
2. Copy the cells: Press Ctrl+C to copy the selected data to your clipboard.
3. Paste as Values: Right-click the highlighted area and choose 'Paste as Values' (often represented by a clipboard icon with '123') directly from the quick menu.

100% compatible with Microsoft Excel (.xlsx, .xls) files and formulasQuick access to 'Paste as Values' from the intuitive right-click menuLightweight software that processes large datasets quickly without laggingFree to download and use for your daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Can I convert formulas to values across an entire worksheet at once?

Yes. Click the 'Select All' button (the triangle in the top-left corner of the grid between row 1 and column A) or press Ctrl+A. Press Ctrl+C to copy, then right-click and choose Paste Special > Values to convert the entire sheet.

Why do I get a 'You cannot change part of an array' error?

This error occurs because array formulas calculate multiple results across a locked block of cells. You cannot delete or overwrite just one cell in this block. You must select the entire array range using 'Go To Special' > 'Current array' before deleting or pasting values.

Is there a keyboard shortcut to paste as values?

Yes. After copying your cells with Ctrl+C, you can press Alt + E, then S, then V, and press Enter to quickly apply the Paste Special > Values command without using your mouse.

Excel & Data Problems