It’s 3:12 PM. You just pasted 78 rows of supplier invoices into Sheet1 — all with inconsistent naming like 'ACME Corp', 'acme-corp', and 'Acme Corporation LLC'. Your CFO needs a clean vendor list by 4:00. You type =UNIQUE(A2:A79)… and nothing happens. You try =AI_CLEAN(A2:A79). Excel returns #NAME?. You stare at the formula bar. You wonder: Does Excel have an AI function?
The Problem
People assume ‘AI’ means magic buttons that fix messy data instantly. It doesn’t. Excel’s AI capabilities are embedded — not branded — and only activate when paired with the right structure, version, and context. The confusion starts here:
| Feature | Available in Excel 365? | Requires Copilot? | Works on Local Files? | Rating (1–5) |
|---|---|---|---|---|
| =TEXTSPLIT() | ✓ | ✗ | ✓ | 4.8 |
| =FLASHFILL (Ctrl+E) | ✓ | ✗ | ✓ | 4.5 |
| =COPILOT (natural language) | ✓ (v2404+) | ✓ | ✗ (requires cloud sync) | 3.2 |
| =AI_SUMMARIZE() (fictional) | ✗ | ✗ | ✗ | 0.0 |
| =XLOOKUP with fuzzy match (via Power Query) | ✓ | ✗ | ✓ | 4.0 |
Notice the last row: no ‘AI’ in the name — yet it uses Levenshtein distance logic under the hood. That’s the hidden truth. Excel’s AI isn’t a function — it’s a stack of smart defaults, pattern recognition, and probabilistic matching.
The Solution
Here’s what actually works — right now, no subscription required beyond Microsoft 365:
- Type your messy vendor list in A2:A12 — e.g., “ACME Corp”, “acme-corp”, “Acme Corporation LLC”, “Amce Corp”, “ACME INC”.
- In B2, enter:
=TEXTSPLIT(A2," "). Drag down to B12. This splits each entry by spaces — revealing tokens. - In C2, enter:
=PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-"," "),"."," "),"LLC",""))). This normalizes casing and removes noise. - Select C2:C12 → Data tab → Text to Columns → Delimited → Space → Finish. Now you have clean, tokenized words.
- In D2, enter:
=INDEX($C$2:$C$12,MATCH(TRUE,ISNUMBER(SEARCH("Acme",C2:C12)),0))+ press Ctrl+Shift+Enter if using legacy Excel (not needed in 365). - Finally, in E2, use:
=UNIQUE(FILTER(C2:C12,ISNUMBER(SEARCH("Acme",C2:C12)))). Result? One clean vendor name: “Acme Corporation”.
That’s 6 steps — but notice: zero Copilot, zero internet, zero add-ins. Just formulas that behave intelligently because they’re built on Excel’s modern engine.
| Original (A2:A12) | Normalized (C2:C12) | Final Unique (E2:E4) |
|---|---|---|
| ACME Corp | Acme Corp | Acme Corp |
| acme-corp | Acme Corp | Acme Corporation |
| Acme Corporation LLC | Acme Corporation | Acme Inc |
| Amce Corp | Amce Corp | |
| ACME INC | Acme Inc | |
| Acme Tech Solutions | Acme Tech Solutions |
The beauty of this approach is that it’s auditable. You see every transformation step in its own column — unlike Copilot, which gives you a black-box result.
Going Further
You can extend this logic without AI branding — but with real intelligence:
- Use
=SEQUENCE(ROWS(A2:A12))+=SORTBY(A2:A12,LEN(A2:A12))to rank entries by string length — often the longest variant is most complete. - In Power Query, enable Fuzzy Matching (Home → Merge Queries → Advanced Options → check “Use fuzzy matching”). Set similarity threshold to 85%. This matches “Acme Corp” to “ACME CORP” with 92% confidence.
- Create a lookup table in F1:G10 with known variants (F1 = “ACME”, G1 = “Acme Corporation”). Then use
=XLOOKUP(A2,$F$1:$F$10,$G$1:$G$10,A2,2). The,2enables approximate match — Excel treats it like a soft AI classifier. - Surprising tip:
=CELL("address",INDEX(A:A,MATCH(TRUE,ISNUMBER(SEARCH("acme",A:A)),0)))returns$A$3— the first cell containing “acme”. That’s Excel doing pattern search *without regex*, faster than any add-in.
When NOT to Use This
Avoid these scenarios — even if the formulas work:
- Legal or compliance documents: Normalizing “Smith & Sons LLP” to “Smith and Sons” drops legal status. Never auto-trim “LLC”, “Inc”, or “PLC” without validation.
- Non-Latin scripts: =TEXTSPLIT fails on Chinese or Arabic text unless delimiter is explicit. Use =MID() + =FIND() instead — or switch to Power Query’s Unicode-aware splitting.
- Files opened in Compatibility Mode: Dynamic arrays (FILTER, SEQUENCE, etc.) return #SPILL! errors. Check File → Info → “Compatibility Mode” — if enabled, save as .xlsx first.
- Copilot hallucinations: When you ask Copilot “Summarize Q3 sales trends”, it may invent numbers from prior sessions. Always verify against source cells — especially if the workbook hasn’t been synced to OneDrive in >2 hours.
What makes this elegant is knowing when to lean on Excel’s built-in smarts — and when to shut them off entirely.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Flash Fill | Ctrl+E | Works after typing 1–2 examples in adjacent column |
| Open Name Manager | Ctrl+F3 | Critical for auditing named ranges used in AI-like formulas |
| Insert Function Dialog | Shift+F3 | Search “textsplit”, “filter”, “unique” — no need to memorize syntax |
| Open Copilot pane | Alt+Q | Only works in Excel for Web or desktop v2404+ with M365 license |
| Toggle Formula View | Ctrl+` | See all formulas at once — essential for debugging AI-style logic |
