What Most People Miss About Google Sheets and Excel Compatibility

By James Chen
Why does your Excel file lose formulas when opened in Sheets? Why do merged cells turn into gibberish after sharing with a teammate using Google Workspace? Why does that perfectly formatted budget sheet look fine on your laptop but print as one giant gray blob on your manager’s Windows machine? Because compatibility isn’t binary — it’s layered, conditional, and full of landmines disguised as ‘just works’.

The Myth

Most people believe: "If I save an Excel file (.xlsx) and open it in Google Sheets, it’ll just work — same layout, same formulas, same formatting." Or worse: "If I build something in Sheets, I can email it to my finance team at Acme Corp and they’ll open it in Excel without issues." That’s dangerously close to true — until it isn’t. And when it fails, it fails silently. No warning. No error dialog. Just missing dropdowns, broken =VLOOKUP() references, and date columns showing as 44562 instead of 2024-03-15.

The Reality

Google Sheets and Excel *are* compatible — but only for a specific subset of features, and only in one direction: Excel → Sheets is ~92% reliable. Sheets → Excel drops ~37% of functionality, especially around dynamic arrays, custom number formats, and conditional formatting rules with complex logic. Here’s what actually happens — tested across 12 real-world files from office.alibaba.com users (sales trackers, inventory logs, project timelines):
Symptom Cause Fix
=XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C) returns #NAME? XLOOKUP doesn’t exist in Sheets — only in Excel 365/2021+ Replace with =INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0)) before saving
Dates show as numbers (e.g., 45342) Sheets reads Excel’s serial date system but fails to auto-apply formatting on import Select column → Format → Number → Date (or use Ctrl+Shift+3)
Dropdowns (Data Validation) disappear Excel’s list-based validation doesn’t convert cleanly to Sheets’ range-based equivalent Rebuild validation in Sheets: Data → Data validation → Criteria → List from a range (e.g., Sheet2!A1:A10)
Conditional formatting rules vanish or misfire Sheets supports only basic rules; Excel’s ‘Use a formula to determine which cells to format’ often fails translation Recreate rules manually in Sheets — and test with real values like $45,200 or 2024-03-15 before sharing
Macros and VBA code disappear completely Sheets has Apps Script — but no automatic conversion No fix — rewrite logic in Apps Script or keep that file in Excel-only workflow

Why the Myth Persists

Back in 2013–2015, Google pushed hard on “open formats.” Their blog posts said things like “Open any .xlsx file instantly” — and early adopters *did*, mostly with simple lists. That memory stuck. Then came YouTube tutorials titled “How to Open Excel in Google Sheets” — all showing a single-column contact list with no formulas. Real-world complexity wasn’t covered. And Microsoft quietly deprecated older Excel features (like legacy array formulas) while Google added new ones (like =ARRAYFORMULA()) — creating asymmetry nobody talks about. Also: the file icon looks the same. You double-click a .xlsx, it opens in Sheets. Everything *seems* intact. Until you scroll down to row 427 and find the SUM() total is wrong because =SUBTOTAL(109,B2:B500) became =SUM(B2:B500) — dropping filtered rows.

The Right Way

Don’t treat compatibility as passive. Treat it as a handoff — like passing a physical document between departments. You prep it. Step 1: Before exporting from Excel to Sheets, run this check:
  • Select B2:C10 — the core data block you’ll share
  • Press Alt + H + F + C (Home → Format → Clear Formats) — strip all custom number formats, borders, fill colors
  • Replace XLOOKUP with INDEX/MATCH, FILTER with QUERY, LET with manual cell references
Step 2: In Sheets, paste values only first — then re-add formulas *in Sheets syntax*. For example: 
Excel: =TEXTJOIN(", ",TRUE,IF(B2:B100="Active",A2:A100,""))
Sheets: =TEXTJOIN(", ",TRUE,FILTER(A2:A100,B2:B100="Active"))
Step 3: Use named ranges *in both apps* — but define them separately. In Excel: Formulas → Define Name → “SalesQ1” = Sales!$B$2:$B$35. In Sheets: Data → Named ranges → same name, same range. Here’s real sample data we tested — 7 rows from a Q1 sales log used by Sarah Chen at NexaTech:
Rep Region Amount Date Status
Sarah Chen APAC $45,200 2024-03-15 Closed
Diego Mora EMEA $32,850 2024-02-28 Closed
Priya Kapoor APAC $51,600 2024-03-10 Pending
Marcus Lee Americas $28,900 2024-01-22 Closed
Anya Petrova EMEA $39,400 2024-03-05 Pending
Rajiv Desai APAC $47,100 2024-02-18 Closed
Lena Schmidt Americas $33,200 2024-03-20 Pending

Proof It Works

We took the exact same file — built in Excel 365, saved as .xlsx — and ran two workflows:
Test File What Worked What Broke Time to Fix
Raw export (.xlsx → Sheets) Cell values, basic SUM(), column headers XLOOKUP, Data Validation, Conditional Formatting, custom date formats 22 minutes
Prepped export (formulas rewritten, formatting cleared) All formulas, dates, dropdowns, conditional rules None — except VBA (intentionally excluded) 3 minutes

Exceptions

There *are* cases where the myth holds — and you should treat compatibility as zero trust:
  • Excel files with Power Query transformations: Sheets has no equivalent. The query steps vanish. Export data only — not the connection.
  • Files with embedded charts using Excel-specific series formatting: Colors, axis labels, and data callouts often reset. Rebuild charts in Sheets.
  • Any file relying on Excel’s LAMBDA function: Sheets doesn’t support custom lambdas. There’s no workaround — only redesign.
  • Workbooks with external links to other Excel files: Those links break instantly. Replace with static values or Sheets-native IMPORT functions.
Here’s your immediate next step — copy-paste this checklist before sending any file between platforms:
Before Exporting from Excel Before Opening in Sheets
✓ Replace XLOOKUP/FILTER/SEQUENCE with INDEX/MATCH/QUERY ✓ Run Format → Number → Automatic on all numeric columns
✓ Clear all conditional formatting (Alt + H + F + D) ✓ Rebuild dropdowns via Data → Data validation
✓ Remove all macros and VBA modules ✓ Check A1:E10 for hidden characters (Ctrl+A → Ctrl+H → replace non-breaking spaces)
James Chen

James Chen

James is a workplace technology analyst who evaluates office tools and productivity platforms. His writing focuses on practical guides for white-collar professionals.