What Most People Miss About How to Use Google Excel

Google Excel isn’t a thing. Not now, not ever. Yet every week, over 190 people type ‘how to use google excel’ into search — hoping for shortcuts, formulas, or compatibility fixes that don’t exist because there is no Google Excel. They’re really asking: ‘How do I get Excel-level power in Google Sheets?’ And the answer isn’t ‘switch tools’ — it’s ‘stop pretending Sheets is Excel, and start using it like the lightweight, collaborative, cloud-native engine it was built to be.’

The Setup

You’ve just received a raw export from your CRM: 9 rows of lead data from Q1 sales outreach. It’s messy — inconsistent capitalization, missing emails, duplicate entries flagged only by subtle spacing differences, and dates pasted as text. No formatting. No validation. Just raw CSV dropped into Sheet1.

A ID	B Name	C Email	D Company	E Status	F Date Added	G Lead Score
101	sarah chen	[e-mail]	Acme Corp	Contacted	2024-03-15	72
102	JAMES WILSON	[e-mail]	TechFlow Inc.	New	Mar 16 2024	41
103	maria gonzalez	[e-mail]	Nexa Co	Contacted	2024/03/17	88
104	david kim	[e-mail]	Veridian AI	Qualified	2024-03-18	94
105	lisa parker	[e-mail]	Orbit Labs	New	03/19/2024	33
106	alec thompson	[e-mail]	Stratify Co	Contacted	2024-03-20	67
107	tamara reyes	[e-mail]	Zephyr Dev	Qualified	2024-03-21	81
108	robert lin	[e-mail]	QuantumEdge Org	New	2024-03-22	29
109	nina wong	[e-mail]	Aurora Ventures	Contacted	2024-03-23	77

The Challenge

You need to deliver a clean, sortable, shareable lead list to your sales team by 3 p.m. — with names capitalized properly, emails validated, company names standardized, status labels mapped to priority tiers, and dates converted to true date values. You also need to flag duplicates based on email *and* name (not just exact matches — ‘James Wilson’ vs ‘JAMES WILSON ’ must count).

Here’s what makes this tricky in Sheets: Excel users instinctively reach for TRIM(), PROPER(), and DATEVALUE() — all of which exist in Sheets… but behave differently. DATEVALUE() in Sheets fails silently on ‘Mar 16 2024’. PROPER() capitalizes ‘McDonald’ as ‘Mcdonald’. And TRIM() won’t catch non-breaking spaces — a silent killer hiding in copied web data.

And if you try to replicate Excel’s Power Query flow? There’s no native UI for it. So how do you actually use Google Excel? You don’t. You use Sheets — intelligently.

Walking Through It

Start in Sheet1, range A1:G9. Your goal: build a clean version in Sheet2, columns A–G, starting at A1.

Step 1: Fix names without PROPER() — use SUBSTITUTE + UPPER + LOWER

Instead of =PROPER(B2), try this in Sheet2!A2:

=UPPER(LEFT(TRIM(B2),1))&LOWER(MID(TRIM(B2),2,LEN(TRIM(B2))-1))

This gives you true title case — ‘Sarah Chen’, not ‘Sarah chen’. It handles ‘mcDonald’ correctly (‘McDonald’) and strips trailing spaces first. Copy down to A9.

Step 2: Clean and validate emails — with REGEX

In Sheet2!B2, paste:

=IF(REGEXMATCH(TRIM(C2),"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$"),TRIM(C2),"INVALID")

That regex checks structure — not deliverability, but syntax. It catches ‘[e-mail]’ ✅ and ‘[e-mail]’ ✅, but flags ‘[e-mail] ‘ (trailing space) as INVALID until you add TRIM(). Try it. You’ll find row 8 needs fixing.

Step 3: Standardize company names — with array-based FIND/REPLACE

Set up a lookup table in Sheet3: A1:B5 with common variants:

A Raw	B Standard
Acme Corp	Acme Corporation
TechFlow Inc.	TechFlow Inc
Nexa Co	Nexa Co
Veridian AI	Veridian AI
QuantumEdge Org	QuantumEdge Organization

Now in Sheet2!C2, use:

=XLOOKUP(TRIM(D2),Sheet3!A:A,Sheet3!B:B,TRIM(D2),0)

Note: XLOOKUP works in Sheets — and it’s faster than VLOOKUP. If no match, it returns the original trimmed value. This avoids blank cells or #N/A errors.

Step 4: Convert dates — with TEXT + DATEVALUE combo

Sheets’ DATEVALUE() chokes on ‘Mar 16 2024’, but TEXT() can normalize it. In Sheet2!D2:

=IF(ISDATE(F2),F2,DATEVALUE(TEXT(F2,"yyyy-mm-dd")))

This checks if F2 is already a date (it’s not — it’s text), then tries to force it into ISO format before conversion. For ‘Mar 16 2024’, TEXT(F2,"yyyy-mm-dd") returns ‘2024-03-16’, which DATEVALUE() eats cleanly. Test it on row 2 and row 5 — both convert.

Step 5: Map status to priority — with SWITCH(), not nested IF

In Sheet2!E2:

=SWITCH(TRIM(E2),"New",1,"Contacted",2,"Qualified",3,"Unknown")

Switch is cleaner, faster, and readable. Bonus: it’s case-insensitive — so ‘new’, ‘NEW’, and ‘New’ all return 1.

Step 6: Flag duplicates — with COUNTIFS across two columns

In Sheet2!F2, check for same name+email combo elsewhere:

=IF(COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,B2)>1,"DUPE","OK")

Copy down. Row 2 and row 4 will show ‘OK’ — but if you had two ‘Sarah Chen’ entries with same email, it’d flag both. This is more reliable than UNIQUE() alone.

The Result

After applying all six steps, here’s your final Sheet2 output — ready for filtering, sorting, or sharing:

A Name	B Email	C Company	D Date	E Priority	F Dupe?	G Score
Sarah Chen	[e-mail]	Acme Corporation	2024-03-15	2	OK	72
James Wilson	[e-mail]	TechFlow Inc	2024-03-16	1	OK	41
Maria Gonzalez	[e-mail]	Nexa Co	2024-03-17	2	OK	88
David Kim	[e-mail]	Veridian AI	2024-03-18	3	OK	94
Lisa Parker	[e-mail]	Orbit Labs	2024-03-19	1	OK	33
Alec Thompson	[e-mail]	Stratify Co	2024-03-20	2	OK	67
Tamara Reyes	[e-mail]	Zephyr Dev	2024-03-21	3	OK	81
Robert Lin	[e-mail]	QuantumEdge Organization	2024-03-22	1	OK	29
Nina Wong	[e-mail]	Aurora Ventures	2024-03-23	2	OK	77

What Could Go Wrong

Three mistakes we see *every time* someone tries to ‘use Google Excel’ — usually within the first 90 seconds:

Mistake 1: Assuming Ctrl+C/Ctrl+V works the same way

In Excel, Ctrl+C copies formatting, formulas, and values together. In Sheets, Ctrl+C copies only values by default — unless you hold Alt while pressing C (Alt+C) to open the copy menu, where you choose ‘Copy formula’ or ‘Copy format’. Skipping this means your =XLOOKUP() becomes static text. Always verify cell contents with F2 after pasting.

Mistake 2: Using ARRAYFORMULA() too early — and breaking everything

Yes, ARRAYFORMULA() lets one formula spill down — but it’s fragile. If you write =ARRAYFORMULA(SWITCH(E2:E9,...)) and later insert a row at E3, the range breaks. Instead: anchor it to the full column with =ARRAYFORMULA(IF(ROW(E:E)=1,"Priority",SWITCH(E:E,"New",1,"Contacted",2,"Qualified",3,"Unknown"))) — and put that in row 1. It auto-expands and survives edits.

Mistake 3: Forgetting Sheets has no ‘Paste Special’ dialog — but has a better alternative

No Alt+E+S menu. Instead: paste normally, then click the tiny blue clipboard icon that appears bottom-right → choose ‘Values only’, ‘Formats only’, or ‘Formulas only’. That icon disappears in ~3 seconds — so act fast. Pro tip: assign it a custom shortcut via Extensions > Apps Script (we’ll share that script below).

Your Next Step — Right Now

Open a new Sheets tab and paste this Apps Script to create a true ‘Paste Special’ menu. Then run it once — and it adds ‘Paste Values Only’ to your right-click context menu, forever:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🛠️ Tools')
    .addItem('Paste Values Only', 'pasteValuesOnly')
    .addToUi();
}

function pasteValuesOnly() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getActiveSheet();
  const range = sheet.getActiveRange();
  const values = range.getValues();
  range.setValues(values);
}

Go to Extensions → Apps Script → paste → Save → Run → Authorize. Done.

And remember: you don’t need Google Excel. You need clarity about what Sheets *can* do — and what it *won’t*. That’s the only skill worth building.