Prompt for Writing an Excel Formula

Why context — the layout and the app — decides everything

A formula is only correct relative to the sheet it runs on, so a request without context forces the AI to guess. If you ask for "a formula to total sales by region" without saying where your data lives, the model invents a layout: it assumes region is in column B and sales in column C, that your data starts on row 2, that there are no merged cells or stray totals. When any of those guesses is wrong, the formula points at the wrong cells and returns a wrong number or a reference error. The template above removes the guessing by asking you to describe the columns and the starting row. Once the model knows that dates are in A, region in B, and sales in C starting on row 2, it can write ranges that match your actual sheet instead of a hypothetical one. Layout is not a nice-to-have detail; it is the thing that makes the formula land on the right data.

Excel and Google Sheets are not the same app

People treat Excel and Google Sheets as interchangeable, and for basic arithmetic they are, but the function libraries diverge in ways that break formulas. Newer functions such as XLOOKUP, LET, and dynamic-array spilling behave differently or are unavailable depending on the app and version. Google Sheets has functions Excel lacks, like ARRAYFORMULA, QUERY, and GOOGLETRANSLATE, while Excel has features Sheets handles differently. Even shared functions can differ in argument order or separators — some locales use semicolons instead of commas. This is why the template asks you to state the app up front. Telling the model "this is Google Sheets" or "this is Excel 365" changes which functions it reaches for and prevents the loop of pasting a formula, getting a "name error," and discovering the function does not exist in your app. If you are on an older Excel, say so, because it rules out the newest functions entirely.

Blanks, errors, and edge cases are where formulas quietly fail

A formula that works on clean sample data often breaks the moment it meets a real sheet, because real sheets contain blanks, text where numbers should be, and lookups that find no match. A lookup with no match returns an error that cascades through everything downstream, so wrapping it in IFERROR to show a blank or a friendly default keeps the sheet readable. Blanks deserve an explicit decision: should an empty cell count as zero, or be ignored entirely? The two choices produce different averages and totals, and getting it wrong skews the result silently. Division formulas need protection against dividing by zero. The template names these concerns directly — handle blanks, wrap errors, decide zero versus empty — so the model reasons about them instead of writing a formula that only survives the happy path. Deciding this deliberately is far cheaper than debugging a dashboard that shows the wrong average because blanks were counted as zeros.

Always test the formula on real sample rows

Even a well-specified formula should be verified before you trust it across thousands of rows. Ask the model to walk through what the formula returns for two or three concrete example rows, including at least one edge case — a blank, a no-match, or a boundary date. This turns the explanation from a claim into a check you can confirm. Then paste the formula into a copy of your sheet and compare a handful of results against numbers you calculate by hand. Spreadsheets are unforgiving because a subtly wrong formula does not error; it just reports a confident wrong number that propagates into charts and decisions. The assumptions note the template requests — "assuming blanks count as zero and the date column is real dates, not text" — is where those silent errors get caught. Read it, test the formula, and only then fill it down the column.

Why this prompt works

AI writes wrong formulas when it guesses your columns or the wrong app. This prompt supplies the layout, the app, and the goal, so the formula points at your real cells, uses functions that actually exist in Excel or Sheets, and handles the blanks and errors that break spreadsheets in practice.

How to customize it

• Describe your columns and starting row so ranges match your sheet.
• State whether it is Excel or Google Sheets; functions differ.
• Test the formula on a few sample rows before filling it down.

Prompt variations to try

Explain an existing formula

You are a spreadsheet tutor. Explain this [Excel / Google Sheets] formula in plain English, step by step:
[paste formula]
Describe what each function and argument does, what the result looks like, and call out any edge cases or bugs you notice, such as how it handles blanks, errors, or no-match lookups. Assume I know basic spreadsheets but not this formula.

Fix a broken formula

You are a spreadsheet expert. This [Excel / Google Sheets] formula is not working:
[paste formula]
App: [Excel version / Google Sheets]. Sheet layout: [list the columns and starting row]. Error or wrong result I see: [describe]. Find the problem, give me the corrected formula, and explain what was wrong (e.g. a reference off by one row, a function that does not exist in this app, or a mishandled blank). Confirm it works on a couple of sample rows.

Convert Excel and Google Sheets

Convert this formula from [Excel / Google Sheets] to [Google Sheets / Excel]:
[paste formula]
Replace any functions that do not exist in the target app with the correct equivalent (for example XLOOKUP, ARRAYFORMULA, QUERY, or a different date handling), adjust argument separators if needed, and confirm it returns the same result. List each change you made and why, and flag anything with no direct equivalent.

Common mistakes to avoid

• Not describing the sheet layout. Without your real columns and starting row, the AI guesses ranges and the formula points at the wrong cells or errors out.
• Not saying which app you use. Functions like XLOOKUP, ARRAYFORMULA, and QUERY differ between Excel and Google Sheets, so a formula for one may fail on the other.
• Ignoring blanks and errors. A no-match lookup or a blank cell can break the whole column. Ask for IFERROR and decide whether blanks count as zero or are ignored.
• Assuming dates are dates. If a column holds dates stored as text, comparisons silently fail. Confirm the format or convert with DATEVALUE before filtering by date.
• Filling the formula down without testing. A subtly wrong formula reports a confident wrong number rather than an error. Verify it on a few sample rows first.

Frequently asked questions

Does the AI need to know my exact column layout?

Will an Excel formula work in Google Sheets?

How do I stop a formula from showing errors?

Can I trust the formula without checking it?

Tip: replace the parts in [square brackets] with your own details before you send. The more specific you are — audience, tone, goal, constraints — the better the AI output.