Help me build a Google Sheets solution for [GOAL]. My sheet has this structure: [SHEET STRUCTURE]. Here are representative rows, including awkward cases: [SAMPLE DATA]. The exact result I need is: [EXPECTED RESULT]. My locale and constraints are: [LOCALE AND CONSTRAINTS]. First, restate the input columns, output location, and calculation rule in plain language. Then provide: 1. The recommended formula, Query, pivot-table setup, or Apps Script approach. Prefer a normal formula when it is readable and maintainable; use Apps Script only when formulas cannot meet the requirement. 2. The exact cell where to place it and whether it spills into adjacent cells. Use absolute and relative references deliberately, and say which parts should move when copied down or across. 3. A plain-English explanation of each important function or clause. 4. A worked result for the supplied sample rows, including blanks, errors, duplicate records, or dates if they matter. 5. One alternative only if there is a meaningful tradeoff, such as a simpler helper-column version versus one array formula. Use Google Sheets syntax, not Excel-only functions or unsupported table references. Do not assume column order, date parsing, decimal separator, named ranges, permissions, or add-ons that I have not supplied. Avoid volatile functions unless necessary. If an error value may appear in source data, handle it intentionally rather than masking all errors. Before answering, self-check the formula against each sample row and confirm that ranges are aligned to the same starting row and height. If a required input is missing, ask up to 3 clarifying questions; otherwise, provide the solution.
Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [GOAL] | Describe the calculation, cleanup, lookup, report, or automation you need. | Return each active customer’s most recent paid invoice amount from the Invoices sheet. |
| [SHEET STRUCTURE] | List sheet names, headers, starting rows, and any existing formulas or named ranges. | Customers!A2:A contains customer IDs and B contains status. Invoices!A2:A contains customer IDs, B contains invoice dates, C contains amount, D contains payment status. |
| [SAMPLE DATA] | Paste a few representative rows, including blanks or unusual cases. | Customers: C-101 active; C-102 inactive; C-103 active. Invoices: C-101, 2026-08-02, 1200, paid; C-101, 2026-09-10, 900, paid; C-103, 2026-09-14, 500, void. |
| [EXPECTED RESULT] | State what should appear and where, with one or more expected examples. | In Customers!C2:C, show 900 for C-101, blank for inactive C-102, and blank for C-103 because it has no paid invoice. |
| [LOCALE AND CONSTRAINTS] | Enter your spreadsheet locale and constraints such as formula-only, no helper columns, or refresh behavior. | United States locale; formula only; a single formula copied down is acceptable |
How to use
- Paste headers, sheet names, and 3-8 sample rows that include the kinds of blanks or exceptions you expect.
- Say where the result should appear and whether the formula will be copied or should spill automatically.
- Test the answer in a duplicate sheet first, then verify the stated sample outputs and one real row.
- Follow up with: “Adapt the formula so it returns ‘Needs review’ instead of blank when there are multiple matching latest records.”
Variations
Formula repair
Use this when an existing formula returns an error or wrong result.
Fix this Google Sheets formula: [FORMULA]. It is in [CELL], and the relevant sheet structure is [STRUCTURE]. It should produce [EXPECTED RESULT], but it currently produces [ACTUAL RESULT] for these examples: [SAMPLE ROWS]. Explain the specific reason it fails, then give the corrected formula and a brief breakdown of its parts. Preserve the original output shape and copy-down behavior unless I say otherwise. Do not use Excel-only syntax. Self-check range dimensions, blank handling, and each sample row before answering.
Dashboard metric
Use this to calculate a trustworthy KPI for a reporting sheet.
Design a Google Sheets metric for [METRIC] on a dashboard. Source sheet structure: [SOURCE COLUMNS]. Define the metric precisely using [BUSINESS RULES], including exclusions and date boundaries. Provide the exact formula or QUERY, placement instructions, and three sample calculations from [SAMPLE DATA]. State how blanks, refunds, duplicates, and late-arriving records are treated. Use [LOCALE] syntax and avoid volatile formulas if a fixed range or helper column is more reliable. Before answering, self-check that the numerator, denominator, and date criterion use compatible ranges.
Data cleanup
Use this when imported sheet data needs normalization.
Create a Google Sheets cleanup approach for [DATA TYPE] in [SOURCE RANGE]. The raw examples are [RAW EXAMPLES], and the standardized output must follow [OUTPUT RULES]. Give a formula for [OUTPUT CELL] that safely handles blanks, malformed values, and already-clean values. Explain what it will not safely infer and flag those cases instead of silently guessing. Use Google Sheets functions available in [LOCALE]. Include expected outputs for every sample. Self-check that the formula does not convert valid text into an unintended date or number.
Tips
- Write the output for a few sample rows before requesting a formula; this exposes ambiguous business rules such as which duplicate wins.
- Keep source columns and formula ranges aligned from the same first data row, especially inside FILTER, SUMIFS, and array formulas.
- Use helper columns for complicated transformations when the one-cell formula is difficult to audit or debug.
- Confirm your spreadsheet locale before pasting formulas, because argument separators and date interpretation can differ.
FAQ
Can ChatGPT create a formula from a screenshot?
It can help, but copied headers, cell references, and sample values are more reliable than a screenshot. Include the sheet names and desired output.
When should I use QUERY instead of a formula?
Use QUERY for grouped, filtered table results. Use regular formulas for row-level calculations, lookups, and logic that must copy with each record.
Will a formula work in Excel too?
Not always. This prompt asks for Google Sheets syntax, so verify compatibility separately if the workbook will move between tools.