Five ways your generated Excel file breaks on someone else's machine

openpyxl writes formulas as strings. It never evaluates them. Your generated workbook can be syntactically perfect, pass every test you wrote, and be full of #VALUE! the moment a customer opens it.

I generated nine workbooks last week and then drove real Excel over them to check. Here is everything that broke, and what to do instead.

1. TEXT() renders in the language of whoever opens the file

This is the worst one because it looks fine on your machine and ships.

ws["B4"] = '="Financial year from "&TEXT(A1,"d mmm yyyy")'

On my Spanish Excel that rendered as:

Financial year from 1 ene yyyy

The format codes inside TEXT() are interpreted in the language of the running Excel. yyyy is not a year token in Spanish — the Spanish token is aaaa — so Excel prints it literally. A French user gets a third result.

Cell number formats do not have this problem. They are stored canonically in the file and translated for display. So put the value in a cell and format the cell:

ws["C4"] = "=Setup!$C$8"
ws["C4"].number_format = "dd mmm yyyy"

Same output on every machine on earth. I have stopped using TEXT() for anything a human will read.

2. Pre-filled formulas turn 500 empty rows into 500 errors

If you pre-fill a data sheet so the user can just type into it, every unused row shows an error the moment the file opens. The workbook looks broken before anyone has entered a single number.

# every empty row shows #VALUE!
ws.cell(row=r, column=8, value=f"=ROUND($F{r}*$G{r},2)")

# guarded
ws.cell(row=r, column=8, value=f'=IF($F{r}="","",ROUND($F{r}*$G{r},2))')

Now the knock-on that nobody warns you about: a cell returning "" contains text, not a number. So this:

value=f"=IF($A{r}=\"\",\"\",$J{r}+$K{r}+$L{r})"

blows up with #VALUE! whenever $J is one of those guarded-empty cells. And a single poisoned cell propagates: mine flowed into a MIN() on a dashboard three sheets away, which silently blanked a "worst performing item" KPI. Nothing errored visibly. The cell was just empty, and I nearly shipped it.

Sum guarded columns defensively:

value=f'=IF($A{r}="","",IF($J{r}="",0,$J{r})+$K{r}+$L{r})'

3. Cross-sheet data validation triggers repair prompts

A dropdown pointing straight at another sheet works in current Excel:

dv = DataValidation(type="list", formula1="=Setup!$B$12:$B$26")

It is also exactly the kind of thing that makes older Excel and some importers show "We found a problem with some content in this file". If you are selling the file, a repair prompt is a refund.

Define a name and point the validation at the name:

wb.defined_names.add(DefinedName("Categories", attr_text="Setup!$B$12:$B$26"))
dv = DataValidation(type="list", formula1="=Categories", allow_blank=True)

4. Google Sheets compatibility is a whitelist, and failure is silent

A lot of people will open your .xlsx by uploading it to Drive. The file opens either way — the difference is whether the numbers are right.

Safe in both: SUMIFS, COUNTIFS, SUMPRODUCT, IFERROR, INDEX/MATCH, EOMONTH, ROUND, IF.

Avoid: dynamic arrays (LET, FILTER, XLOOKUP, SEQUENCE) and structured table references. Sheets either lacks them or imports them differently, and it does not tell you.

Two more, less obvious:

• Amounts as plain numbers, not a currency format. Currency formats carry a locale.
• yyyy-mm-dd dates. Sorts correctly, unambiguous everywhere, no 03/04 guessing game.

5. The one that cost me an hour: PowerShell blames the wrong line

To verify a workbook you have to open it in real Excel and recalculate. On Windows that means COM, and COM from PowerShell has a trap.

$ws.Range("J6").Value2 = $clientName

La conversión especificada no es válida.
En línea: 32 Carácter: 26
+ $setup.Range("J$(6 + $i)").Value2 = $clients[$i]
+                  ~~~~~~

The caret points at the arithmetic. The arithmetic is fine. In script scope PowerShell wraps values in PSObject, Excel rejects them from .Value2, and the InvalidCastException surfaces against the wrong token. I studied 6 + $i for half an hour.

Cast explicitly, every time:

$ws.Range("A2").Value2 = [string]$name
$ws.Range("B2").Value2 = [double]$amount
$ws.Range("C2").Value2 = [double](Get-Date "2026-01-15").ToOADate()

Dates go in as OLE Automation doubles, not strings. And prefer .Range("A2") over .Cells.Item(2,1) — the latter has its own parameterised-property quirks.

The verifier

None of the above is findable by reading your Python. You have to open the file and look. So:

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.DisplayAlerts = $false
$wb = $xl.Workbooks.Open((Resolve-Path $Path).Path)

# ... inject a realistic year of sample data here ...

$xl.CalculateFullRebuild()

$total = 0
foreach ($sheet in $wb.Worksheets) {
    try {
        # xlCellTypeFormulas, xlErrors
        $errs = $sheet.UsedRange.SpecialCells(-4123, 16)
        if ($errs) {
            Write-Output ("{0}: {1} cells -> {2}" -f $sheet.Name, $errs.Count, $errs.Address())
            $total += $errs.Count
        }
    } catch {
        # SpecialCells throws when there are no matches. That is a pass.
    }
}
if ($total -gt 0) { exit 1 }

Output on a workbook that was "working":

--- FORMULA ERRORS ---
  Jobs: 4 cells -> $O$11:$P$11,$O$15:$P$15
  TOTAL: 4

Those four were problem 2 above — quoted-but-not-started jobs where the guarded columns were text and the total tried to add them. Caught before publishing rather than after.

It exits non-zero, so it goes in the build.

Inject sample data before recalculating. An empty workbook hides every bug that only appears with values in it. That is where I found the occupancy figure that was coming out at 0.31% instead of 51% — I was multiplying by 365 a number that was already expressed per month. No formula error, no exception, just a wrong number that a customer would have found.

Takeaway

Generating a spreadsheet is not the hard part. Trusting it is. Two rules that would have saved me most of the above:

1. Nothing a human reads goes through TEXT().
2. Nothing ships until real Excel has opened it, with data in it, and reported zero errors.

I packaged the styling helpers, the Sheets-safe formula wrappers and that verifier into a module — it is here if it saves you the afternoon. The traps above are all written up in its README, which is the actually useful part.