Spreadsheet formulas with AI

0:00 Spreadsheet formulas with AI

Here's a quiet superpower of AI in spreadsheets. When an assistant writes a formula, you get something you can see, test and reuse, not just a number in a chat. But AI-written formulas fail in very specific ways: wrong reference locking, approximate matching, off-by-one date ranges. In this lecture you'll learn how to describe what you need so the AI writes the right formula, how to verify it with hand-calculated tests and a reconciliation, modern functions that make formulas readable, and a checks sheet you can add to any workbook.

0:39 Why formulas

Why focus on formulas? Because spreadsheets remain the most widely used analysis tool in business, and assistants built into them, like Copilot in Excel, Gemini in Google Sheets, or add-ins like Claude for Excel, can write formulas from plain language, explain inherited ones, and debug errors. The result is inherently more verifiable than a number stated in chat. Finance can audit it. A colleague can extend it. And you can test it with the same care you'd give any other calculation.

1:14 Describe precisely

Here's how to describe what you need. Give the layout and the logic. Sheet Orders: column A order date, B country, C channel, D revenue. Sheet Summary: countries down column A, months as first-of-month dates across row one. Write a formula for B two that sums revenue for the country and month, which I can fill across and down. Say which tool, Excel or Google Sheets, since functions differ. And mention your locale, because some locales use semicolons as separators. The more precise the layout, the fewer surprises.

1:52 What good looks like

A good answer looks like SUMIFS with mixed references: the country column locked with a dollar sign before the column letter, and the month row locked with a dollar sign before the row number, so filling works. And the date logic: from the first of the month up to, but not including, the first of the next month. Always ask the AI to explain the formula part by part. If it can't explain why a reference is locked, it probably got it wrong.

2:28 Verification

Now verification, the heart of this lecture. Hand-check a few cells: filter the source for one country and month, sum it manually, and compare. Reconcile totals: the sum of your summary table should equal the source total for the period, or something is double-counted or missed. Test edge cases: blanks, zeros, text in number columns, dates at month boundaries, and spelling variants like UK with a trailing space. Check fill behavior by spot-checking a far corner cell. And watch for silent failures, like lookups with approximate matching or errors hidden by IFERROR.

3:08 Readable functions

Modern functions help a lot. In current Excel and Google Sheets, LET lets you name each part of a formula, so a reviewer can read the logic like a sentence. XLOOKUP with an explicit not-found value and exact match avoids silent wrong matches from old lookup defaults. And FILTER returns matching rows for inspection. Ask for these by name if your version supports them. Readable formulas are easier to check, which makes them safer, whoever wrote them.

3:41 Example 1: tiered commission

First example, a simple one. A sales manager asks for a tiered commission formula: five percent up to a threshold, eight percent above it, plus a bonus if the target is met. The AI's first formula applies eight percent to the whole amount once the threshold is exceeded, not just the portion above it. The manager catches it by testing three salespeople whose commissions she calculates by hand: one below the threshold, one just above, one far above. The corrected formula passes all three. Tiered logic is exactly where hand-calculated tests pay off.

4:21 Example 2: service charge allocation

Second example, a business case. A property manager in the UAE asks for a formula that allocates building service charges by unit size, with a minimum charge for small units. The first AI formula applies the minimum after allocation, so allocated totals no longer match the building's actual costs. The checks sheet flags that the allocated total exceeds the invoice. The corrected version allocates the remainder after minimums proportionally. Three hand-calculated units and the reconciliation check both pass, and the manager can defend every tenant's charge.

4:58 Watch me do it, part 1

Watch me build a checks sheet. Cell A one: the source total, sum of the revenue column. A two: the summary total, sum of the summary grid. A three: do totals reconcile? The absolute difference is less than one cent, true or false. A four: count of blank countries in the orders sheet. A five: count of countries not in my approved list, using SUMPRODUCT with ISNA and MATCH. Five cells, each showing true or false or a count. I glance at them before sharing any workbook.

5:36 Watch me do it, part 2

The amber cell says two unknown countries. I filter the orders sheet and find U A E written with dots, and a typo. I fix them at the source, the check turns to zero, and the reconciliation stays true. Then I ask the assistant to add a one-line note beside each complex formula: what it calculates, its inputs and assumptions, like revenue excludes refunds. Workbooks outlive their authors, and that note stops the next person from breaking a formula they don't understand.

6:12 In-spreadsheet AI

A few more tools worth knowing. In Microsoft 365, you can ask Copilot in Excel to explain or suggest formulas, and use Python in Excel for analysis that's awkward in formulas, on supported plans. The Claude for Excel add-in and Gemini in Google Sheets can also read your workbook and propose changes. Whichever you use, the rule is the same: the formula is the deliverable, and you test it with hand-calculated cases and a reconciliation before anyone relies on it.

6:47 Trust signals

How do you know your spreadsheet work is trustworthy? Three signals. The checks sheet is green before anything is shared. Complex formulas carry a one-line note with inputs and assumptions. And when someone else opens the workbook, they can explain what a key formula does without asking you. If that last test fails, the formula is too clever. Ask the AI to refactor it with LET and clear names, and test it again with the same hand-calculated cases.

7:21 Common mistakes

Common mistakes. Functions not available in your tool or version. Wrong reference locking, so filled formulas drift. Approximate instead of exact matching. Off-by-one date ranges, including the first day of the next month. Assuming clean data, when there are trailing spaces or numbers stored as text. And errors wrapped in IFERROR that hide real problems.

7:45 Recap

Recap. Formulas are verifiable artifacts, so prefer them to numbers stated in chat. Describe layout, logic, tool and locale, and ask for a part-by-part explanation. Verify with hand-checked cells, reconciled totals, edge cases and fill checks. Prefer readable functions like LET and XLOOKUP. And add a checks sheet and formula notes to every important workbook.

8:09 Try this now

Try this now. Ask an AI assistant to write one formula you need this week. Verify it with three hand-calculated test cases and a reconciliation total. Then add a five-cell checks sheet to the workbook, and a one-line note beside the formula explaining what it does and what it assumes.