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:
- 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.
- 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.
- In the editor, choose Insert > Module, then paste the code into the new module.
- Return to Excel, keep the intended cells selected, and run the macro with Alt+F8. Choose
ConvertSelectionToValues, select Run, and confirm the prompt. - 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/Aremains 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
- Excel Not Equal: The <> Operator Explained
- Excel Arrow Keys Not Moving Between Cells? Quick Fixes
- Excel Not Responding? Causes, Fixes, and Recovery Steps
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Macro Recorder for Games: How It Works and What to KnowA macro recorder for games captures a sequence of keyboard or mouse inputs and replays them, often with…
- VBA Turn Off AutoFilter in Excel Without Losing DataTo turn off a worksheet AutoFilter in VBA, first show all rows if a filter is active, then…
- VBA InputBox: How to Collect Multiple InputsVBA’s standard InputBox collects one response per prompt; it does not display several separate fields in a single…
- Excel Macro to Pull Data From Other Worksheets SafelyYes. In Excel for Microsoft 365 on Windows, a VBA macro can read ranges from other worksheets and…
- Excel VBA Macro to Send a Worksheet File via TeamsYes—but Excel VBA has no built-in command that sends a worksheet directly to Microsoft Teams. A practical Windows…
- VBA Sort Key1: How to Sort a Range by One Column in ExcelIn Excel VBA, Key1 is the first column or row Excel uses to decide the sort order when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.