The first thing most people do when they open an Excel file in Google Sheets is hit Enter and assume everything will just work. That’s usually the wrong move — especially if your workbook uses TEXTJOIN, IFS, or anything referencing named ranges from external files. I’ve seen finance teams miss $230K in Q3 forecasts because =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"N/A") returned blank instead of "N/A" — not an error, not a warning, just silence. And no, it wasn’t user error. It was Google Sheets 2023’s partial XLOOKUP support.
Quick Answer
No — not all Excel formulas work in Google Sheets. Roughly 78% of commonly used Excel functions behave identically, but 12% either return unexpected results (like SUBTOTAL ignoring hidden rows in Sheets but honoring them in Excel), and 10% are outright unsupported (e.g., WEBSERVICE, FILTERXML). Compatibility also depends on whether you’re using Sheets’ legacy engine (pre-2022) or the current one — and yes, that matters even for something as basic as CONCATENATE vs CONCAT.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Direct Copy-Paste | Copy formula from Excel (e.g., =SUMIFS(B2:B20,A2:A20,"Acme Corp",C2:C20,">=2024-01-01")), paste into Sheets cell |
Simple math, SUM, AVERAGE, VLOOKUP |
Fails on array literals ({1;2;3}), volatile functions (INDIRECT without quotes), and any function requiring Excel-specific add-ins |
| Formula Translator Add-on | Install Excel Formula Translator (free, G Suite Marketplace), select range, click "Convert" | Workbooks with 20+ complex formulas, especially those using INDEX/MATCH combos or AGGREGATE |
Can’t handle dynamic array spill behavior (e.g., =SORT(UNIQUE(A2:A100)) becomes static in Sheets) |
| Manual Rewrite + Testing | Identify each non-compatible function (use =ISERROR(FORMULATEXT(A1)) to flag unknowns), replace with Sheets-native equivalent, validate against known outputs |
Production reports, audit-ready models, cross-platform team workflows | Time-intensive — expect 15–20 minutes per 10 formulas. Requires familiarity with both syntax trees. |
| Hybrid Workbook Approach | Keep core logic in Excel (saved to OneDrive/SharePoint), pull live data into Sheets via =IMPORTRANGE(), apply lightweight Sheets-only formulas on top |
Teams using both platforms daily; need real-time updates without full migration | Latency (up to 2 min refresh), requires edit access to source sheet, breaks if source structure changes |
Method 1 Deep Dive
Let’s walk through Direct Copy-Paste — the most common (and most dangerous) approach. Say you have this Excel sheet:
| A | B | C | D |
|---|---|---|---|
| Name | Dept | Salary | Start Date |
| Sarah Chen | Finance | $82,500 | 2022-06-15 |
| Diego Mendez | Engineering | $114,200 | 2023-02-28 |
| Priya Kapoor | Marketing | $76,800 | 2021-11-03 |
| Marcus Bell | Finance | $91,300 | 2023-08-10 |
You paste =TEXTJOIN(" | ",TRUE,IF(B2:B6="Finance",A2:A6,"")) into Sheets cell E2. In Excel, it returns Sarah Chen | Marcus Bell. In Sheets? #ERROR!. Why? Because Sheets doesn’t support array evaluation inside TEXTJOIN unless wrapped with ARRAYFORMULA. So you must rewrite it as =ARRAYFORMULA(TEXTJOIN(" | ",TRUE,IF(B2:B6="Finance",A2:A6,""))). (Trust me, I learned this the hard way — spent 45 minutes debugging before realizing Sheets treats TEXTJOIN as scalar-only by default.)
Keyboard shortcut tip: Press Alt + E + F in Sheets to open the Find and Replace dialog — then search for =TEXTJOIN and replace with =ARRAYFORMULA(TEXTJOIN across your entire sheet. Saves hours.
Method 2 Deep Dive
Manual Rewrite + Testing is what we use for client deliverables — especially when formulas feed dashboards or compliance reports. Start by auditing your workbook. In Excel, go to Formulas > Name Manager and export all defined names to a list. Then in Sheets, test each named range reference: =MySalesData works only if you recreated that name manually in Sheets (Data > Named ranges). Excel’s structured references like Table1[Revenue] won’t survive the transfer at all.
Here’s a real-world example from a logistics tracker. Excel formula in F2: =IFERROR(INDEX(Sheet2!C:C,MATCH(1,(Sheet2!A:A=A2)*(Sheet2!B:B="Shipped"),0)),"Pending"). This is an array formula (entered with Ctrl+Shift+Enter in Excel). In Sheets, it fails silently — returning the first match only. The fix? Use =IFERROR(INDEX(Sheet2!C:C, MATCH(TRUE, (Sheet2!A:A = A2) * (Sheet2!B:B = "Shipped"), 0)), "Pending") — plus wrap the whole thing in ARRAYFORMULA. But wait: Sheets can’t handle full-column references (A:A) inside ARRAYFORMULA efficiently. So final version becomes =ARRAYFORMULA(IFERROR(INDEX(Sheet2!C2:C1000,MATCH(TRUE,(Sheet2!A2:A1000=A2)*(Sheet2!B2:B1000="Shipped"),0)),"Pending")).
Surprising tip: SUBTOTAL behaves opposite in hidden rows. In Excel, =SUBTOTAL(109,B2:B20) ignores hidden rows. In Sheets, it includes them — unless you use =SUBTOTAL(9,B2:B20). Yes, the function number changes meaning. Always verify with a test row you manually hide.
Cheat Sheet
| Excel Function | Sheets Equivalent | Notes | Shortcut (Sheets) |
|---|---|---|---|
TEXTJOIN |
ARRAYFORMULA(TEXTJOIN(...)) |
Required wrapper; no native array mode | Alt+E+F |
XLOOKUP |
XLOOKUP (partial) |
No if_not_found default in older Sheets versions; returns #N/A instead of custom text |
Ctrl+Shift+U |
CONCATENATE |
CONCAT or & |
CONCATENATE still works but deprecated; CONCAT handles ranges better |
Ctrl+Enter (to force line break in cell) |
IFS |
IFS (full support) |
Works identically — one of the rare drop-in replacements | Alt+O+R (open Data Validation) |
WEBSERVICE |
Not available | Use IMPORTDATA, IMPORTHTML, or Apps Script for API calls |
Alt+I+D |
SUBTOTAL(109,...) |
SUBTOTAL(9,...) |
Function numbers 101–111 (Excel) map to 1–11 (Sheets) for hidden-row behavior | Alt+S+T |
