Naar de inhoud
OneKitly

Converting Formulas to Values Keeps the Cached Answer, Not the Calculation

Gepubliceerd op 1-9-2026 · 4 min leestijd · Bestandstools

Daniel Okonkwo

Daniel Okonkwo — Front-enddeveloper en techredacteur bij OneKitly

Webprestaties · Bestandsformaten

Getoetst aan 3 bronnen

Profiel bekijken →
Kort gezegd

Every cell holding a formula in an .xlsx file actually holds two things: the formula itself and the value the program that last saved the file had computed for it. Excel writes both so that another reader can show a number without recalculating a workbook it may not fully understand. Converting formulas to values deletes the first and keeps the second — it is a deletion, not an evaluation, and nothing here recomputes anything. That distinction only shows up in one place, and it is worth knowing before you rely on it: a workbook produced by a script or an export that wrote formulas without caching their results will come out with those cells empty. Measured on a two-sheet test file, a cell holding A2+B2 with no cached value produced the row 2,3, — the total column blank — while the same formula with its cached 5 produced 2,3,5. If your source is a real spreadsheet saved from Excel, LibreOffice or Numbers, the caches are there and the conversion is lossless.

Advertentie

A spreadsheet file stores both the formula and the last answer Excel computed. Stripping the formula leaves the stored answer — and leaves a blank wherever the file never carried one.

Why a file carries the answer at all

Recalculating a workbook means implementing several hundred functions, the reference model behind them and the order in which they depend on each other — and getting any of it slightly wrong produces a number that looks plausible and is not. Storing the last computed value alongside the formula lets a viewer, a converter or a phone show the right figure without any of that machinery. It is the same trade every document format makes when it caches a rendering, and it is why a spreadsheet opens instantly in a preview pane.

When you actually want this

Three situations, all of them about handing the file to someone else. A workbook whose formulas point at other files opens with broken references on any machine but yours, and values travel intact. A pricing sheet or a model you are sending out often contains its own working — margins, assumptions, the supplier tab a formula reaches into — and flattening it sends the numbers without the reasoning. And a file destined for a system that reads spreadsheets rather than opens them, an import routine or a database load, is far more predictable with no formulas in it at all. If you want to keep the working and only see it, extract the formulas to a list instead: that reads them without touching the file.

The same formula, with and without a cached result
Cell as storedAfter conversionThe row in CSV
formula A2+B2, cached value 5value 5, no formula2,3,5
formula A2+B2, no cached valueempty cell2,3,
Formules omzetten naar waardenVervangt elke formule door het resultaat waarmee Excel het laatst heeft opgeslagen, zodat het blad zijn getallen houdt maar niet langer laat zien hoe die zijn verkregen — handig voordat je een bestand verstuurt. Er wordt niets herberekend: een werkmap die zonder opgeslagen resultaten is bewaard, komt leeg terug. Werkt met Excel (XLSX, XLS, XLSM, XLSB), OpenDocument (ODS), Apple Numbers, CSV en TSV — al bevatten CSV en TSV van begin af aan geen formules.Probeer de tool →
Advertentie

Veelgestelde vragen

How do I check whether my file has the cached values?
Open it in the .xlsx viewer before converting: if the calculated cells show numbers there, the caches are present and the conversion will keep them. If they show blanks, the file was written without them and converting will make that permanent. Either way, keep the original until you have looked at the result — the conversion is not reversible.
Does it change the numbers in any way?
No. The stored value is copied nowhere and rounded nowhere — the formula is deleted and the value that was already in the cell stays exactly as it was, to the last digit the file recorded. What changes is that the cell will no longer update when something it referred to changes, which is the point of doing it.
Can I see the formulas without removing them?
That is what the formula extractor is for: it lists every formula in the workbook with its sheet and cell address and leaves the file untouched. It is the faster way to audit a model somebody sent you, and the only way to find the one cell where a range stops a row short of the total.

Artikelen die je misschien interesseren

Alle gidsen →
UitlegThe Workbook with Twenty-Three Formulas That Reported NoneA cell can hold a formula and no cached result. A reader that skips empty cells throws the formula away with them — and says the file has none. One parsing option separates the two cases.HandleidingOne File per Tab, and What a Split Leaves BehindSplitting a workbook writes one .xlsx per sheet, named after the tab. It carries the values across and not the formulas — which is the right default when the point is to send one department its own tab.UitlegThe .ods File That Is Valid, and That Your Reader RefusesOpenDocument makes styles.xml optional. Several readers demand it and reject the whole document without it — "Cannot find file styles.xml in zip" on a file that is not broken.VergelijkingA Column Called "Total (EUR)" Cannot Be an XML Element NameJSON takes your header text as a key exactly as written. XML cannot — its names may not hold spaces, parentheses or a leading digit — so they are normalised, and knowing how prevents a surprise downstream.UitlegA Cell of Three Spaces Counts as Empty, and That Is the PointWhitespace is the whole reason these tools exist. A row of blanks looks empty, is not empty to a strict test, and quietly survives every filter you apply after it.HandleidingMerging Workbooks: One Sheet Each, or One Table for AllTwelve monthly reports can become twelve tabs or one table of twelve months. Only the second can be sorted and summed in one go — and it is the one where the header rows need handling.

Vergelijkbare tools

Bronnen

Een fout in dit artikel gezien?