Convert Formulas to Values

What it does

Excel's built-in way to freeze formulas is Copy, then Paste Special, Values. It works, but only on a single contiguous range, and it overwrites the destination so a slip of the keyboard can clobber the wrong cells. The macro skips the copy-paste shuffle entirely. Select any range (one block or many), run it, every formula cell is replaced in place with its current value while the display format (dates, currency, percentages) stays intact. Hardcoded values in the same selection are untouched. The popup tells you how many cells got frozen.

Real-world example

Picture a senior accountant at a 4-state regional bank, the Friday before a quarterly audit kickoff. The audit team is getting view-only access to her loan loss reserve workbook on Monday. The workbook has 11 tabs of source data, 3 tabs of summary calculations, and dozens of cross-tab VLOOKUP and INDIRECT formulas that pull from the source tabs. If the audit team accidentally deletes a row in a source tab, every dependent formula breaks and the workbook starts showing #REF errors mid-meeting.

Her standard handoff procedure: save a copy of the workbook with _AUDIT in the filename, then go through every summary tab and convert formulas to values so the audit copy is fully self-contained. Her current method: select column G, Ctrl+C, right-click, Paste Special, Values. Repeat for column H, I, J. Repeat for the next 3 tabs. About 20 minutes of clicking, with one or two times per quarter where she pastes into the wrong cell and has to undo back to a known good state.

With this macro she opens the audit copy, selects the entire used range of each summary tab (Ctrl+A inside the data block), runs the tool. The macro freezes 1,400 formula cells across the 3 summary tabs in about 2 seconds each. The popup shows the count after each run, which she sanity-checks against her expectation. The audit copy is now self-contained and safe to share. Total elapsed: about 90 seconds for all three tabs.

On a 1,400-cell freeze pass across 3 tabs, expect about 18 minutes saved per quarter. The bigger win is not having to remember the exact paste-special muscle memory, which is what causes the occasional clobber.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the range you want to freeze. This can be a single column, a block, or the whole used range via Ctrl+A.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick ConvertFormulasToValues, click Run.
3. The macro replaces every formula cell with its evaluated value. The popup reports how many cells got converted and how many were skipped.

FAQ

Does it preserve cell formatting like dates and currency?

Yes. The macro only changes the cell's value, not its NumberFormat. So a formula returning a date stays formatted as a date, currency stays formatted as currency, and percentages stay formatted as percentages. The visible display is identical before and after; only the underlying formula is gone.

What happens with array formulas?

Single-cell array formulas ({=...}) freeze cleanly. Multi-cell array formulas where assigning to one cell at a time would error are skipped and counted in the "skipped" total. 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 convert with this macro after first replacing the array with a static range. Most modern dynamic-array formulas (XLOOKUP, FILTER, SORT) freeze cleanly cell-by-cell.

Will it touch hardcoded values?

No. The macro skips any cell where HasFormula is False. So if your selection mixes formulas and typed-in numbers, only the formulas get rewritten and the typed-in numbers are left exactly as they were.

Can it freeze just the formulas in a specific column or tab?

Yes. The macro operates on whatever you select. To freeze one column, click the column header (or select just that column), then run. To freeze an entire tab, Ctrl+A twice (or click the corner cell at the intersection of row and column headers), then run.

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. The macro runs entirely offline. Your data never leaves your machine.