You built a working sheet, chose File → Download → Microsoft Excel (.xlsx), and sent it on. It came back with a screenshot attached: a column that summed to 4,812 in the browser now reads #NAME?, and the table your QUERY produced is a single frozen number with empty cells underneath. Nothing is corrupted. The export simply cannot carry a formula language that Excel does not speak.

The fix is not to repair the file afterwards: decide before you export which formulas you can lose, and give the rest logic Excel can run.

What the export actually keeps

Google’s .xlsx writer is good at presentation. These survive a round trip in almost every case:

  • Cell values, number formats, fonts, fills, column widths and merged cells
  • Frozen panes, sheet order, print areas
  • Conditional formatting from simple comparisons, plus data validation lists
  • Named ranges, and formulas both engines share: SUM, SUMIFS, COUNTIFS, IF, VLOOKUP, INDEX/MATCH, TEXT

What does not survive is anything the Excel calculation engine has no definition for. It fails silently in the grid — the file opens without a warning, so you find the damage only when someone edits a cell and the numbers do not move.

Why the formulas break in two different ways

When Google meets a function Excel does not have, it does one of two things depending on the function.

It writes the last calculated value. This is the common outcome for QUERY, IMPORTRANGE and GOOGLEFINANCE. The cell looks correct, and the formula bar shows 4812 instead of a formula. Nothing is broken, but nothing recalculates either: change a source cell and the figure stays where it was. Most people ship this without noticing, because the workbook looks right on the day it goes out.

It writes the function name anyway. Excel then reports #NAME? because it has never heard of it. Expect this with SPLIT, FLATTEN, SORTN, the three REGEX* functions, IMAGE, custom Apps Script functions, and any Named Function you defined in Sheets.

ARRAYFORMULA fails in a third, nastier way. The wrapper is dropped, so a formula that filled forty rows collapses into one value with blanks below. TEXTJOIN, XLOOKUP and FILTER are a grey area: they exist in Excel 2019 and Microsoft 365 but are written with an _xlfn. prefix, so an older Excel shows #NAME? while a current one works.

Fix 1 — Replace the fragile functions before you export

Do this in the Google Sheet, on a copy, while you can still compare both versions. Substitutions are mechanical:

  • QUERY(A1:E500, "select A, sum(D) group by A") becomes SUMIFS with a criteria column, or an Excel pivot table if you need the grouping
  • IMPORTRANGE becomes a pasted copy of the values, or Data → Get Data → From Web against the published CSV URL
  • ARRAYFORMULA becomes a normal formula filled down, or a table’s calculated column
  • SPLIT becomes Data → Text to Columns, run once on the imported column
  • REGEX* becomes Find & Replace with wildcards, or TEXTBEFORE in Microsoft 365

Then export. This keeps a live, editable workbook — the only version worth sending to someone who will type into it.

Fix 2 — Export a values-only copy and rebuild the logic in Excel

When the sheet is a report rather than a model, stop trying to preserve formulas. In Google Sheets take File → Make a copy, and in the copy select the used range, copy, then Paste special → Values only on each sheet. Export that copy.

You now have an honest snapshot: every number correct as of today, no dead formulas pretending to calculate. Rebuild only the two or three formulas someone will actually change — totals, a variance column, a lookup — inside an Excel table (Insert → Table) so the calculated column fills itself. That beats debugging forty converted formulas, and the recipient gets something they can extend.

Fix 3 — Move the data, not the workbook

If the workbook exists mainly to carry rows between systems, skip .xlsx altogether. File → Download → Comma-separated values exports the active sheet only, so you get one file per tab — annoying, but each is small and imports predictably through Data → From Text/CSV.

The catch is encoding: a CSV from Sheets is UTF-8, and Excel’s double-click path may still read it as ANSI and mangle accented names. CSV opens with garbled characters in Excel covers the import.

Fix 4 — Check the numbers and dates that change type

Formula loss is visible. Type damage is not, and it produces wrong answers rather than obvious errors.

Long numbers. An account number or 16-digit reference is stored as text in Sheets. Excel sees digits, converts them to a number, and after 15 significant digits the tail becomes zeros. Format those columns as text before export, or set the column type to Text in the import dialog. Leading zeros go the same way: 00742 arrives as 742.

Dates. Both apps count from 1900, so most dates land correctly. A sheet stored as text, or one from a legacy Mac workbook on the 1904 system, can arrive shifted by four years and four days. Check the last date in each column before trusting a date filter.

After import, use Home → Find & Select → Go To Special → Formulas → Errors to list every #NAME?, #REF! and #VALUE! in one pass. Ctrl+` (grave accent) flips the workbook into formula view, the quickest way to spot cells that are now bare values.

When rebuilding is the wrong call

The exporter also truncates at Excel’s limits without complaining. A tab with 1.2 million rows is written up to row 1,048,576 and the rest dropped — the file opens, the row count looks plausible, and 150,000 records are gone. Split oversized sheets before export.

The other case worth stopping on: a workbook whose entire value is a chain of QUERY and IMPORTRANGE formulas. Converting it produces a dead snapshot that looks alive — the next person to update a source cell gets stale numbers with no error to warn them. Rebuild it on the Excel side, or keep it in Sheets and share the browser link. A converted workbook where every formula shows #REF! has lost its sheet references, and no repair invents those back; our repair service can rebuild links from the structure that is left, but a clean re-export is usually cheaper.

FAQ

Why does my formula show a number instead of the formula?

The function had no Excel equivalent, so Google stored the computed value. The file never contained the formula, so retype the logic if it must recalculate.

Does downloading as .xlsx keep ARRAYFORMULA?

No. The wrapper is removed and the multi-cell result usually collapses to one value. Rewrite it as a filled-down formula or a calculated column.

Why #NAME? in Excel 2016 but not in Microsoft 365?

Newer functions export with an _xlfn. prefix. Microsoft 365 and Excel 2021+ resolve it; older builds do not. That is a version gap, not corruption.

Still stuck? Email the file to help@easyofficefix.com with the exact error text. You get an assessment and a flat price before any work starts.