MAX’S NOTES
How to ask AI for an Excel formula and check it before copying
Describe the cells, rules and expected answers. A practice inventory formula shows why boundary values, blank cells and copied references need separate checks.
To get a suitable Excel formula from AI, provide the cell addresses, the rule and examples with answers you already know. Test the proposed formula on a small copy of your data first. Excel accepting a formula does not mean it solves the problem you intended.
You do not have to share an entire work file. A simple formula can often be explained using column descriptions and fictional rows. The example below is designed for you to reproduce yourself.
Start with a rule you understand without Excel
Imagine a practice inventory of office supplies. Column A contains item names, B contains stock quantities and C will contain a status. Cell F1 contains a shared threshold of 5.
The rule is: show “Check” when stock is below the threshold, and “Enough” when stock equals or exceeds it. If the quantity is unknown and the B cell is blank, show “Clarify.”
Decide what happens exactly at the boundary. “Below five” and “five or fewer” are different rules. AI should not have to guess which one you mean.
For this practice example, F1 is numeric, and populated B cells contain only non-negative whole numbers. Text, spaces and error values need separate handling before using this example.
What to put in your AI prompt
Specify the application and version, function language and argument separator. The language you use in the chat does not describe every setting in your spreadsheet.
Next, give the data layout, the condition, the treatment of blank cells and the result location. If you will copy the formula, explain which references should move and which should stay fixed.
A reusable prompt
Help me write an Excel formula. My setup uses English function names and commas between arguments.
Column A contains item names, B contains stock quantities and C should show the result. Data starts in row 2. F1 contains a numeric threshold of 5.
If B2 is blank, return “Clarify.” If the numeric quantity is below F1, return “Check”; otherwise return “Enough.” Populated B cells contain only non-negative whole numbers.
Give the formula for C2 and explain each part. When copied down, the quantity reference should change rows, while the reference to F1 must stay fixed.
Show the expected results for quantities of 4, 5, 6, 0 and a blank cell. Do not suggest macros, external connections or changes to the input data.
This is a prompt template, not a statement about a particular AI product’s capabilities. Substitute your application’s settings; the exercise does not require buying a separate tool.
The formula for this example
For the specified English function names and comma separator:
=IF(B2="","Clarify",IF(B2<$F$1,"Check","Enough"))
The first condition handles a blank value. The nested IF compares the quantity with the threshold. Microsoft documents IF as selecting a result according to whether a condition is true; nesting conditions lets you handle them in sequence.
B2 refers to the current row. $F$1 fixes both the threshold’s column and row. Excel’s documentation explains that relative references change when a formula is copied, while absolute references stay fixed. Copying from C2 to C3 should therefore change the quantity to B3 and leave the threshold as $F$1.
If your application uses localized function names or a different separator, adapt this version. Do not replace characters throughout the file at random: establish the setting and test a single cell.
Check answers you already know
With F1 = 5, the expected outcomes are:
- Quantity 4 — “Check.”
- Quantity 5 — “Enough.”
- Quantity 6 — “Enough.”
- Quantity 0 — “Check.”
- Blank cell — “Clarify.”
These are expected answers derived from the task’s rule, not a report of running the formula in a spreadsheet application. Enter it in your own test copy and compare the actual results with the list.
Also copy it to the next row and inspect the addresses in the formula bar. Then temporarily change F1 from 5 to 7: a quantity of 6 should change to “Check.”
This can reveal a wrong comparison operator or an accidentally shifted threshold reference. A polished AI answer does not replace either check, however confidently it explains itself.
When the result does not match
Do not just ask AI to “fix the formula.” Provide the specific case: cell addresses and values, the current formula, the actual result and the expected result. Copy any error message exactly.
For example: “B2 contains the number 5 and F1 equals 5. I expect ‘Enough’ but get ‘Check.’ Find the mismatch with the rule ‘strictly below the threshold.’”
Do not hide every problem behind a blank result just to make the sheet look tidy. First establish whether the issue is in the condition, a reference or an input value.
Before moving to the working spreadsheet
Check an ordinary row, the boundary value, zero, a blank cell and copying down. If your real data includes negative quantities, numbers stored as text or spaces, this example no longer covers the whole task. Clarify the rule and add the relevant checks.
AI helps translate a clear rule into a formula. You should still understand the rule and the expected result. Start with a small copy and expand only after checking it.
Sources
- Microsoft Support — IF function
- Microsoft Support — Switch between relative, absolute, and mixed references