Guides
How to Convert All Formulas to Values in Excel (Whole Workbook)
Updated 2026-10-18
A quote or settlement sheet is going to a client. The formulas reveal how you price things and where the numbers come from, and references to other files make Excel ask them to update links. The usual answer is to replace formulas with their values before sending.
Save a copy first. Once formulas are values, they are gone; undo only works until you close the file.
Method 1: Paste Values
- Select the range (for a whole sheet, click the corner button or press Ctrl+A).
- Copy with Ctrl+C.
- Right-click → under Paste Options choose Values (the 123 icon), or press Ctrl+Alt+V, pick Values and OK.
Pasted in place, each formula is replaced by the value it was showing.
Method 2: several sheets, one at a time
Excel has no "convert the whole workbook" button; you repeat method 1 on every sheet.
Don't do it with grouped sheets. When several tabs are selected, Copy takes only the active sheet's cells, but Paste writes them into every grouped sheet at the same position, overwriting the other sheets' data with the active sheet's.
With many sheets or files, a short VBA loop helps, or use method 3 below.
Check before converting
- Formulas never calculated. In manual calculation mode, or in files written by other programs and never recalculated in Excel, cells may have no result yet. Press F9 (or Formulas → Calculate Now) first.
- Array and dynamic array formulas. Select the whole spill range when pasting; selecting part of it gives "You can't change part of an array".
- External links. The links inside formulas disappear, but Edit Links may still list them because of names, charts or data validation. See Excel keeps asking to update links.
- Many files. Opening, selecting, pasting and saving each one takes a while.
Method 3: convert the whole workbook online, and clean the rest
In xlszip's Clean for sharing, tick Turn all formulas into values:
- every formula on every sheet becomes its displayed value in one go; shared and array formulas are handled as whole groups;
- afterwards every cell is verified: the displayed value must match the original, or no file is produced;
- formulas that were never calculated have no value to keep and become blank; the report says how many;
- tick Break external links, Remove author, company, local path and Remove comments in the same pass;
- process several files at once; a single file up to 10 MB is processed in your browser by default and never uploaded.
Only want to hide the formulas?
If the file stays in use internally and you just don't want formulas visible: select the cells → Ctrl+1 → Protection tab → tick Hidden, then Review → Protect Sheet. The formula bar no longer shows them. Sheet protection is easy to remove, though (see Forgot the sheet protection password), so for anything confidential, values are the safer choice.