A Google Sheets .xlsx breaks silently in Excel — rewriting 203 cells without dynamic arrays

Build something in Google Sheets, export it as .xlsx, hand it to someone, and they open a different file than the one you tested. The failure is quiet enough that neither of you finds out.

After putting a password manager sheet on Booth, I wanted to check that the “works in Excel and Google Sheets” line on the product page was true, so I opened the exported .xlsx with openpyxl. Every dashboard formula had turned into a function I’d never seen called __xludf.DUMMYFUNCTION. There were 203 of them. All 203 were wrapped in IFERROR. This is how I found them and what I rewrote them into.

No error appears, so nothing looks broken

Google-only functions don’t get stripped on export. Excel can’t parse them, so they’re stored as __xludf.DUMMYFUNCTION("original formula") with the original kept as a string argument. Opening that in Excel should give you #NAME?.

It didn’t, because the original formula was already inside an IFERROR.

=IFERROR(__xludf.DUMMYFUNCTION("IFERROR(SORTN(FILTER({'パスワード一覧'!A2:A80, …

That wrapper was there to print an em dash when a query returned nothing. In Excel it also catches “no such function.” What comes back is an empty string or the fallback text, and the cell reads ”— no matches —”. The display for zero results and the display for a formula that never ran are the same.

Five places in the shipped file were affected.

flowchart TD
  A["Sheets formula<br/>ARRAYFORMULA / FILTER / SORTN / QUERY"] --> B["Opened in Excel"]
  B --> C["Function does not exist"]
  C --> D["The outer IFERROR catches it"]
  D --> E["Empty string returned"]
  E --> F["Screen shows '— no matches —'"]

The reused-password warning read “no matches” not because there were no duplicates, but because the check never ran. That’s a safety-adjacent readout showing a clean bill of health it hadn’t earned.

Count the broken cells instead of hunting for them

__xludf.DUMMYFUNCTION persists as text, so walking every sheet with openpyxl gives you an exact count. It beats squinting at the dashboard, and the same code proves the count went to zero after the rewrite.

KEYS = ("DUMMYFUNCTION", "REGEXMATCH", "SORTN",
        "QUERY(", "ARRAYFORMULA", "UNIQUE(", "FILTER(")

def check_no_google_funcs(path):
    wb = openpyxl.load_workbook(path)
    bad = []
    for sh in wb.worksheets:
        for row in sh.iter_rows():
            for c in row:
                v = c.value
                txt = getattr(v, "text", None) or (v if isinstance(v, str) else "")
                # formula cells only — the FAQ sheet mentions these names in prose
                if not txt.startswith("="):
                    continue
                if any(k in txt for k in KEYS):
                    bad.append(f"{sh.title}!{c.coordinate}")
    return bad

That txt.startswith("=") line earns its place. The workbook ships with a how-to sheet whose FAQ answers use the word ARRAYFORMULA in ordinary prose. Without the filter, prose you can’t fix shows up in your list of things to fix.

The count came to 203: 199 in the password list, 3 on the dashboard, 1 in subscriptions. Nearly all of the 199 were column G, where only G2 held a real formula. G3 downward had become =IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),"") — 198 placeholder husks where the array formula used to spill.

Skip dynamic arrays — Excel 2016 doesn’t have them

Excel 365 ships FILTER, SORT, UNIQUE, and TAKE. They map closely onto what the Google formulas were doing, which makes them the obvious target. They also don’t exist in Excel 2019 or 2016. I can’t know which version a buyer runs, and quietly amending the product page to say “requires 365” isn’t a fix, it’s a retreat.

So the rewrite avoids dynamic arrays entirely.

The idea is to split “sort and take the top N” into two steps. Each row computes its own rank into a helper column; the display side just asks for the row whose rank is n. Delete the sorting operation and the need for dynamic arrays goes with it.

I generated the replacements row by row from openpyxl. Pasting 200 rows × 4 helper columns by hand isn’t realistic, and when the range needs to change later it’s one constant in the script.

Write the rank into a helper column with SUMPRODUCT

“Everything past 90 days since its last update, ordered by days elapsed, descending” becomes a per-row formula. RANK doesn’t fit because the ranking applies to a filtered subset.

def rank_renew_formula(row):
    L, A = f"$L{row}", f"$A{row}"
    la, lb = f"$L$2:$L${LAST_ROW}", f"$A$2:$A${LAST_ROW}"
    rows = f"ROW($L$2:$L${LAST_ROW})"
    return (
        f'=IF(AND({A}<>"",ISNUMBER({L}),{L}>90),'
        f'SUMPRODUCT(({lb}<>"")*ISNUMBER({la})*({la}>90)*'
        f'(({la}>{L})+(({la}={L})*({rows}<ROW({L})))))+1,"")'
    )

Which produces:

=IF(AND($A2<>"",ISNUMBER($L2),$L2>90),
   SUMPRODUCT(($A$2:$A$200<>"")*ISNUMBER($L$2:$L$200)*($L$2:$L$200>90)
   *(($L$2:$L$200>$L2)+(($L$2:$L$200=$L2)*(ROW($L$2:$L$200)<ROW($L2)))))+1,"")

Multiplying conditions together is the whole trick with SUMPRODUCT: count the rows that beat you, add one, and that’s your rank. Two details do real work here.

The ISNUMBER term can’t be dropped. Column L returns "" for rows with nothing to measure, and Excel evaluates text as greater than any number, so $L$2:$L$200>90 on its own would let every blank row into the ranking. Multiplying by the numeric test removes them.

Ties need handling too. Two rows with identical elapsed days would share a rank, and MATCH only ever returns the first hit, so the other row vanishes from the table. Adding (equal value) × (lower row number) puts the earlier row first and keeps ranks distinct.

Replace REGEXMATCH with an array constant and SUBSTITUTE

Password strength was checking character classes with REGEXMATCH(E2, "[A-Z]"). Excel has no regular expressions, so I switched to counting occurrences per class instead.

def char_class_count(cell, chars):
    """Build a formula counting how many characters of `cell` appear in `chars`."""
    arr = "{" + ",".join(f'"{c}"' for c in chars) + "}"
    return f'SUMPRODUCT(LEN({cell})-LEN(SUBSTITUTE({cell},{arr},"")))'

Handing SUBSTITUTE an array constant of {"A","B",…,"Z"} gives back 26 strings, each missing one letter. Sum the length deltas with SUMPRODUCT and you have the uppercase count. SUBSTITUTE is case-sensitive, so [A-Z] and [a-z] port over directly. Symbols come out as total length minus upper, lower, and digits.

My first version split the string with MID(E2, ROW(INDIRECT("1:"&LEN(E2))), 1) and matched each character with FIND. Verification rated every single password as weak. INDIRECT array expansion behaves differently across environments, and when it fails it lands in the IFERROR branch — so once again, quietly. Dropping INDIRECT cleared it.

Let the display side do nothing but INDEX/MATCH

With ranks sitting in a helper column, the dashboard only has to fetch the row holding rank n.

def lookup(rank_col, value_col, first_row, row):
    n = row - first_row + 1
    return (
        f'=IFERROR(INDEX({SHEET}!{value_col}$2:{value_col}${LAST_ROW},'
        f'MATCH({n},{SHEET}!${rank_col}$2:${rank_col}${LAST_ROW},0)),"")'
    )

=IFERROR(INDEX('パスワード一覧'!A$2:A$200,
         MATCH(1,'パスワード一覧'!$R$2:$R$200,0)),"")

The rank could come from a relative reference like ROWS(B$10:B10), but since Python is emitting a formula per row anyway, a literal integer is simpler and dodges another function-support difference. IFERROR shows up here as well, though now it carries only its intended meaning: no row has rank 3, so there are fewer than three matches.

Formulas aren’t the only thing that goes quiet

Writing sample data for the trial edition with openpyxl, I repeated the same class of mistake. Passing dates as the string "2019-04-02" blanked out days-elapsed, the needs-update count, and the top-5 rotation list all at once.

Column L computes INT(TODAY()-K2). When K2 holds text the subtraction fails, IFERROR absorbs it, and an empty string comes back. The helper column’s ISNUMBER then correctly excludes the row, MATCH finds nothing to fetch, and the dashboard prints “no matches.” No error surfaces anywhere along that chain.

ws.cell(row=r, column=10).value = datetime.date.fromisoformat(reg)  # a date, not a str
ws.cell(row=r, column=10).number_format = "yyyy-mm-dd"

IFERROR is worth having as long as it only catches the failures you anticipated. Once it starts folding unanticipated ones into the same appearance, a file can ship broken and stay that way.

Wrapping up

If you build in Google Sheets and ship .xlsx, open the export with something other than a spreadsheet at least once. Counting __xludf.DUMMYFUNCTION takes twenty lines, and the same twenty lines confirm the count reached zero after you fix it.

For the rewrite itself, dropping all the way down to helper columns and INDEX/MATCH suited a distributed file better than reaching for 365’s dynamic arrays. The formulas get long, but Excel 2016 and Google Sheets produce the same numbers. When a script generates them, formula length stops being a cost worth optimizing.

The same instinct — make breakage visible instead of trusting it not to happen — shows up in catching content rule violations at build time with Zod. The corrected file is live on the password manager sheet.

FAQ

What happens to Google-only functions when you export to .xlsx?

They survive the export. ARRAYFORMULA, FILTER, SORTN, QUERY, UNIQUE, and REGEXMATCH have no Excel equivalent, so the exported file stores them as a function called __xludf.DUMMYFUNCTION with the original formula kept as a string argument. openpyxl shows them plainly. Excel never evaluates them.

Why does it show ‘no matches’ instead of an error?

Because the original formula was already wrapped in IFERROR. On the Sheets side that wrapper existed to print an em dash when nothing matched. In Excel it also catches the “this function does not exist” error, so the cell returns an empty string or the fallback text and the sheet looks fine. Every one of the 203 broken cells in my file had that shape.

Can’t you just swap in Excel 365’s FILTER / SORT / UNIQUE?

Only if you can assume the buyer’s Excel version. Dynamic arrays don’t exist in Excel 2019 or 2016, so they’re off the table for something you ship to strangers. Putting the rank, sequence, and flag into per-row helper columns and reading them back with INDEX/MATCH produces the same result on Excel 2016 and Google Sheets alike.

How do you count character classes without REGEXMATCH?

With an array constant and the length delta from SUBSTITUTE. SUBSTITUTE($E2,{"A","B",…},"") returns one string per letter with that letter removed, so summing the LEN differences with SUMPRODUCT gives you the count. SUBSTITUTE is case-sensitive, which covers the upper-versus-lower distinction for free. Splitting the string with MID and INDIRECT is fragile across environments — in my case it silently rated every password as weak.