Spreadsheet formula helper prompt: free ChatGPT template
Updated · By Robert Breen
Use this when you know what you want a sheet to do but not the formula: count the late orders, total a column by month, flag rows past a date. Describe your sheet exactly, with real column letters, header names and a few sample rows, and the AI can write a formula that fits your sheet instead of a generic one. The prompt also asks for a plain explanation and a test you can run, because a formula that looks right can quietly count the wrong rows.
Always try a new formula on a copy of the sheet first, especially one other people use.
The prompt
Copy it into ChatGPT (or Claude, Gemini or Copilot) and replace every [BLANK] with your own details. It uses the four parts from Prompt Writing 101: Role, Context, Task and Format.
Role: You are a patient spreadsheet expert who explains formulas to people who use spreadsheets every day but never learned functions. Context: I use [APP]. My sheet is called [SHEET NAME]. The columns, with letters and header names: [COLUMNS]. A few sample rows exactly as they appear: [SAMPLE ROWS]. What I want: [GOAL]. Task: Write one formula for my exact columns and say which cell it goes in. Explain each part in plain words, one line per part. Give a small test: which sample rows it should count and the answer I should see. Name one common way it breaks, like dates stored as text. If my goal is unclear, ask one question instead of guessing. Format: The formula on its own line, then the headings "What each part does", "Test it" and "Watch out for". Plain words.
Fill in the blanks
[APP]- Google Sheets or Excel. Some functions work differently between them.
[SHEET NAME]- The tab name, exactly as written, since formulas that point to other tabs need it.
[COLUMNS]- Each column letter with its header, such as "A Vendor, B Order, E Due Date".
[SAMPLE ROWS]- Three to five real-looking rows with private details changed. Include an awkward one, like a blank cell.
[GOAL]- What the formula should do, in plain words, such as "count follow-ups due before today".
Example, filled in
A made-up example from the free lesson Build an AI Vendor Follow-Up Agent in n8n. Ridgeline Supply Co. is the made-up warehouse business from the operations agent lesson, where an AI agent saves vendor follow-ups to a Vendor Follow-Ups sheet. The operations manager wants a count of follow-ups that are past due.
Role: You are a patient spreadsheet expert who explains formulas to people who use spreadsheets every day but never learned functions. Context: I use Google Sheets. My sheet is called Vendor Follow-Ups. The columns, with letters and header names: A Vendor, B Order, C Issue, D Follow-up Message, E Due Date, F Status (I added this one, it says Open or Done). A few sample rows exactly as they appear: Tallpine Pallet Co., PO 4471, 5 days late, (message), Sep 29, Open. Corrugo Box Supply, PO 4480, No ship date yet, (message), Sep 30, Done. Northwind Wrap, PO 4492, Shipped 12 rolls short, (message), Sep 29, Open. One row has a blank Due Date. What I want: one cell at the top of a Summary tab that shows how many follow-ups are still Open and have a due date before today. Task: Write one formula for my exact columns and say which cell it goes in. Explain each part in plain words, one line per part. Give a small test: which sample rows it should count and the answer I should see. Name one common way it breaks, like dates stored as text. If my goal is unclear, ask one question instead of guessing. Format: The formula on its own line, then the headings "What each part does", "Test it" and "Watch out for". Plain words.
What a good answer looks like
- A COUNTIFS formula that points to the Vendor Follow-Ups tab, checks Status for "Open" and compares Due Date with TODAY().
- The explanation says what each piece does in a sentence, such as "only counts rows where F says Open".
- "Test it" names the Tallpine and Northwind Wrap rows and gives an answer of 2, if today is after September 29.
- A warning that dates typed as "Sep 29" with no year may be stored as text and get skipped.
How to check it before you use it
An AI draft can sound right and still say something you never told it. Use the habit from the Reply Faster lesson: check every promise, name, date and number against what you gave it before anything goes out.
- Make a copy of the sheet (File, then Make a copy) and try the formula there first.
- Filter the sheet by hand to the same rows and compare the count. If the numbers differ, find out why before using it.
- Click a date cell and check it is a real date, not text. Format, then Number, then Date fixes most cases.
- Paste any error message, like #N/A or #VALUE!, back into the chat with the formula and ask what it means.
Practice it in the free lesson
Build an AI Vendor Follow-Up Agent in n8n: Build an AI agent for Ridgeline Supply Co. that writes follow-up messages for late or pending purchase orders and saves them to Google Sheets — chat trigger, AI Agent, system prompt, model, memory, and the Sheets tool. You do every step yourself in a practice copy of n8n, and nothing touches your real accounts.
Also useful: Build an AI Agent That Categorizes Business Expenses · n8n AI Agent Tutorial: Save Social Media Ideas to Google Sheets
Terms used here
Related prompts
- Data cleanup plan prompt: Turn messy spreadsheet columns into clean, consistent ones: a column-by-column plan with standard values, rules for odd entries…
- Google Sheets columns plan prompt: Plan the Google Sheets columns for an n8n automation: header names, what goes in each, who fills it, and which must match your…
- AR aging summary prompt: Turn an accounts receivable aging report into a one-page summary for the owner: what is overdue, who owes the most, and which…
More reports and planning prompts
- Survey results summary prompt: Summarize survey responses into counted themes with exact quotes, standout comments and follow-up questions, without overstating…
- Vendor quote comparison prompt: Compare vendor quotes side by side on price, what's included, timing and warranty, with "Not in quote" for gaps and the…
- Weekly leads summary prompt: Paste a week of form leads from your Google Sheet and get counts by type, who is still waiting, patterns with row numbers and…