How to Replace an Excel Formula with Its Calculated Value
Question details
The user needs a way to permanently convert formula results into static values within the same cells in Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user wants to freeze formula results to prevent recalculations, share clean data, or simplify a workbook, but finds no standard worksheet function for automated permanent conversion.
- Observed behavior
- Excel dynamically updates formulas by default. There is no built-in worksheet function that permanently overwrites the formula with its result; it requires manual copy-pasting or VBA scripts.
Before converting bulk formulas to static values or running automated VBA scripts, duplicate your worksheet or workbook, as replacing a formula with a static value cannot easily be reversed once saved.
Use Paste Special to Manually Convert Formulas to Values
The simplest and most reliable method for a one-time conversion of specific formula cells to static values.
By default, copying and pasting a cell in Excel duplicates the underlying formula. By utilizing the 'Paste Special' menu, you can force Excel to paste only the final calculated result, entirely stripping out the formula.
Highlight the specific cell or range of cells containing the formulas you want to convert.
Right-click the highlighted area and select 'Copy', or press 'Ctrl + C' on your keyboard.
Without changing your selection, right-click the same highlighted area, hover over 'Paste Special', and choose 'Values' (often indicated by an icon with '123'). Alternatively, you can use the keyboard shortcut 'Alt > E > S > V > Enter'.
Automate the Conversion Using a VBA Macro
Use an event-triggered VBA macro to automatically replace formulas with their results as soon as they are entered into a specific column or range.
Easily Convert Formulas to Values in WPS Spreadsheet
WPS Office provides an intuitive and highly compatible spreadsheet environment. You can use standard Paste Special features or implement advanced VBA macros seamlessly to manage your formulas and data.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing the formulas.
- 2. Highlight and Copy: Select the cells containing your formulas, right-click, and choose 'Copy'.
- 3. Paste Special as Values: Right-click over the same selection, select 'Paste Special', choose 'Values', and click 'OK'.
Frequently Asked Questions
Can I easily undo a formula-to-value conversion?
If you just performed the Paste Special operation, you can immediately press Ctrl+Z to undo it and restore your formulas. However, once you save and close the workbook, the formulas are permanently replaced by the static values and cannot be restored.
Is there a keyboard shortcut for pasting as values?
Yes. After copying the selected cells (Ctrl+C), you can press Alt, then E, then S, then V, and finally hit Enter. Alternatively, you can press the Menu key on your keyboard followed by V.
Why use 'Value2' instead of 'Value' in the VBA code?
Using '.Value2' is generally a best practice for raw data transfer in VBA because it doesn't try to check for Date or Currency formatting like '.Value' does. It simply passes the underlying unformatted data, making the script slightly faster and less prone to formatting errors.
How do I apply the VBA macro to multiple columns instead of just one?
In the VBA script provided, you can modify the 'Set myCol = Columns(3)' line to target multiple columns. For example, 'Set myCol = Range("C:C, E:E")' will apply the value-replacement automation to both columns C and E.
