Excel Formula Prompt Generator
Describe your data layout and goal, and get a formula prompt that accounts for your actual Excel version and real edge cases.
Describing a spreadsheet task in plain language and getting back a working formula depends on the model knowing your actual column layout, not just the general goal — a formula referencing the wrong column letters is a common, avoidable failure. This tool builds a prompt that includes your real column layout alongside the task description.
How to use it
Which columns hold what data -- real column letters or headers, not a general description.
The actual task -- a lookup, a conditional sum, a calculation across columns.
Includes your real layout, producing a formula that references the correct columns.
Tips for better results
- State whether you're using Excel or Google Sheets. Some functions and syntax differ meaningfully between the two.
- Give real column letters or header names, not vague descriptions. A specific column reference produces a formula that works on the first try more reliably than a vague one.
- Test the formula on a small sample before applying it broadly. Especially for conditional logic -- verify against a few rows you already know the correct answer for.
Example output
“Columns: A=Product, B=Units Sold, C=Price. Request: total revenue where units sold exceeds 100. Produces a formula referencing columns B and C correctly, not guessed placeholders.”
TL;DR
A generic “write me an Excel formula” request produces a formula built on guessed cell references and may lean on functions that do not exist in your specific Excel version — dynamic array functions like FILTER or UNIQUE simply are not available in older Excel, and Google Sheets has its own function names for several common tasks.
Why environment and edge cases matter more than the core formula
The underlying logic of a lookup or a conditional calculation is often similar across spreadsheet tools, but the exact function available to express that logic is not — a formula built assuming XLOOKUP will simply fail with a name error in an older Excel install that only has VLOOKUP, and a formula that assumes Excel’s syntax will not run as written in Google Sheets. Specifying the environment up front avoids the frustrating cycle of pasting a formula, getting an error, and going back to ask for a version compatible with what you actually have.
Edge cases matter just as much in practice — a lookup formula that works perfectly until it hits one blank cell or one code that is not in the reference table, then returns a cryptic error that breaks a report someone else is relying on. Naming the edge cases explicitly, rather than leaving the model to assume clean data, is what keeps a formula from looking correct in testing and then failing quietly in real use weeks later.
Describing a price-lookup formula across two sheets, with a missing-code edge case, on modern Excel, produces:
“Write a formula for Excel (Microsoft 365 / modern) that does the following: look up a price from another sheet based on a product code.
My data layout: Product codes in Sheet1 column A…
Handle these edge cases explicitly: product code not found in Sheet2…
Modern dynamic array functions (FILTER, UNIQUE, XLOOKUP, LET) are fine to use…
Wrap the result in IFERROR…”
That gives the model both the layout and the specific failure mode to guard against, rather than a formula that only works on clean data.
Using this across ChatGPT, Claude, and Gemini
All three generally handle common Excel functions well, but double-check any dynamic array or LET-based formula against your actual Excel build, since function availability has rolled out gradually across Microsoft 365 updates. Claude and ChatGPT both tend to explain formulas clearly step by step when asked, which is worth relying on if the generated formula is more complex than what you would have written unassisted.
FAQ
Will this work for Google Sheets too?
Yes — select Google Sheets as the environment, and the generated prompt asks for Google Sheets-specific function names and syntax where they differ from Excel.
What if I am not sure which Excel version I have?
If your Excel has functions like XLOOKUP or FILTER available in the formula bar autocomplete, you are on a modern version — otherwise, select the older Excel option to be safe.
Can this generate a formula involving multiple sheets and workbooks?
Yes — describe the cross-sheet or cross-workbook layout clearly in the data layout field, including exact sheet names if they matter.
Should I always wrap formulas in IFERROR?
Not always necessary, but it is worth doing for anything shared with other people or feeding into an actual report, where a raw error code sitting in a cell is far more confusing to a reader than a clear, plain-language fallback message.