Excel Value Macro: Convert Formulas to Values

VBA & macros · Updated November 18, 2026

To convert selected Excel formulas into their current results with a VBA macro, assign a range’s Value2 property to itself: target.Value2 = target.Value2. The cells keep their displayed results and formatting, but the formulas are replaced, so save a copy first—macros cannot be undone with Ctrl+Z.

What an Excel value macro does

In Excel VBA, Value and Value2 are properties that read or set the contents of a cell or range. A value-conversion macro reads the selected cells’ current contents and writes those contents back. When a cell contains a formula, the value read is its calculated result, so writing that result back replaces the formula.

For example, if C2 contains =A2*B2 and currently displays 42, converting C2 to a value leaves the number 42 in the cell. It no longer updates if A2 or B2 changes. Text, numbers, dates represented by serial numbers, and existing errors are also left as cell contents; the macro is not a formula evaluator or an error-cleanup tool.

In this context, “value macro” usually means a macro that freezes formula results. It can also refer more generally to VBA code that reads or writes the Value property. The example below is specifically for replacing formulas in a selected, continuous range.

A safe macro for the selected cells

This macro checks that a worksheet range is selected, rejects a selection made up of separate areas, and asks for confirmation. It uses Value2, which transfers cell values without the extra date and currency conversions associated with VBA’s Value property.

Sub ConvertSelectionToValues()
    Dim target As Range

    ' Require a worksheet cell selection.
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select worksheet cells first.", vbExclamation
        Exit Sub
    End If

    Set target = Selection

    ' Keep the operation to one continuous block.
    If target.Areas.Count > 1 Then
        MsgBox "Select one continuous range of cells.", vbExclamation
        Exit Sub
    End If

    ' Confirm before replacing formulas permanently.
    If MsgBox("Replace formulas with their current results?", _
        vbQuestion + vbYesNo, "Convert to Values") <> vbYes Then Exit Sub

    target.Value2 = target.Value2
End Sub

The macro changes the cell contents, not their number formats, fill colors, borders, or other ordinary formatting. A cell formatted as currency, for example, can continue to display a currency symbol after its formula is replaced. Cells that already contain fixed values remain those values.

How to add and run the macro

Before running VBA, save a separate copy of the workbook. Then use these steps in Excel for Microsoft 365 on Windows:

  1. Open the workbook and select the continuous block of cells you want to convert. Avoid selecting an entire worksheet column unless you genuinely intend to process every cell in it.
  2. Open the Visual Basic Editor with Alt+F11, or choose Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
  3. In the editor, choose Insert > Module, then paste the code into the new module.
  4. Return to Excel, keep the intended cells selected, and run the macro with Alt+F8. Choose ConvertSelectionToValues, select Run, and confirm the prompt.
  5. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code in that file.

On a Mac, the same VBA code works in desktop Excel. Open the editor from Developer > Visual Basic; use Excel > Settings > Ribbon & Toolbar to show the Developer tab if needed. To run a macro, use Tools > Macro > Macros. Keyboard shortcuts can depend on the Mac keyboard and function-key settings, so the menu commands are a reliable alternative.

Check calculation results before freezing them

The macro stores the result Excel currently has for each formula. If the workbook uses manual calculation, that result may not reflect the latest input changes. Before converting, choose Formulas > Calculate Now, or press F9 on Windows, and check the cells. Recalculate first if the formulas depend on other workbooks, refreshed data, or volatile functions such as NOW and RAND.

For instance, suppose A2 contains 6, B2 contains 7, and C2 contains =A2*B2. After Excel calculates, C2 displays 42. The macro leaves 42 in C2; later changing B2 to 8 will not change C2. That is useful for a finalized report, but harmful if you still need the worksheet to respond to new inputs.

What the macro changes—and what it does not

Converting formulas to values is a one-way change unless you restore the formulas from a backup or recreate them. It can reduce a workbook’s dependence on calculation and prevent a report’s results from changing when source cells are edited. It does not remove formatting, delete the worksheet, or automatically turn the results into plain text.

  • Formulas: Replaced by the result Excel has calculated.
  • Formatting: Normally retained, including number formats and cell colors.
  • Cell references: No longer update through the removed formula.
  • Errors: A formula result such as #N/A remains an error value; the macro does not fix it.
  • Links and data: The macro does not refresh external connections or verify whether the underlying information is current.

Be especially careful with dynamic-array formulas, such as formulas that spill results into neighboring cells, and with calculated columns in Excel tables. Spill output cells are controlled by the formula in the anchor cell, so trying to overwrite part of a spill can fail. Review the range and test on a copy before using a macro on these structures.

Common problems and safer handling

If Excel reports that it cannot change part of an array, the selection may include only part of an array formula or a spilled result. Cancel, identify the formula’s full output range, and decide whether replacing that formula is appropriate. Some protected worksheets also block edits; unprotect the sheet only if you are authorized to do so.

If the macro does nothing useful, check that you selected the intended cells and approved the confirmation prompt. If Excel blocks the macro, review the file’s trust and macro-security settings rather than enabling all macros indiscriminately. Only run VBA from a source you trust, because macros can make extensive changes to a workbook.

The code deliberately refuses discontiguous selections—for example, cells selected by holding Ctrl—because handling separate areas can be less predictable. Convert one continuous block at a time. For large or important workbooks, test the operation on a duplicate and compare key totals or sample formulas afterward.

Macro or Paste Values?

A macro is useful when you repeat the same conversion, need a confirmation step, or want a reusable tool for a defined workflow. For a one-time change, Excel’s built-in Paste Values command is simpler and does not require VBA or a macro-enabled file.

To use the built-in method, copy the formula cells, then choose Home > Paste > Paste Values. This replaces formulas in the copied cells with their current results. On Windows, Ctrl+C followed by Ctrl+Alt+V opens Paste Special; choose Values and confirm. On Mac, use the Home tab’s Paste menu and choose Values, since keyboard shortcuts can differ by keyboard and Excel setup.

Paste Values is also a sensible choice when you want to avoid adding code to a workbook. The macro becomes worthwhile when the task is frequent, repeatable, and clearly scoped. Neither method should be used until you are sure the formulas are no longer needed.

Frequently Asked Questions

Does an Excel value macro remove formatting?
No. The example replaces cell contents but normally leaves formatting such as number formats, font, fill, and borders intact.

Can I undo a macro with Ctrl+Z?
No. Excel clears its normal undo history when VBA changes worksheet cells. Save a separate copy before running the macro so you can recover the original formulas if needed.

Will the converted cells update when source data changes?
No. A converted formula cell contains a fixed result rather than a formula linked to its inputs. Run or recreate the original formula again if you need ongoing updates.

Can I convert only some formulas in a worksheet?
Yes. Select the continuous range you want to change before running the macro. It processes the selection, not the entire workbook; make sure the selected range does not include cells you need to remain formula-driven.

Can I use this macro in Excel for the web?
Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Open the workbook in desktop Excel to run this VBA code, or use Paste Values in the web app.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.