Google Sheets Formula Generator Prompt
Generate Google Sheets formulas, ARRAYFORMULA expressions, and Apps Script snippets from plain English descriptions.
Prompt Template
You are a Google Sheets power user and formula expert. I need a Google Sheets formula (or set of formulas) to do the following: **What I want:** [describe in plain English, e.g., look up a product price from another sheet based on product name, calculate a running total, flag rows where the date is overdue] **My data layout:** - Sheet name: [name] - Key columns: [e.g., Column A = dates, Column B = product names, Column C = quantities, Column D = prices] - Data starts at row: [e.g., row 2 (row 1 is headers)] - Number of rows: [approximate, e.g., ~500 rows, growing weekly] **Constraints:** - [e.g., Must work with ARRAYFORMULA for auto-expanding / Needs to handle blank cells / Should ignore errors from missing lookups / Must be a single formula, no helper columns] Provide: 1. The formula with clear explanation of each part 2. Where to place it (which cell) 3. If the formula is complex, break it down step by step 4. Common gotchas or edge cases to watch for 5. A simpler alternative if the optimal formula is hard to maintain
Example Output
Formula: Conditional Running Total by Category
**Goal:** Running total of amounts in column D, grouped by the category in column B.
Formula (place in E2):
=ARRAYFORMULA(IF(B2:B="","",MMULT((ROW(B2:B)>=TRANSPOSE(ROW(B2:B)))*(B2:B=TRANSPOSE(B2:B)),D2:D)))
How it works:
1. ROW(B2:B)>=TRANSPOSE(ROW(B2:B)) — matrix where each row includes itself and all previous rows
2. B2:B=TRANSPOSE(B2:B) — filters to only rows matching the same category
3. MMULT — multiplies the matrices to sum column D for matching rows up to the current row
4. IF(B2:B="","") — blanks empty rows
Simpler alternative (no ARRAYFORMULA):
=SUMPRODUCT((B:B2=B2)*(D:D2))
Drag down manually. Simpler but does not auto-expand.
Watch out for:
- MMULT slows beyond ~2,000 rows
- Blank cells in D cause errors — wrap in IF(D2:D="",0,D2:D)
Tips for Best Results
- 💡Describe your data layout — column letters, sheet names, header row — so the formula references your actual cells.
- 💡If your sheet grows over time, ask for ARRAYFORMULA versions that auto-expand to new rows.
- 💡For complex lookups, mention whether you need exact match or approximate — it changes VLOOKUP's last parameter.
- 💡Ask for 'error handling' explicitly if your data has blanks or missing values — IFERROR wrapping is often needed.
- 💡For very large sheets (10,000+ rows), ask the AI to suggest QUERY() or Apps Script alternatives to heavy ARRAYFORMULA chains.
Frequently Asked Questions
What is the Google Sheets Formula Generator Prompt prompt?
Generate Google Sheets formulas, ARRAYFORMULA expressions, and Apps Script snippets from plain English descriptions. It's a free ChatGPT prompt template from our Coding collection — copy it, fill in the bracketed variables, and paste it into your AI tool.
Which AI tools work with this prompt?
It's written and tested for ChatGPT, Claude and Gemini. Any AI assistant that accepts free-form text prompts will handle it well.
How do I customize this ChatGPT prompt?
Replace the bracketed variables — such as [name], [e.g., row 2 (row 1 is headers)] — with your own details before running it. Describe your data layout — column letters, sheet names, header row — so the formula references your actual cells.
Is this prompt free to use?
Yes. Every prompt on PromptAtlas is free to copy, customize, and use — no signup required.
Related Prompts
Python FileNotFoundError Debugging Prompt
Debug Python FileNotFoundError from the exact traceback, resolved path, working directory, filesystem context, deployment environment, and failing operation.
Python ZeroDivisionError Debugging Prompt
Debug Python ZeroDivisionError from the exact traceback, operand values and types, data path, numeric semantics, runtime context, and failing input.
Python IndentationError Debugging Prompt
Debug Python IndentationError from the exact parser message, whitespace, block structure, source bytes, formatter settings, and runtime version.
Python SyntaxError: Invalid Syntax Debugging Prompt
Debug Python SyntaxError invalid syntax from the exact parser location, surrounding tokens, Python version, generated code, execution context, and minimal reproduction.
Python AttributeError: Object Has No Attribute Debugging Prompt
Debug Python AttributeError from the exact traceback, runtime object type, attribute contract, construction path, version, imports, and minimal reproduction.
Python UnboundLocalError Debugging Prompt
Debug Python UnboundLocalError from the exact traceback, binding scope, assignments, branches, closures, imports, runtime path, and minimal reproduction.