--- title: "Spreadsheet formulas with AI | Optimize All Academy" description: "Why formulas are a sweet spot Spreadsheets remain the most widely used analysis tool in business. AI assistants (including those built into spreadsheet…" url: https://optimizeall.com/learn/ai-for-data-analysis-and-decision-making/spreadsheet-formulas-with-ai updated: 2026-10-05 --- AI for Data Analysis & Decision Making · Writing formulas, SQL and Python with AI, and verifying them · lesson 7 of 16 · 10 min # Spreadsheet formulas with AI ## Why formulas are a sweet spot Spreadsheets remain the most widely used analysis tool in business. AI assistants (including those built into spreadsheet tools) can write formulas from plain-language descriptions, explain inherited formulas, and debug errors. The result is a formula you can see, test and reuse, which is inherently more verifiable than a number stated in chat. ## Describing what you need Give the AI the layout and the logic: ```text Sheet "Orders": A = order_date, B = country, C = channel, D = revenue. Sheet "Summary": A2:A7 = countries, B1:G1 = months as dates (first of month). Write a formula for B2 that sums revenue for the country in A2 and the month in B1, which I can fill across and down. Use Excel. Explain it. ``` Mention which tool (Excel, Google Sheets, others), since functions and syntax differ, and specify the locale if relevant (some locales use semicolons as argument separators). ## Example output and explanation ```text =SUMIFS(Orders!$D:$D, Orders!$B:$B, $A2, Orders!$A:$A, ">="&B$1, Orders!$A:$A, "<"&EDATE(B$1,1)) ``` The explanation should cover the mixed references (`$A2` locks the column, `B$1` locks the row) so filling works, and the date range logic (from the first of the month up to, but not including, the first of the next month). ## Explaining and debugging - **Explain:** paste an inherited formula and ask "Explain step by step what this does and any edge cases where it could give wrong results." - **Debug:** paste the formula, the error or wrong result, and a few sample rows: "This returns #N/A for some rows; here are three of them." - **Refactor:** "Rewrite this nested IF as something easier to maintain." ## Verification techniques for formulas 1. **Hand-check a few cells.** Filter the source data for one country and month, sum it manually (or with the status bar), and compare. 2. **Reconcile totals.** The sum of your summary table should equal the total of the source column (for the covered period). If not, something is double-counted or missed. 3. **Test edge cases:** blanks, zero values, text in number columns, dates at month boundaries, spelling variants ("UK " with a trailing space). 4. **Check fill behavior:** after filling, spot-check a far corner cell to confirm references moved correctly. 5. **Watch for silent failures:** lookups that return the wrong match because of approximate matching defaults, or errors wrapped in IFERROR that hide real problems. ## Common AI formula mistakes - Using a function not available in your tool or version. - Wrong reference locking, so filled formulas drift. - Approximate instead of exact matching in lookups. - Off-by-one date ranges (including the first day of the next month). - Assuming clean data (trailing spaces, numbers stored as text). ## Worked example: commission calculation A sales manager asks for a tiered commission formula: 5% up to a threshold, 8% above it, plus a bonus if the target is met. The AI's first formula applies 8% to the whole amount once the threshold is exceeded, rather than only to the portion above it. The manager catches it by testing three example 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 a few hand-calculated test cases pay off. ## Documenting formulas for colleagues Ask the AI to add a short note beside complex formulas (what it calculates, its inputs and any assumptions, such as "revenue excludes refunds"). Workbooks outlive their authors; a one-line explanation prevents the next person from breaking a formula they don't understand. ## Hands-on: modern formulas worth asking for Current Excel (Microsoft 365) and Google Sheets support functions that make AI-written formulas easier to read and test. Ask for them by name when your version supports them: ```text =LET(rev, Orders!D:D, cty, Orders!B:B, dt, Orders!A:A, start, B$1, finish, EDATE(B$1, 1), SUMIFS(rev, cty, $A2, dt, ">=" & start, dt, "<" & finish)) =XLOOKUP(A2, Customers!A:A, Customers!C:C, "NOT FOUND", 0) exact match, explicit not-found =FILTER(Orders!A:D, (Orders!B:B = "UAE") * (Orders!D:D > 1000), "none") ``` `LET` names each part so a reviewer can read the logic; `XLOOKUP` with an explicit not-found value avoids silent wrong matches. ## Hands-on: a checks sheet in five formulas ```text A1 Source total =SUM(Orders!D:D) A2 Summary total =SUM(Summary!B2:G7) A3 Totals reconcile? =ABS(A1 - A2) < 0.01 A4 Blank countries =COUNTBLANK(Orders!B2:B20000) A5 Unknown countries =SUMPRODUCT(--ISNA(MATCH(Orders!B2:B20000, Lists!A:A, 0))) ``` In Microsoft 365, you can also ask **Copilot in Excel** to explain a formula or suggest one, or use **Python in Excel** for analysis that is awkward in formulas. With the **Claude for Excel** add-in or Google Sheets' **Gemini** features, the same rule applies: the formula is the deliverable, and you test it with hand-calculated cases. ## Second worked example: a UAE property manager's service charges A property manager 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 totals no longer match the building's actual costs. The manager's checks sheet flags that the allocated total exceeds the invoice. The corrected version allocates the remainder after minimums proportionally. Three hand-calculated test units and the reconciliation check both pass. ## Going further For important workbooks, build a small "checks" sheet: reconciliation totals, counts of blanks, and flags for values outside expected ranges, each showing TRUE or FALSE. Ask the AI to write these check formulas. A glance at the checks sheet before sharing catches most errors. ## Video lecture: Spreadsheet formulas with AI Lecture coming soon · 15 chapters · about 8 minutes. Read the full transcript below. 1. Spreadsheet formulas with AI 2. Why formulas 3. Describe precisely 4. What good looks like 5. Verification 6. Readable functions 7. Example 1: tiered commission 8. Example 2: service charge allocation 9. Watch me do it, part 1 10. Watch me do it, part 2 11. In-spreadsheet AI 12. Trust signals 13. Common mistakes 14. Recap 15. Try this now ## Lecture transcript ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ## Key takeaways - Formulas are verifiable artifacts; prefer them to numbers stated in chat. - Describe sheet layout, logic, tool and locale; ask for an explanation. - Verify by hand-checking cells, reconciling totals, testing edge cases and checking fill behavior. - Tiered and date-boundary logic are common failure points; test with hand-calculated examples. ## Try it Ask an AI to write one formula you need this week. Verify it with three hand-calculated test cases and a reconciliation total. - [Previous: A reusable prompt library for trustworthy AI analysis](https://optimizeall.com/learn/ai-for-data-analysis-and-decision-making/analysis-prompt-library) - [Next: SQL with AI: from question to query](https://optimizeall.com/learn/ai-for-data-analysis-and-decision-making/sql-with-ai) - [All lessons of AI for Data Analysis & Decision Making](https://optimizeall.com/learn/ai-for-data-analysis-and-decision-making)