Practical guides
How to write Excel formulas with AI
Provide the cell layout, Excel version and expected result. Then test the suggested formula in a copy of your workbook before filling it through real data.
On this page
Give the AI a formula problem it can inspect
In Chat AI App, paste a small table with its column letters, row numbers and calculation rule. State your Excel version and ask for one formula, a plain-language explanation and expected results for sample rows. Enter the formula yourself in a copy of the workbook and compare its result with a manual calculation. A plausible formula is a proposal until Excel and your checks agree.
You can also attach a workbook with + → Files. Wait for processing and check the attachment’s coverage information. For debugging, paste the existing formula as text as well: an extracted cell value does not necessarily reveal the expression that produced it. This chat workflow does not control an open Excel session or automatically update your live sheet.
1. Specify the layout and business rule
Include the sheet name, exact ranges, headers and destination cell. Explain whether the formula should total several matching rows, retrieve one record or calculate a result for each row. Those tasks require different logic even when they use the same table. State what blanks, duplicates and unmatched values should mean.
Provide a few representative rows instead of pasting an unexplained workbook. Include a row that should match and one that should not. Remove information the calculation does not need. For broader cleaning and reconciliation, use the Excel analysis guide first.
Version is part of the problem. Microsoft documents SUMIFS for Excel 2016 and later versions listed on its support page. Its XLOOKUP documentation explicitly says XLOOKUP is unavailable in Excel 2016 and 2019. Ask for a compatible alternative when those are your target versions, including when colleagues will open the workbook.
2. Start with a fictional six-row table
The following original example represents six fictional orders, not customer data or a recorded app execution. Put the headers in A1:D1 and the records in rows 2–7 on a worksheet called Orders. Amounts are plain numbers in USD. Each order ID occurs once.
| A: OrderID | B: Region | C: Status | D: Amount |
|---|---|---|---|
| O101 | West | Paid | 120 |
| O102 | East | Paid | 85 |
| O103 | West | Pending | 60 |
| O104 | West | Paid | 40 |
| O105 | East | Paid | 110 |
| O106 | West | Paid | 75 |
The task is to total amounts only when Region is West and Status is Paid. Before asking AI, identify the qualifying records: O101, O104 and O106. Their amounts total 120 + 40 + 75 = 235. O103 must be excluded because its payment is pending.
3. Use a prompt with an expected answer
I use [Excel version/platform] with [formula language] and [comma/semicolon]
argument separators. On Orders, A1:D1 contains OrderID, Region, Status, Amount.
The six records below occupy rows 2–7. Each ID is unique; amounts are USD numbers.
Write one formula for F2, on the same sheet, that sums Amount only when
Region is West AND Status is Paid. Do not include Pending orders.
Explain each range and criterion. Show the qualifying order IDs and the result.
My independent expected total is 235. If your result differs, explain why.
Include checks for no matching rows and a changed status. Use no macros.
[Paste the six records]
For English Excel using commas, the formula is:
=SUMIFS(D2:D7,B2:B7,"West",C2:C7,"Paid")
The first range supplies amounts; the next two range-and-criterion pairs test region and status. All ranges cover the same six rows. Microsoft requires matching range dimensions. Replacing C2:C7 with C3:C8 would also misalign the records, even though the ranges have equal lengths.
For a reusable selector, enter West in H2 and Paid in I2, then use =SUMIFS(D2:D7,B2:B7,H2,C2:C7,I2). Decide which references should stay fixed before copying the formula elsewhere. A formula intended for one summary cell does not need to be filled down through every order.
4. Distinguish a lookup from a total
A second request might be “return the amount for order O104”. In an Excel version with XLOOKUP, use:
=XLOOKUP("O104",A2:A7,D2:D7,"Not found",0)
The expected result is 40. The final 0 explicitly selects an exact match, and an absent ID such as O999 returns “Not found”. Microsoft describes XLOOKUP’s default search as returning the first match. If IDs repeat, do not assume it totals them or checks uniqueness; define the duplicate rule before accepting a lookup.
These examples answer different questions. Using XLOOKUP to retrieve the first West order would not calculate total paid West revenue. Ask the AI to explain why its chosen function matches your requested operation, not just whether the syntax looks valid.
5. Test the result before expanding the range
- Baseline: The conditional total is 235, and the O104 lookup is 40.
- Include a changed record: In a scratch copy, change O103 from Pending to Paid. The conditional total should become 295.
- Exclude a changed record: Restore O103, then change O106 to Pending. The total should become 160.
- No matches: Restore the data, then use North as the region criterion. SUMIFS should return 0 for this example.
Restore the baseline after each independent test. These checks make the intended rule visible; they do not prove that an arbitrary real workbook is clean. Check how new rows will be included, and inspect the actual formula in the destination cell rather than trusting a screenshot of its displayed value.
6. Report an error precisely
If Excel rejects the expression, send the exact formula, error, version, separator convention and a small reproducible table. Keep quote marks straight when copying. A #NAME? result may point to an unsupported or misspelled function; an unexpected zero warrants checking labels, numeric values and the selected ranges.
Do not ask AI merely to “fix it” repeatedly. Say which test failed and what you expected. Once the formula works, keep its description beside the workbook’s instructions so another reader knows the inclusion rule. If you download a generated workbook, open it in the intended Excel version and repeat these checks there too.
Common questions
Do I need to upload my whole workbook?
Why does an AI formula work for someone else but not for me?
Can Chat AI App insert formulas into my open Excel workbook?
Sources and further reading
- Microsoft: SUMIFS syntax, criteria and aligned ranges
- Microsoft: XLOOKUP syntax, matching and version availability