The spreadsheet did not go away - AI moved in next door
Finance runs on the grid, and it is not going anywhere. What changed is that AI now sits beside it: a chat window that writes and explains a formula you paste back, and a copilot living inside Excel or Sheets that can analyze a range and drop a chart in place. Both are useful. Both can be subtly, confidently wrong. The skill tonight is not "let AI do my spreadsheet" - it is "let AI draft, and me verify". You describe what you want, the AI gives you a formula, and you prove it on a row where you already know the answer before you trust it on the other nine thousand.
Two modes of AI in the grid 6 min live
There are two ways AI shows up around your spreadsheet, and they solve different problems. Knowing which one you are using stops the most common frustration - waiting for the grid to "just do it" when you are actually in a chat window, or pasting sensitive data into a consumer chat when a governed in-grid copilot was right there.
LiveChat-assist versus in-grid copilot3 min▶
Same AI family, two very different working styles. Pick by what you are trying to do.
- Chat-assist - you are in ChatGPT or Claude, beside the sheet. You describe the task, it hands you a formula and an explanation, you paste the formula into the cell. The grid does the actual math. This is where formula generation, debugging, and "teach me what this does" live.
- In-grid copilot - Excel Copilot or Gemini in Google Sheets sits inside the workbook. It can read a selected range, build a quick pivot or chart, and summarize what a tab is doing, all without you leaving the file. Great for fast exploration and a first-pass chart.
Self-studyWhich one for the job in front of you2 min read▶
A quick gut-check when you are not sure which mode you want.
- "I need a formula and I do not remember the syntax" - chat-assist.
- "This XLOOKUP returns #N/A and I cannot see why" - chat-assist, paste the formula and a sample row.
- "Give me a chart of monthly spend by category, now" - in-grid copilot.
- "What is this inherited workbook even doing?" - in-grid copilot to summarize, chat-assist to decode the ugly formulas.
You will often bounce between the two in one task, and that is fine. Just stay aware of which window you are in and what it can see.
What it is genuinely great at 5 min live
AI in the grid is not magic and it is not useless - it is very good at a specific, boring set of jobs that eat your afternoons. Lean into these, and stay skeptical of the one place it will quietly hurt you: a formula that looks right and is off by a subtle logic error.
LiveThe jobs it earns its keep on3 min▶
| Task | How AI helps |
|---|---|
| Formula generation | Describe the lookup or aggregation in words; get XLOOKUP, SUMIFS, or a nested IF with the syntax right |
| Formula debugging | Paste a broken formula and a sample row; it explains the #N/A, #REF, or wrong result and suggests the fix |
| Data cleanup | Parse a jumbled column, dedupe, standardize dates and text case - as a formula or a step list |
| Pivot and analysis | In-grid copilot builds a quick pivot, ranks, or trends a range and charts it in place |
| Explain this workbook | Summarize what an inherited or tangled workbook does before you touch it |
The SUMIFS that was subtly wrong. An analyst asked AI for a SUMIFS to total Q2 costs by department. The formula looked perfect and returned a clean number - but the criteria range was off by one column, so it summed the wrong field. It matched the total by luck on the first department and was wrong on the rest. Nobody noticed until the reconciliation broke. The fix was not a better prompt - it was testing the formula on one department whose total was known.
Self-studyWhere it quietly fails - and the tell2 min read▶
The danger is not the obvious error - a broken formula throws #REF and you catch it. The danger is the plausible error: a formula that runs clean and returns a number that is simply wrong.
- Off-by-one ranges. The lookup or sum points one column or row off. Runs fine, wrong answer.
- Wrong match mode. Approximate match where you needed exact, so it grabs the nearest instead of the right row.
- Silent type coercion. Text that looks like a number ("1,200") does not sum, and the AI does not always warn you.
The tell is always the same: it looks authoritative and it did not show you a check. So you build the check in - test on rows where you already know the answer. That is the whole of Part 3.
The safe pattern: describe, generate, test, keep it in the sheet 4 min live
One habit protects you from every subtle-wrong-formula story above. It costs about ninety seconds and it is not optional in finance. Describe the intent, get the formula, test it on rows where you know the answer, and keep the math in the sheet where it can be audited - never trapped in a chat log nobody can trace.
LiveWhy the math stays in the sheet2 min▶
This is the rule that separates a spreadsheet you can defend from a number you cannot trace.
- Describe the intent. "Sum Q2 marketing spend by region, exact match on region code." The clearer the intent, the fewer subtle-wrong outcomes.
- Generate the formula. Get the formula and the explanation. Read the explanation - that is where you catch a wrong match mode or the wrong range.
- Test on known rows. Run it where you already know the answer: one region whose total you totalled by hand, or a filtered subtotal. If it matches there, your confidence is earned, not assumed.
- Keep the math in the sheet. The formula lives in the cell, referencing your data. Anyone can click it and see the logic. Never let the AI just tell you "the total is 4.1m" in chat - that number is untraceable and unverifiable.
Generate and verify a SUMIFS against a known total ★ 12 min · everyone builds
We take a real task - total spend by category for one month - and walk the safe pattern end to end. You describe the intent, get a SUMIFS, then prove it against a total you already know before you trust it anywhere else.
In Excel or Google Sheets, paste the small synthetic expense table below into cells A1:C11 (it is safe for any tool - rail one). Column A is category, B is region, C is amount in SGD.
Get a known answer first. Manually total the "Marketing" rows: 620 + 300 = 920. Write 920 in a cell labelled "known Marketing total". This is your check.
Ask chat-assist for the formula with the prompt below. It should hand you a SUMIFS (or XLOOKUP variant) plus a one-line explanation of how the criteria work.
Paste the formula into a cell and point it at "Marketing". Does it return 920? If yes, your formula logic is verified on a known row. If not, read the explanation - the range or the match is off, and now you can see exactly where.
Now trust it wider. Drag or re-point it to the other categories. Because you proved it on Marketing, you can rely on it for Salaries and Travel without hand-totalling every one. That is the payoff of the known-row test.
Clean a messy column and verify a sample ★ 8 min · build your own
Real finance data is filthy - dates in three formats, inconsistent text case, stray spaces. AI is excellent at proposing the cleanup, and you stay excellent at spot-checking it. We standardize a jumbled date column and then verify a handful of rows by eye.
Paste the messy date column below into a new column. Notice the problem: same dates, four different formats, one with a trailing space. A PivotTable or SUMIFS by month will treat these as different values.
Ask chat-assist for a cleanup approach with the prompt below. Ask for a formula if one will do, or a short step list if the mess needs more than one move.
Apply the suggested formula or steps into an adjacent "clean date" column. Keep the original column untouched beside it - never overwrite raw data, so you can always compare.
Verify a sample by eye. Pick three rows across the different original formats and confirm the cleaned value is the correct date. If "03/04/2026" resolved to the wrong day-versus-month, you catch it now - date ambiguity is where cleanup quietly goes wrong.
Only once the sample checks out do you use the clean column for grouping, pivots, or lookups. Verified sample first, trust second.
The month that split in two. A team pasted a year of expenses and grouped by month for the board pack. Two months showed suspiciously low totals - because the same month had been entered as both "Mar-2026" and "2026-03" and the pivot counted them separately. AI cleaned the column in seconds once asked; the win was the analyst spot-checking three rows and catching that one "03/04" was European day-first, not US month-first. AI did the tedious 90%; her eyes caught the 10% that would have embarrassed the whole pack.
Try it yourself - this week ◐ 30-45 min total
- Finish both demos if you ran short - especially the known-row test in Demo 1. Feeling a formula prove itself against a number you calculated is the lesson.
- Take one real (synthetic or public) formula you struggle with at work - a nested IF, an INDEX/MATCH, a tricky SUMIFS - and have chat-assist write and explain it. Then test it on a row you know.
- Clean one genuinely messy column from your own work using the Demo 2 pattern. Keep the raw column, verify three rows by eye before you trust the clean one.
- Try the in-grid copilot once (Excel Copilot or Gemini in Sheets) on a range: ask it to chart monthly spend, and sanity-check the chart against a number you know.
- Add your best formula prompt to the prompt library you started in b2 - "generate and verify a SUMIFS" is a keeper.
Official sources covered
This session teaches from public AI-for-spreadsheets curricula. Certificates, graded assessments, and vendor tooling stay with the official programs - noted honestly. This session covers:
Three questions before you go 🎯 ◐ 90 seconds
1 · When AI helps with a spreadsheet, where should the actual math live?
LLMs are not calculators - they predict text and can be confidently wrong on arithmetic. Keep the formula in the cell so the sheet does the math and anyone can audit the logic.
2 · An AI-generated SUMIFS runs clean and returns a tidy number. What do you do before trusting it?
The dangerous errors are the plausible ones - off-by-one ranges or the wrong match mode that run fine and return the wrong number. Testing against a known total is the only reliable catch.
3 · You want a quick chart of monthly spend without leaving your workbook. Which mode fits?
The in-grid copilot lives inside the workbook and can pivot, trend, and chart a range in place. Chat-assist is for writing and debugging the formulas you paste back into the grid.