This Excel formula prompt is for anyone who has to do arithmetic in a spreadsheet every month but does not want to memorize functions: say what you need to calculate and which columns the data sits in, paste a few rows of examples, and the AI returns a formula you can paste straight in, explaining each function step by step. State the version and the range first, and the formula will not throw errors in your sheet.
Copy this Excel formula prompt and add your request
You are a spreadsheet formula assistant. Write a formula using only the range and request given below. Do not change the original data, and do not guess at columns you were not given.
Software and version: {{software_and_version}}
Data range: {{data_range}}
Request: {{requirement}}
Sample data:
{{sample_data}}
Please output: 1. One formula starting with =, ready to paste, with references matching the sample; 2. An explanation, left to right, of what each function and reference is calculating; 3. How blank cells, text, or error values will be handled, and say directly if any are not handled; 4. If there is a simpler way to write it, give one alternative and say when the results would differ.
Requirement: use only the columns mentioned, do not invent fields; every function must work in the version given, and if one does not, say so directly.What to put in the brackets decides whether the formula works straight away
| Bracket content | How to fill it in | Example |
|---|---|---|
| {{software_and_version}} | Say whether you use Excel or WPS, and roughly which version | Excel 365 |
| {{data_range}} | Say what each column holds and which rows the data runs over | Column A names, column B sales, rows 2 to 51 |
| {{requirement}} | Say in one sentence what to calculate and on what condition | Calculate commission in bands from the sales in column B: 5% on the part above 10,000 yuan |
| {{sample_data}} | Paste 3 to 5 rows in their real format, and state the result you want | Zhang San · 12,000 yuan · wanted result 100 yuan |
The request line is the one most worth taking care over. Writing only "help me calculate commission" leaves the AI to guess the rules; writing "no commission on sales up to 10,000 yuan, 5% on the part above" makes the conditions and the threshold in the formula match your own method. It helps to include one boundary row in the sample, such as exactly 10,000 yuan, so the AI is less likely to mix up greater than and greater than or equal to when writing the condition.
If the formula errors after pasting, check these three places first
- References drifting: check whether the column letters and row numbers in the formula are the ones from your sample. When you paste down a whole column, relative references move with it; put a $ before a column letter to fix that column.
- Version not supported: a newer function errors outright in an older version, on another computer too. Put the software and version in the prompt, and the AI will avoid functions you cannot use.
- Blanks producing errors: blank cells, text, and error values mixed into a number column can make a formula return an error or calculate wrongly. Ask the AI to state in its output how such values are handled, then decide whether to clean the data first.
A formula only does the arithmetic; it does not check the result for you. After pasting, test one or two rows against the sample data, and only drag it down the whole column once they match. Tables holding ID numbers or salary details should be de-identified before pasting, and keep a separate copy of the original data.
