What Most People Miss About Google Spreadsheets Compatible With Excel

By David Park
Yes, Google Sheets files open in Excel and Excel files open in Sheets. But if you assume full compatibility, you’ll lose conditional formatting, break array formulas, and misalign dates without warning.

Google Sheets vs Excel File Compatibility

SymptomCauseFix
#REF! errors after opening Excel file in SheetsExcel uses absolute sheet references like '[Budget.xlsm]Q1' — Sheets doesn’t support external workbook linksReplace with IMPORTRANGE or copy data manually into same spreadsheet
Dates show as numbers (e.g., 45200 instead of 2023-09-15)Excel stores dates as serial numbers starting from 1900; Sheets uses 1970 epoch — mismatch when cells lack date formattingSelect column → Format → Number → Date in Sheets. In Excel, use Ctrl+1 → Date → match locale (e.g., English-US)
=XLOOKUP() returns #NAME?XLOOKUP exists only in Excel 365/2021+ — Sheets has no equivalent (VLOOKUP works, but INDEX/MATCH is safer)Use =INDEX(A2:A10,MATCH(D2,B2:B10,0)) in both. Test in Sheets first before sending to Excel users.
Cell borders disappear in Excel after editing in SheetsSheets applies 'border to cell', Excel applies 'border to range' — overlapping border logic differsIn Excel, reapply borders via Home → Borders → All Borders (Alt+H+B+A). Don’t rely on Sheets’ border UI for cross-platform work.
=TEXTJOIN(,", ",A2:A10) fails in SheetsSheets requires delimiter argument even if empty: =TEXTJOIN("",TRUE,A2:A10). Excel accepts blank first arg.Always specify delimiter and ignore_empty (TRUE/FALSE) explicitly. Use =TEXTJOIN(", ",TRUE,A2:A10) in both.
Pivot tables vanish or reset layoutSheets pivot tables don’t save field settings in .xlsx; Excel ignores Sheets’ pivot cache structureNever share pivot output as source data. Export pivots as values (Copy → Paste Special → Values) before saving as .xlsx.

When to Use Google Sheets

Do this when collaboration trumps precision: shared vendor scorecards, real-time event checklists, or weekly sales dashboards updated by non-Excel users. Example: Sarah Chen at Acme Corp runs a supplier onboarding tracker. She shares Sheet1!A1:E25 with Procurement, Legal, and IT. Each team adds status updates in real time. No version conflicts. No email chains. She exports to Excel only when finalizing the Q3 vendor report. Formulas used: =TODAY(), =IF(ISBLANK(C5),"Pending",C5), =COUNTIF(E2:E25,"Approved") She avoids: XLOOKUP, dynamic arrays, macros, or any function requiring Excel 365. Her validation rules live in Sheets Data Validation — not Excel Data Tools.

When to Use Excel

Use Excel when your data must survive audit, calculation integrity matters, or you’re doing anything with financial modeling, HR payroll, or inventory reconciliation. Example: At Veridian Logistics, the freight cost model lives in FreightModel.xlsx. It pulls raw data from ERP into Sheet1 (A1:G5000), calculates landed cost in Sheet2 using =SUMPRODUCT(B2:B5000,D2:D5000,E2:E5000), and outputs variance reports in Sheet3. This file breaks in Sheets because: - SUMPRODUCT over 5,000 rows times three columns exceeds Sheets’ calculation engine limits (spills timeout) - Named ranges like "FuelRate" and "ZoneMap" aren’t preserved on export - Conditional formatting rules based on formulas (e.g., =$F2>0.05) convert to static colors So Veridian never opens that file in Sheets. They export *only* the final summary tab (Sheet3!A1:F20) as CSV for stakeholder review.

The Hybrid Approach

Don’t choose one. Chain them. Here’s what works: • Build your master dataset in Excel (e.g., MasterData.xlsx, columns A–Z, 12,000 rows) • Publish it as a web query: Data → Get Data → From Web → paste public Google Sheet URL (must be published to web) • In Sheets, pull live data with =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1abc123...", "Sheet1!A1:Z12000") • Add lightweight Sheets-only layers: dropdowns, checkboxes, simple dashboards • Never edit raw data in Sheets. Never run calculations on IMPORTRANGE output — wrap it in ARRAYFORMULA only if necessary Surprising tip: You can force Excel to refresh IMPORTRANGE-style data without Sheets. Save your Excel file to OneDrive, enable co-authoring, and share the link. Excel Online users get real-time sync — no Sheets middleman needed.

Performance Benchmarks

TaskExcel (365, 16GB RAM)Sheets (Chrome, 10k row sheet)Winner
Open 50k-row .xlsx with formulas1.8 secCrash or 45+ sec timeoutExcel
Refresh =QUERY(IMPORTRANGE(...)) pulling 2k rowsN/A (no native import)2.3 sec (cached)Sheets
Apply conditional formatting to 10k cells4.1 sec1.9 secSheets
Calculate =XIRR(B2:B1000,C2:C1000)0.07 sec#NUM! error (unsupported)Excel
Undo last 5 editsCtrl+Z ×5 (instant)Version history only — no granular undoExcel
Add comment to cell B12Alt+R+C → type → EnterRight-click → Comment → type → Ctrl+EnterTie

Your next step: Open your most critical shared workbook right now. Check these three cells:

  • A1: Does it contain a date? If yes, select it → Right-click → Format → Confirm it’s set to Date (not General).
  • B2:C10: Any VLOOKUP or INDEX/MATCH? Replace all with =XLOOKUP() only if every user has Excel 365 — otherwise stick with INDEX/MATCH.
  • Sheet2!D1: Is it a pivot table? If yes, copy that entire range → Paste Special → Values → Save As → Report_Final_Values.xlsx.
David Park

David Park

David brings deep expertise in office supply evaluation and procurement. He has tested hundreds of products to help teams make informed purchasing decisions.