What Most People Miss About Excel's AI — It’s Already Running

Yes, Excel has AI now. But it doesn’t pop up asking questions—it waits silently in the background until you *trigger* it with the right command or context.

The Setup

You’re auditing Q1 sales for six regional reps. Data lives in Sheet1, A1:E9. No headers yet—just raw entries dumped from CRM export. Names are inconsistent ("J. Lee", "James Lee", "Lee, James"). Amounts include commas and dollar signs. Dates are mixed formats: some as "3/15/2024", others as "2024-03-15" or even "Mar 15, 2024".

A	B	C	D	E
J. Lee	Acme Corp	$24,500	3/15/2024	New
Sarah Chen	Nexus Labs	$18,900	2024-03-17	Renewal
Lee, James	Veridian Systems	$31,200	Mar 20, 2024	New
M. Torres	Strata Dynamics	$14,650	03/22/2024	New
James Lee	Acme Corp	$22,800	2024-03-25	Renewal
A. Kim	Quantum Edge	$45,200	Mar 28, 2024	New
Sarah C.	Nexus Labs	$19,100	2024-04-01	Renewal
T. Reed	Veridian Systems	$27,400	4/5/2024	New

The Challenge

You need a clean, deduplicated summary by rep name and company—with standardized names, numeric amounts, proper dates, and totals per rep. Doing this manually takes 12–15 minutes. You try Flash Fill. It fails on “J. Lee” vs “James Lee”. You try Power Query. Too slow for one-off cleanup. You type “=UNIQUE(A2:A9)” — but that gives you four variations of the same person. The real problem isn’t formatting. It’s ambiguity. Excel’s AI won’t auto-resolve “J. Lee” = “James Lee” unless you give it *context*. And it won’t guess your intent unless you anchor it with structure first.

Walking Through It

Do this first: Insert a header row at A1. Type “Name”, “Company”, “Amount”, “Date”, “Type”. Now select A1:E9. Press Alt + A + T. That opens the Text to Columns wizard—no AI needed yet. Choose “Delimited”, click Next, uncheck everything except “Space”, then Finish. This splits initials but leaves junk. Ignore it. We’re setting stage. Now—here’s the counterintuitive part: Don’t use Copilot yet. Select A2:A9. Go to the Data tab > Text to Columns again. This time choose “Fixed width”. Click once before the dot in “J. Lee”. Click again before “Lee,” in “Lee, James”. Finish. You now have First and Last in separate columns. Why? Because Copilot needs clean, aligned inputs to recognize patterns. Garbage in = garbage out—even with AI. Now insert two new columns: F1 = “Clean Name”, G1 = “Standard Date”. Select F2. Type: =PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(A2,".",""),","," "))). Drag down. You’ll get “J Lee”, “Sarah Chen”, “James Lee”, etc. That’s still not enough. So now—select F2:F9 and G2:G9. Right-click > Ask Copilot. If you don’t see that option, your Microsoft 365 subscription isn’t licensed for Copilot (only Business Standard, E3/E5, or Microsoft 365 Apps for enterprise). If you do see it, click it. Copilot will suggest: “Combine first and last names into full names using standard format.” Accept. It outputs:

F	G
James Lee	2024-03-15
Sarah Chen	2024-03-17
James Lee	2024-03-20
Maria Torres	2024-03-22
James Lee	2024-03-25
Alex Kim	2024-03-28
Sarah Chen	2024-04-01
Taylor Reed	2024-04-05

Notice: Copilot guessed “J. Lee” → “James Lee”, “M. Torres” → “Maria Torres”, “A. Kim” → “Alex Kim”. It used the surrounding data pattern—not dictionary lookup. Now select H1 = “Amount (Number)”. In H2, type =VALUE(SUBSTITUTE(SUBSTITUTE(C2,"$",""),",","")). Drag down. Then select H2:H9 and G2:G9. Right-click > Ask Copilot again. Tell it: “Group by Clean Name, sum Amount, show earliest and latest date per rep.” It returns a new table starting at J1.

The Result

Here’s what lands in J1:M5 after Copilot finishes:

J	K	L	M
Clean Name	Total Amount	First Date	Last Date
James Lee	$78,500	2024-03-15	2024-03-25
Sarah Chen	$38,000	2024-03-17	2024-04-01
Maria Torres	$14,650	2024-03-22	2024-03-22
Alex Kim	$45,200	2024-03-28	2024-03-28
Taylor Reed	$27,400	2024-04-05	2024-04-05

No formulas copied. No pivot tables. No manual deduping. Just one right-click and two prompts.

What Could Go Wrong

• Mistake #1: Asking Copilot before cleaning column headers. If A1 says “Rep” instead of “Name”, Copilot treats the whole column as labels—not data—and returns gibberish. It reads structure, not content.
• Mistake #2: Selecting non-contiguous ranges before right-clicking. Copilot ignores disjointed selections. Highlight A2:A9 and C2:C9 separately? It sees only A2:A9. Always hold Ctrl and click to add ranges—or better, select entire blocks like A2:E9 first.
• Mistake #3: Using “=AI()” or “=COPILOT()”. Those don’t exist. There is no AI function. Copilot only activates via right-click or the ribbon button (Home > Copilot). Typing anything in a cell triggers formula mode—not AI mode.

Next step: Open any Excel file with 10+ rows of messy data. Add headers. Select the data block. Press Alt + X + C (opens Copilot pane). Type: “Fix inconsistencies in Name and Date columns, then summarize by person.” Watch it run. If it hesitates, add: “Use first name + last name format, ISO date.” Then test this shortcut: Alt + N + V opens the Data tab—where you’ll find “Get Data” and “Text to Columns”, the two tools Copilot relies on most. Master those first. The AI just speeds up what you already know how to do.