AI for Excel Formulas: Write, Explain, and Test the Result

Use AI for Excel formulas by giving it the worksheet layout, the calculation rule, and a few examples with known results. Ask for the formula and an explanation, then test both ordinary rows and awkward cases in a copy of the workbook. A formula that runs can still calculate the wrong thing.

fenbs can keep the approved formula prompt and workbook rules together, so your next connected assistant does not have to guess what counts as an approved expense or a missing value.

1. Describe the sheet, columns, data types, Excel version, and where the formula will go.
2. Write the business rule in plain English, including how blanks, zero values, and duplicates should behave.
3. Ask for one formula, a short explanation of each part, and predicted results for your sample rows.
4. Test the formula in Excel against answers you calculated independently. Save the approved rule with the prompt.

What does AI need to know about your workbook?

Give it column meanings, not just letters. “Column C is net expense in dollars, stored as numbers; negative values are credits” is much more useful than “sum column C.” State whether the data is an Excel table, a fixed range, or a range that will grow.

Include your Excel version and the separators your installation uses. The examples below use English function names and commas. Ask for an alternative if a function is unavailable, rather than assuming that a formula from a newer installation will work everywhere.

• Location: the sheet name, header row, first data row, and destination cell.
• Meaning: what each relevant column represents, including units and allowed status labels.
• Missing data: whether an empty cell, a zero, and text such as “unknown” mean different things.
• Copying: which references should remain fixed and which should change when you fill down.
• Expected answer: at least one small example you can solve without AI.

Use invented sample rows with the same structure when real data is sensitive. You usually do not need to upload an entire workbook to ask for a formula. For the surrounding workflow, start with using AI at work.

How do you ask for a conditional total?

Suppose column A contains a project, column B contains approval status, and column C contains numeric amounts. You want the total for the project named in E2, including only rows marked Approved. Negative amounts are credits and should reduce the total.

Write an Excel formula for F2.
Data rows: 2 through 6; headers in row 1.
A: project name. B: status. C: numeric amount in dollars.
E2: the project to total.
Sum C only when A matches E2 and B is Approved.
Keep negative amounts and genuine zero values.
Fix the source ranges so I can copy F2 down; let E2 change.
Explain each argument and predict the result for the sample below.
Do not use macros or change the data.

=SUMIFS($C$2:$C$6,$A$2:$A$6,E2,$B$2:$B$6,"Approved")

With North in E2, the expected result is 80: 100 minus 20 plus zero. The draft amount and the South amount must stay out. This uses SUMIFS↗ (opens in a new tab), which Microsoft documents for adding values that meet multiple conditions. The sum range comes first; the condition ranges must have matching dimensions.

The fixed range stops at row 6. A new row 7 will not join the calculation automatically, so do not treat this demonstration as a growing-data solution. Ask for a formula using your actual Excel table when new rows are part of the workflow.

How do you request a lookup without hiding missing matches?

A lookup answers a different question from a total: find a matching identifier and return its value. Tell the assistant whether identifiers must be unique, what a missing identifier should show, and whether a legitimate zero must remain visible.

On a separate example sheet, A2:A4 contains unique SKU codes:
SKU-1, SKU-2, SKU-3. B2:B4 contains numeric stock: 12, 0, 7.
D2 contains the SKU to look up. Return the stock in E2.
If D2 is blank, return an empty string.
If no exact match exists, return Not found. Preserve zero stock.
Explain the duplicate-ID risk and do not silently replace all errors.

=IF(D2="","",XLOOKUP(D2,$A$2:$A$4,$B$2:$B$4,"Not found",0))

The final zero requests an exact match. Microsoft’s XLOOKUP documentation↗ (opens in a new tab) explains matching behavior and version availability. It also describes the default search from the first item: duplicate identifiers can therefore produce a plausible first result instead of exposing a data problem.

For this example, SKU-2 should return the number zero; SKU-9 should return Not found; an empty D2 should appear empty. If duplicates are possible in your real sheet, decide whether to reject them, show a warning, or combine their values before you approve a formula.

Which tests catch a believable but wrong formula?

Write expected results before asking AI to evaluate its answer. Otherwise, the same misunderstanding can produce both the formula and its apparently reassuring test results. Run the tests in Excel; a written prediction alone is not a successful calculation.

Also test your actual import problems: text stored where numbers should be, extra spaces in identifiers, and missing statuses. Decide whether to clean those values or reject them. Ask AI to explain the consequence of that choice instead of silently changing the calculation rule.

What should you do when Excel rejects the formula?

Give the assistant the exact formula, the error shown, your Excel version, and a tiny sample that reproduces the problem. Say what you expected. Do not simply ask it to fix the spreadsheet: that leaves the original assumptions unexamined.

Check function spelling, separators, sheet names, range sizes, and copied references. Microsoft’s formula overview↗ (opens in a new tab) explains how references work. Avoid covering every error with a blank result; an empty-looking cell can conceal a broken reference as easily as a missing match.

For choosing software, use the separate Excel AI tools guide. The process here is about specifying and checking a formula, regardless of which approved assistant drafts it.

How to do this with fenbs

1. Create a free account and add a project for the workbook or recurring report.
2. Save the formula request in Skills and prompts, with fields for sheet layout, Excel version, and expected examples.
3. Record approved calculation rules in Decisions and rules, such as whether credits reduce totals and duplicate identifiers are allowed.
4. Add a context note describing the workbook structure and test cases, without uploading confidential rows into the project record.
5. Connect a supported assistant through integrations. Ask it to read those rules before proposing a formula, then save the testing outcome and next step.

fenbs does not calculate Excel formulas or replace workbook testing. It is not the right fit if your only need is one isolated calculation. It is free for one person, no credit card, and helps when several sessions or assistants need the same project context. Find more reusable patterns in the AI prompts hub.