Guide

Getting Started With Python Excel AutomationDeep dive

Convert Excel Formulas to Values with Python

Freeze a workbook's formulas into static values with openpyxl while keeping every style, plus the LibreOffice recalculation step for files Excel has never opened.

Sending a workbook full of formulas to someone outside your team is a small act of faith: they may open it in an application that calculates differently, paste it into a system that cannot calculate at all, or change an input and unknowingly rewrite your numbers. Freezing formulas into values removes that risk. This guide, part of Working with Excel Formulas in Python, shows the conversion, how to keep the formatting, and what to do when there are no cached results to freeze.

Prerequisites

Bash

pip install openpyxl

The optional recalculation step later uses LibreOffice, which is available as soffice on most Linux distributions and inside the standard LibreOffice install on macOS and Windows.

The core conversion

Load the file twice, copy cached numbers over formula cells, and save under a new name:

Python

from openpyxl import load_workbook

SRC = "report.xlsx"
DST = "report_frozen.xlsx"

values = load_workbook(SRC, data_only=True)   # cached results
book = load_workbook(SRC)                     # formulas, styles, layout

frozen = 0
for sheet in book.sheetnames:
    v_ws, b_ws = values[sheet], book[sheet]
    for row in b_ws.iter_rows():
        for cell in row:
            if isinstance(cell.value, str) and cell.value.startswith("="):
                cell.value = v_ws[cell.coordinate].value
                frozen += 1

book.save(DST)
print(f"froze {frozen} formula cell(s) into {DST}")

Writing into book rather than values is what preserves the report's appearance: fills, borders, number formats, column widths and merged cells all live on that handle and are untouched by the value assignment. Saving to a new path keeps the model intact — you almost never want to overwrite the file that still contains the formulas.

Refuse to ship silently empty cells

If the source has no cached results, the loop above writes None everywhere and produces a beautifully formatted blank report. Check first, and fail loudly:

Python

from openpyxl import load_workbook

def freeze(src, dst):
    values = load_workbook(src, data_only=True)
    book = load_workbook(src)

    missing = []
    for sheet in book.sheetnames:
        v_ws, b_ws = values[sheet], book[sheet]
        for row in b_ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.startswith("="):
                    cached = v_ws[cell.coordinate].value
                    if cached is None:
                        missing.append(f"{sheet}!{cell.coordinate}")
                    else:
                        cell.value = cached

    if missing:
        raise ValueError(
            f"{len(missing)} formula cell(s) have no cached result "
            f"(first: {missing[0]}). Recalculate the workbook first."
        )
    book.save(dst)
    return dst

freeze("report.xlsx", "report_frozen.xlsx")

Raising rather than warning is the right default here, because the failure is invisible downstream: a client receives a report where every total reads zero and nothing in the file says why.

Recalculate headlessly when there is no cache

A workbook your own pipeline generated has never been calculated, so there is nothing to freeze. LibreOffice can do the calculation on a server, without a display:

Python

import subprocess
from pathlib import Path

def recalculate(path, outdir="recalc"):
    """Round-trip a workbook through LibreOffice so formulas get cached results."""
    Path(outdir).mkdir(exist_ok=True)
    subprocess.run(
        ["soffice", "--headless", "--convert-to", "xlsx", "--outdir", outdir, str(path)],
        check=True,
        timeout=180,
    )
    return Path(outdir) / Path(path).name

recalculated = recalculate("report.xlsx")
freeze(recalculated, "report_frozen.xlsx")

LibreOffice evaluates the formulas as it converts and writes the results into the output file, which then behaves exactly like a workbook a person saved from Excel. Two caveats are worth knowing before you build a pipeline on it: functions added in recent Excel versions may not be supported, and LibreOffice must not already be running as a desktop session for the same user profile, or the headless call exits immediately without converting.

The third box is the one to prefer when your code owns the calculation. Producing values in pandas and writing literals means there is never a cache to manage, no LibreOffice dependency, and no possibility of a reader's Excel version disagreeing with yours.

Keep an audit trail of what was frozen

Once the formula is gone, nobody can see how a number was produced. Copy the formula text into a hidden column or a comment before overwriting, so the derivation survives:

Python

from openpyxl import load_workbook
from openpyxl.comments import Comment

values = load_workbook("report.xlsx", data_only=True)
book = load_workbook("report.xlsx")

for sheet in book.sheetnames:
    v_ws, b_ws = values[sheet], book[sheet]
    for row in b_ws.iter_rows():
        for cell in row:
            if isinstance(cell.value, str) and cell.value.startswith("="):
                formula, cached = cell.value, v_ws[cell.coordinate].value
                if cached is None:
                    continue
                cell.value = cached
                cell.comment = Comment(f"Was: {formula}", "report job")

book.save("report_frozen_audited.xlsx")

Comments travel with the cell and stay out of the way visually. For a regulated report, writing the formula inventory to a separate log file alongside the delivered workbook is usually the better record — it is diffable, and it does not bloat the file a reader downloads.

Freeze only part of a workbook

Freezing everything is rarely required. A common pattern keeps the interactive model on one sheet and freezes the sheet that people copy into presentations:

Python

from openpyxl import load_workbook

FREEZE_SHEETS = {"Summary", "Board Pack"}

values = load_workbook("report.xlsx", data_only=True)
book = load_workbook("report.xlsx")

for sheet in FREEZE_SHEETS & set(book.sheetnames):
    v_ws, b_ws = values[sheet], book[sheet]
    for row in b_ws.iter_rows():
        for cell in row:
            if isinstance(cell.value, str) and cell.value.startswith("="):
                cell.value = v_ws[cell.coordinate].value

book.save("report_partial_freeze.xlsx")

Because the detail sheets keep their formulas, a reader can still trace a figure by following the summary's original layout — while the summary itself can no longer drift.

Verify the frozen file before you send it

A frozen report should contain no formulas at all, and every number that mattered should still be there. Two assertions catch both mistakes:

Python

from openpyxl import load_workbook

frozen = load_workbook("report_frozen.xlsx")
leftovers = [
    f"{ws.title}!{cell.coordinate}"
    for ws in frozen.worksheets
    for row in ws.iter_rows()
    for cell in row
    if isinstance(cell.value, str) and cell.value.startswith("=")
]
assert not leftovers, f"still holds formulas: {leftovers[:5]}"

original = load_workbook("report.xlsx", data_only=True)
for ws in frozen.worksheets:
    src = original[ws.title]
    for row in ws.iter_rows():
        for cell in row:
            if cell.value is not None:
                assert cell.value == src[cell.coordinate].value, cell.coordinate

print("frozen file matches the cached values it came from")

Run this as the final step of the job that produces the file. It takes seconds, it fails loudly when a sheet was skipped, and it turns "the freeze worked on my machine" into something the pipeline can prove on every run.

Common pitfalls and gotchas

Symptom	Cause	Fix
Frozen cells are blank	No cached results in the source	Recalculate with Excel or LibreOffice first
Styling lost	Values were written into the `data_only` handle and saved without the second load	Write into the default handle instead
Numbers frozen from stale inputs	Cache predates the last edit to source cells	Recalculate, or set `fullCalcOnLoad` before the round trip
`soffice` returns instantly, no output	A desktop LibreOffice session is using the profile	Close it, or pass a dedicated `-env:UserInstallation` profile
Dates become numbers	The cached value is a serial and the cell's format was General	Assign the date format after freezing
Original file overwritten	Wrote back to the source path	Always save the frozen copy to a new filename

Performance and scale notes

The conversion is two full parses plus one write, so on a 50 MB workbook expect it to be dominated by I/O rather than by the loop. Iterating with ws.iter_rows() is already the efficient access pattern; avoid ws.cell(...) lookups in a nested range loop, which re-resolve coordinates one at a time. Read-only mode cannot be used for the writable handle, so peak memory is roughly the size of the workbook in object form — for very large files, freeze sheet by sheet and save between sheets, or generate the frozen report from the source data instead of converting a finished workbook.

Conclusion

Freezing formulas is a copy from one workbook handle to another: data_only=True supplies the numbers, the ordinary handle supplies everything else, and saving to a new file keeps the model available. The whole technique rests on there being cached results to copy, so check for them and either recalculate through LibreOffice or, better, compute the figures in pandas and never depend on a cache at all.

Frequently asked questions

Why do my frozen cells come out empty? The workbook had no cached results to freeze. Recalculate it once — in Excel, or headlessly with LibreOffice — before running the conversion.

Does freezing lose my formatting? Not if you load the file twice and write the values into the ordinary handle. Saving a workbook loaded with data_only=True keeps styles too, but gives you no chance to check for missing values first.

Can I freeze only one sheet? Yes. Iterate the sheets you want and leave the others untouched — the conversion is per cell, so any subset works.

Is there a way to keep the formula visible for reference? Write the formula text into a neighbouring column or a cell comment before overwriting the cell, so auditors can still see how the number was derived.

Up to the parent guide:

Working with Excel Formulas in Python — the full formula workflow.

Related guides:

Read Formula Results with openpyxl data_only — the cache this conversion depends on.
Write a Formula to an Excel Cell with openpyxl — putting the formulas there in the first place.
Convert an Excel File to PDF with Python — the other common way to send an uneditable report.
Populate an Excel Template Without Losing Formatting — keeping styles through a scripted edit.