How to Convert Formulas to Values in Excel

The "freeze the model before sharing" problem

You built a workbook that depends on lookups into source tabs. The audit team is getting view-only access on Monday. If they accidentally delete a row in a source tab, every dependent formula breaks and the workbook starts showing #REF! errors mid-meeting. The fix: convert the formulas to their current values before handing over the file.

Excel calls this "Paste Special > Values". Doing it once is fine. Doing it across an entire workbook is tedious. Here's the spectrum of approaches.

TL;DR — Key takeaways

• The standard fix is Copy > Paste Special > Values on a contiguous range. It works, one block at a time.
• The keyboard shortcut Ctrl + Alt + V > V > Enter speeds it up but you still process one block at a time.
• A free VBA macro processes any selection (one block, many blocks, or the entire used range) in one pass and skips array formulas that can't be assigned per-cell.
• Always work on a copy. Once formulas are gone, getting them back means going to a backup or rebuilding.

Method 1: Paste Special > Values (the classic)

The standard Excel workflow:

1. Select the range with formulas.
2. Ctrl + C to copy.
3. With the same range still selected, right-click > Paste Special > Values.
4. Click OK.

Formulas get replaced with their current evaluated values. Display formatting (date, currency, percentage) is preserved because Paste Special > Values changes the cell content, not the format.

The catch: for a 14-tab workbook with formulas scattered everywhere, you do this 14 times (once per tab) or more (if formulas are in multiple disjoint blocks per tab). Each paste is fast, but the navigation and click sequence adds up.

Method 2: The keyboard shortcut

Same operation, faster keystrokes:

1. Select range. Ctrl + C.
2. Same range selected. Ctrl + Alt + V opens Paste Special.
3. Press V to pick Values.
4. Press Enter.

About 4 seconds per block once you've muscle-memoried the sequence. Works the same as the menu version.

Method 3: The "value = value" formula bar trick

A lesser-known trick for a single column or row:

1. Select the range.
2. Click in the formula bar.
3. Press F9 (Windows) or Fn + F9 (Mac).
4. Press Enter.

Excel evaluates the formula in the formula bar and writes the result back. This works for an array of cells when you've selected and entered the formula bar from the array context.

The catch: F9-in-formula-bar mostly works for a single formula at a time. For ranges, behavior varies by Excel version. Don't rely on this method for important conversions.

Method 4: The free VBA macro (whole-selection, array-aware)

Download Convert Formulas to Values. Free .xlsm with one macro.

Steps:

1. Select the range, the column, the block, or the entire used range (Ctrl+A).
2. Press Alt + F8, pick ConvertFormulasToValues, click Run.
3. The macro replaces every formula cell with its evaluated value. The popup reports converted count and skipped count.

The macro handles array formulas gracefully: cells that can't be assigned per-cell (because they're part of a multi-cell array formula) are skipped and counted in the "skipped" total. Modern dynamic-array formulas (XLOOKUP, FILTER, SORT) freeze cleanly cell-by-cell.

A common scenario: pre-share workbook freeze

You're handing off a quarterly model to the audit team. The model has 11 source tabs and 3 summary tabs. Summary tabs use INDIRECT, OFFSET, and VLOOKUP formulas that pull from the source tabs.

With the macro:

1. Save a copy of the workbook with _AUDIT in the filename.
2. Open the audit copy.
3. On each summary tab: Ctrl+A to select the used range, run the macro.
4. Formulas are replaced with values; display format is preserved.
5. Save and send.

About 90 seconds for 3 summary tabs versus 20 minutes of per-block Paste Special navigation.

What about cell formatting?

The macro replaces the cell's value, not its format. So a formula returning a date stays formatted as a date. Currency stays currency. Percentage stays percentage. The display is identical before and after; only the underlying formula is gone.

What about array formulas?

Single-cell array formulas ({=...}) freeze cleanly. Multi-cell array formulas where assigning to one cell at a time would error are skipped. To freeze a multi-cell array formula, select the entire array first (Excel highlights the full range when you click any cell of it), then either copy/paste-values manually or replace the array with a static range first.

What about formulas in conditional formatting rules?

Conditional Formatting rules use formulas of their own (in the rule definition, not the cells). Those are not touched by Paste Special > Values or by the macro. To "freeze" a CF rule's effect, you'd have to delete the rule and apply the equivalent static formatting manually.

Frequently asked questions

How do I find which cells have formulas before freezing them?

Use Highlight All Formula Cells. Select your range, run the macro, every formula cell turns light yellow. Audit visually, decide whether to freeze.

Will it touch hardcoded values?

No. The macro skips any cell where HasFormula is False. So a cell containing the literal text =A1+B1 (typed as text, not as a formula) is left alone, and a cell with a typed-in number stays untouched.

Can I freeze formulas in only specific columns?

Yes, just select those columns before running. The macro processes only your selection.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes every conversion in one step. After that, formula cells are restored. As always, work on a copy of the file before doing a bulk freeze.

What's the difference between this and "lock cells"?

Locking cells (Format Cells > Protection > Locked) plus protecting the sheet prevents editing but doesn't change the formulas. The cells still calculate and depend on their source ranges. Converting to values actually replaces the formulas with static numbers, so deleting source data has no effect on the values.

What to do next

Before freezing formulas, it's good practice to audit which cells will be affected. Run Highlight All Formula Cells first to see them. After freezing, if you also want to add a totals row to the now-static data, Add Totals Row to Numeric Columns writes SUM formulas you may want to leave live (or freeze afterward).