What Most People Miss About Excel and Google Spreadsheet
By James Chen
No, Excel and Google Sheets are not the same. But if you assume they behave identically—especially when copying formulas or sharing files—you’ll waste hours debugging silent errors.
The Setup
You’re auditing Q1 sales for a small SaaS team. Finance sent you two files: Q1_Sales_Excel.xlsx and Q1_Sales_Google.xlsx. Both contain identical columns: Rep Name, Client, Deal Size ($), Close Date, Region, and Status. You need to calculate commission (12% of Deal Size), flag overdue follow-ups (>30 days since Close Date), and group by Region.
Here’s what lives in A1:F10 of both files:
Rep Name
Client
Deal Size ($)
Close Date
Region
Status
Sarah Chen
Acme Corp
$45,200
2024-03-15
APAC
Closed
Diego Morales
Nexus Labs
$62,800
2024-02-28
EMEA
Closed
Priya Patel
Stellar Inc
$31,500
2024-01-10
NA
Follow-up
Jamal Wright
Vista Group
$79,000
2024-03-05
NA
Closed
Aiko Tanaka
Lumen Systems
$22,400
2024-02-12
APAC
Proposal Sent
Marcus Bell
Orion Health
$54,600
2024-01-22
EMEA
Closed
Tasha Reed
Quantum Dynamics
$88,300
2024-03-20
NA
Closed
Eduardo Silva
FusionWorks
$19,750
2024-02-05
EMEA
Follow-up
The Challenge
You want to add two new columns: G1 = Commission and H1 = Overdue?. In Excel, you type =F2*0.12 in G2, then drag down. In Sheets, same thing—right? Not quite. When you copy that formula from Excel into Sheets, it *works*. But when someone edits the file in Sheets and adds a row between rows 4 and 5, Excel keeps references like $F$2 locked — Sheets silently converts them to relative references unless you explicitly lock with $. Worse: date math behaves differently. =TODAY()-C2>30 returns TRUE in Excel on 2024-04-15 for a 2024-03-15 close date—but in Sheets, it might return #VALUE! if the date column is formatted as plain text (which Sheets does more often than Excel). And here’s the kicker: Alt+= (AutoSum) inserts SUM() in Excel—but in Sheets, it inserts SUM(A1:A10) even if your selection is B2:B8. You won’t notice until your totals are off by $200K.
Walking Through It
Let’s fix G2:G10 first. In Excel (A1 mode), select G2, type =F2*0.12, press Ctrl+Enter to fill without changing selection, then drag down to G10. Done.
In Sheets? Same formula works—but now try inserting a new row at row 5. Excel shifts the formula in G6 to =F6*0.12 automatically. Sheets does too… *unless* the original formula used F2 instead of $F$2. If you’d typed =F2*0.12 in Sheets and inserted above row 5, G6 becomes =F7*0.12 — correct. But if you’d used =F$2*0.12, it stays =F$2*0.12, pulling from the wrong row. So Sheets is *more forgiving* on relative refs—but *less forgiving* on mixed ones.
Now for overdue logic. In Excel, in H2, type =IF(TODAY()-D2>30,"YES","NO"). Drag down. Works.
In Sheets? Same formula — but check D2’s format first. Right-click > Format > Number > Date. If it says “Plain text”, Sheets treats D2 as a string. Excel would auto-convert. Sheets won’t. So you get #VALUE!. Fix: Select D2:D10, go to Format > Number > Date — or use =IF(TODAY()-DATEVALUE(D2)>30,"YES","NO") as a band-aid.
Before (H2:H10, unformatted dates in Sheets):
Row
H2 Value
2
#VALUE!
3
#VALUE!
4
#VALUE!
After fixing date format + applying formula:
Row
H2 Value
2
NO
3
NO
4
YES
The Result
Here’s the final cleaned table (G2:H10) after applying consistent formulas and date formatting in both apps:
Rep Name
Commission
Overdue?
Sarah Chen
$5,424.00
NO
Diego Morales
$7,536.00
NO
Priya Patel
$3,780.00
YES
Jamal Wright
$9,480.00
NO
Aiko Tanaka
$2,688.00
NO
Marcus Bell
$6,552.00
YES
Tasha Reed
$10,596.00
NO
Eduardo Silva
$2,370.00
YES
What Could Go Wrong
Most people treat these apps like twins. They’re not. They’re cousins who went to different schools—and learned different grammar.
Symptom
Cause
Fix
Formulas break when shared with colleagues using the other app
Excel’s TEXTJOIN has no direct Sheets equivalent; Sheets uses JOIN or TEXTJOIN with different argument order
Use =CONCATENATE(A2," | ",B2) — works in both
Conditional formatting disappears after export
Sheets doesn’t support Excel’s 3-color scale “lowest/highest” logic — only percentiles or numbers
In Sheets: Format > Conditional formatting > Color scale > “Min point = number, Max point = number”
PivotTable filters don’t sync across versions
Excel saves pivot cache locally; Sheets recalculates live from source — so filtered rows in Excel won’t appear in Sheets’ version
Build filters in Sheets first, then download as .xlsx if Excel users need offline access
One last tip: Alt+M, V opens Excel’s Evaluate Formula dialog — invaluable for debugging. Sheets has no native equivalent. You’ll need to copy-paste each piece into a blank cell manually. (Trust me, I learned this the hard way during a 2 a.m. client audit.)
If you’re juggling both apps daily, keep this cheat sheet open:
Date validation: Always run =ISDATE(D2) in Sheets before doing date math
Formula porting: Replace TEXTJOIN("",TRUE,A2:C2) (Excel) with JOIN("",FILTER(A2:C2,A2:C2<>'')) (Sheets)
Keyboard shortcut: In Excel, Alt+H, O, I auto-fits column width. In Sheets, it’s Alt+O, C, I — and it only works if you’ve selected the entire column first
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.
