How to use AI to plan spreadsheet formulas
Using AI to plan spreadsheet formulas starts with a rule written in plain language. The tool can turn that rule into a proposed formula, but you need to check references, formatting and results before applying it to an entire table.
In this exercise, we will calculate the final value of fictional orders. You can follow the method in the spreadsheet application you already use, without relying on a particular AI integration.
1. Describe the columns and the rule
Imagine a table with quantity in column B, unit price in C and discount in D. We want to multiply quantity by price and then apply the discount to that subtotal.
Define each field before requesting a formula. Also specify that the discount is stored as a percentage, such as 10%, to avoid confusion with the number 10.
| Field | Fictional example | Meaning |
|---|---|---|
| B2 | 3 | Number of items |
| C2 | 20 | Price per item |
| D2 | 10% | Discount on the subtotal |
| E2 | 54 | Expected result |
Microsoft’s overview of Excel formulas explains elements such as operators and references. It is a useful syntax reference if that is your application.
2. Specify your working environment
Tell the AI which application and language settings you use. Function names and separators can vary between applications or regional settings.
Asking for “a discount formula” is not enough. Specify the first data row, the fields involved and what should happen when information is missing.
Help me plan a formula for my spreadsheet.
B2 contains quantity, C2 contains unit price and D2 contains a percentage discount.
E2 should show quantity × price × (1 − discount).
Explain the references and use the example 3, 20 and 10%, which should return 54.
Before suggesting functions, ask which application and regional settings I use.
Do not decide how to handle empty fields without asking.3. Test the proposal in a copy
For the rule described here, a simple arithmetic expression is =B2*C2*(1-D2). This is a starting point for the exercise, not a universal formula for every commercial policy.
Try a zero discount, zero quantity and a 100% discount. These cases help reveal whether the implementation matches the rule you wrote.
Consider negative quantities and discounts above 100% separately. Those situations require a business decision rather than an assumption made by the model.
4. Check what happens across rows
After validating one row, copy the formula to a few test rows. Check whether references move as expected and whether any rate should remain fixed.
A formula that works in E2 can behave differently when copied. Ask for an explanation of relative and absolute references before introducing a shared value used across several rows.
Do not copy the formula across the original table until those checks pass. Keeping a small test area makes it easier to see which change introduced a problem.
5. Record the approved decision
Keep the rule, examples and chosen formula near the spreadsheet. Another person can then review the calculation without having to reconstruct the entire AI conversation.
When the rule changes, repeat the tests before replacing the column. Treat a formula as approved only after comparing its result with an independent calculation.
To understand why a convincing answer still needs checking, read what an LLM is.
Frequently asked questions
Is an AI-suggested formula ready to use?
Treat it as a proposal. Check the rule, references and examples with known results before applying it to the original spreadsheet.
Should I specify my spreadsheet application?
Yes. Function names and separators can vary by application and regional settings.
How can I test a discount formula?
Use simple values with known results and check cases such as zero discount, zero quantity and a 100% discount. Define separately how to handle cases outside the rule.