Ask an AI for "a formula to sum by region" and you get a formula that looks right and may not fit your sheet. The model cannot see your columns, your Excel version, or the dates that were imported as text. These 55 prompts are built around that gap. The formula, cleaning, SQL and pivot prompts ask you for the sheet structure (headers and a few sample rows, dummy or anonymised), tell the model to state its assumptions, and ask for a small test so you can confirm the answer before filling it down a thousand rows.
The pack is for analysts and everyday spreadsheet users who work in Excel, Google Sheets, SQL or Python. It has eight sections: formulas, data cleaning, pivot and summary tables, SQL and Python, exploration and statistical sense-checks, charts and dashboards, models and Power BI, and communicating results. Where a method has a classic trap (join fan-out, averaging averages, peeking at an A/B test, QUERY dropping mixed columns), the prompt names it and builds in a check.
Every prompt uses {{VARIABLES}} for your details and works in ChatGPT, Claude and Gemini. Four include a worked example with fictional data, including a SQL fix and a Power Query import. The prompts will not replace judgement: you still verify numbers against a total you trust.
Ten prompts for writing, debugging and understanding formulas. Each one asks for your app version, header names and a few sample rows, then states its assumptions and gives you a three-row test. They cover lookups (XLOOKUP, INDEX/MATCH), dynamic arrays, Google Sheets QUERY, dates, text parsing, nested IF clean-up, inherited formulas and Excel-to-Sheets translation. Where a function exists in only one app, the prompt tells the model to say so.
Best for: Getting a correct first formula without guessing at your columns
You are an Excel and Google Sheets specialist who writes formulas other people can maintain.
App and version: {{SPREADSHEET_APP}}
What the formula must do: {{GOAL_IN_PLAIN_ENGLISH}}
Column headers and three sample rows (dummy or anonymised): {{SHEET_STRUCTURE}}
Cell where the formula will live: {{TARGET_CELL}}
Write one formula. Rules:
- Use only functions available in my stated version. If the best function is newer, give it and also a fallback for older versions.
- Use my real column letters and header names. If my structure is ambiguous, ask me up to three questions before writing anything.
- State every assumption you made (blank cells, text stored as numbers, duplicates, upper or lower case).
- Prefer clear over clever. Don't nest more than three levels without saying why.
Output: (1) the formula in a code block, (2) two sentences on how it evaluates, (3) a test: the result I should see for each of my sample rows, (4) one edge case that would break it and how to guard against it.
Fill in: SPREADSHEET_APP GOAL_IN_PLAIN_ENGLISH SHEET_STRUCTURE TARGET_CELL
Paste real header names and a few dummy rows; the test line lets you confirm the formula before filling it down. Use dummy or anonymised data, or a tool your employer has approved for company data.
Best for: Finding silent wrong answers and #N/A, #VALUE! and #REF! errors
You are a spreadsheet auditor who finds why a formula quietly returns the wrong answer, not just why it errors.
App and version: {{SPREADSHEET_APP}}
The formula, exactly as typed: {{BROKEN_FORMULA}}
What I expected vs what I get (include the error code if any): {{EXPECTED_VS_ACTUAL}}
Relevant headers and sample rows: {{SHEET_STRUCTURE}}
Work through it in this order:
1. Restate in plain English what the formula currently does, step by step, so I can see if it matches my intent.
2. List the likely causes ranked by probability: text-formatted numbers, trailing spaces, wrong match mode, absolute versus relative references, ranges that stop short, hidden rows, regional list separators, volatile or circular references.
3. For each cause, give a one-cell check I can run to confirm or rule it out.
4. Only after the checks, give a corrected formula and say which cause it fixes.
If you cannot tell from what I gave, say so and name the one extra piece of information you need. Do not claim a cause is certain unless my sample proves it.
Fill in: SPREADSHEET_APP BROKEN_FORMULA EXPECTED_VS_ACTUAL SHEET_STRUCTURE
Run the checks in order before applying the fix; it is often the data, not the formula. Use dummy or anonymised data, or a tool your employer has approved for company data.
Best for: Choosing and writing lookups that don't break on messy keys
You are a spreadsheet specialist who chooses the right lookup for the file the user actually has.
App and version: {{SPREADSHEET_APP}}
Lookup table (sheet name, headers, three sample rows): {{LOOKUP_TABLE}}
Where the key values live and what I want pulled back: {{LOOKUP_GOAL}}
Known data problems (duplicates, missing keys, mixed text/number IDs): {{KNOWN_ISSUES}}
Do this:
1. Recommend XLOOKUP, INDEX/MATCH or VLOOKUP for my situation and give two sentences on why. If my version lacks XLOOKUP, say so and don't use it.
2. Write the formula with exact match, using whole-column references only if my version handles them efficiently; otherwise bounded ranges.
3. Wrap it so a missing key shows a clear message rather than #N/A, and explain what that hides.
4. Show how to return several columns at once if it applies.
5. Tell me how to find keys that fail to match (a one-cell count I can run).
State your assumptions. End with a three-row test using my sample.
Fill in: SPREADSHEET_APP LOOKUP_TABLE LOOKUP_GOAL KNOWN_ISSUES
Say what your data problems are; most lookup failures are duplicates or text-versus-number IDs. Use dummy or anonymised data, or a tool your employer has approved for company data.
Best for: Building live filtered or de-duplicated lists without helper columns
You are a spreadsheet expert in array formulas, and you know the two apps differ.
App and version: {{SPREADSHEET_APP}}
Source data (headers and three sample rows): {{SHEET_STRUCTURE}}
The live list I want to produce: {{OUTPUT_GOAL}}
Conditions that decide which rows appear: {{CONDITIONS}}
Write a formula that produces the list. Rules:
- Use only functions my app has. FILTER, SORT, UNIQUE and LET exist in both Excel 365 and Google Sheets. SORTBY and TAKE are Excel 365 only at the time of writing, CHOOSECOLS exists in both, and SORTN and ARRAYFORMULA are Google Sheets only. If I use Excel 2019 or older, there are no dynamic arrays, so say so and give the closest older approach.
- Say how the result spills (Excel spills automatically; Google Sheets needs an empty area below and to the right) and what must stay empty.
- Make the sheet show a clear message when no rows match.
- Use LET to name repeated pieces only if it improves readability.
- Explain how to reference the whole spilled result from another formula in my app.
Output: the formula, a line-by-line explanation, and a test: which of my sample rows should appear, in what order. Tell me to confirm in my own sheet that each function is available in my version.
Fill in: SPREADSHEET_APP SHEET_STRUCTURE OUTPUT_GOAL CONDITIONS
Say whether you use Excel 365 or Google Sheets; the function lists differ.
Best for: Answering a question about a Sheets range with SQL-like syntax
You are a Google Sheets specialist who writes QUERY formulas using the Google Visualization query language.
Sheet range and headers (with column letters) and three sample rows: {{SHEET_STRUCTURE}}
The question I want answered: {{QUESTION}}
How many header rows the range has: {{HEADER_ROWS}}
Write a QUERY formula. Rules:
- Refer to columns by letter (A, B, C) when the data is a normal range; Col1, Col2 apply only when the data is an array expression such as a curly-brace join. Say which one you use.
- Remember QUERY guesses the type of each column; warn me if any of my columns mix text and numbers, because that makes values vanish.
- Show how to use dates correctly (the date literal syntax) if dates are involved.
- Explain each clause (select, where, group by, order by, label, limit) in one line.
- If QUERY is the wrong tool for this question, say so and give FILTER or a pivot table instead.
Output: the formula, the clause explanation, a short test with expected rows from my sample, and the single most likely reason it would return an empty result. Ask me before answering if the structure is unclear.
Fill in: SHEET_STRUCTURE QUESTION HEADER_ROWS
QUERY silently drops mixed-type columns, so list any column that has both text and numbers.
Best for: Tenure, ageing, deadlines and working-day calculations
You are a spreadsheet specialist who understands how Excel and Google Sheets store dates as serial numbers and where date maths goes wrong.
App and version: {{SPREADSHEET_APP}}
The calculation I need (for example age, tenure, days overdue, month-end, working days between): {{DATE_TASK}}
Sample date cells exactly as they appear, including how they are formatted: {{SAMPLE_DATES}}
Weekend pattern and holiday list location, if relevant: {{CALENDAR_RULES}}
Do this:
1. First check my sample dates: say whether each looks like a real date value or text, and how I can tell with one test cell.
2. Write the formula. Use functions such as DATEDIF, EOMONTH, NETWORKDAYS.INTL, WORKDAY, or DATE as appropriate, and explain why you chose them.
3. Say whether the result counts the start day, the end day, both or neither, and how to change that.
4. Warn me about regional date format traps (day-month versus month-day) and time zones if they apply.
State your assumptions. Finish with a test using three of my sample dates and the exact result I should see for each. If my sample is not enough to decide, ask me first.
Fill in: SPREADSHEET_APP DATE_TASK SAMPLE_DATES CALENDAR_RULES
Include the dates exactly as displayed; text dates are the usual culprit.
Best for: Pulling codes, names and numbers out of free-text cells
You are a spreadsheet specialist in text functions. LEFT, MID, FIND, SUBSTITUTE and TEXTJOIN work in both Excel and Google Sheets; TEXTSPLIT, TEXTBEFORE and TEXTAFTER are Excel 365 only; SPLIT is Google Sheets only; REGEXEXTRACT and REGEXREPLACE exist in Google Sheets and in recent Microsoft 365 builds, so check your build.
App and version: {{SPREADSHEET_APP}}
Five example cells showing the messy input and, beside each, the result I want: {{INPUT_OUTPUT_EXAMPLES}}
Anything that varies between rows (missing parts, extra spaces, different separators): {{VARIATIONS}}
Write the formula. Rules:
- Work out the pattern from my examples and state it in one sentence before writing the formula. If the examples are inconsistent, point to the conflicting row and ask me.
- Use the simplest functions my app and version support. If a function that would be simpler is missing from my app, give the fallback.
- Handle leading and trailing spaces and non-breaking spaces.
- Show what the formula returns for a row that doesn't match the pattern, so errors are visible instead of silently wrong.
Output: the pattern statement, the formula, a table of my five examples with the result the formula gives for each (mark any that differ from what I wanted), and one row type I should test that I haven't mentioned.
Fill in: SPREADSHEET_APP INPUT_OUTPUT_EXAMPLES VARIATIONS
Give input and desired output side by side; the model infers the rule from them.
Best for: Replacing unreadable nested IFs with something maintainable
You are a spreadsheet reviewer who refactors tangled conditional logic into something a colleague can edit safely.
App and version: {{SPREADSHEET_APP}}
The current formula, exactly as typed: {{CURRENT_FORMULA}}
In plain English, the business rule it is supposed to implement: {{BUSINESS_RULE}}
How often the thresholds or categories change: {{CHANGE_FREQUENCY}}
Do this:
1. Explain what the current formula does, branch by branch, and flag any branch that can never be reached or any gap where no branch applies.
2. Compare my options: IFS, SWITCH, a lookup against a small rules table on its own sheet, or leaving it as is. Recommend one given how often the rules change.
3. Write the refactored version, and the rules table layout if you recommend one.
4. List the boundary values I should test (just below, exactly at, and just above each threshold) and the result each should give.
If my plain-English rule disagrees with the formula, show the disagreement instead of silently choosing one. State assumptions and tell me what I still need to confirm with whoever owns the rule.
Fill in: SPREADSHEET_APP CURRENT_FORMULA BUSINESS_RULE CHANGE_FREQUENCY
Check the boundary tests against the rule's owner, not against the formula.
Best for: Understanding a legacy formula before touching it
You are a patient spreadsheet teacher helping me understand a formula someone else wrote before I change it.
App and version: {{SPREADSHEET_APP}}
The formula: {{FORMULA_TO_EXPLAIN}}
What the cell is meant to show, if I know: {{CELL_PURPOSE}}
Names, ranges or other sheets it refers to, with what each contains: {{REFERENCED_RANGES}}
Explain it for someone comfortable with SUM and IF but not with advanced functions:
1. A one-sentence summary of what it calculates.
2. A step-by-step breakdown from the innermost function outward, each step with a made-up input and result.
3. What each function argument does, and any argument that looks unusual or risky (hard-coded numbers, whole-column ranges, volatile functions like INDIRECT or OFFSET).
4. Anything that would break it if someone inserts a row, sorts the data or renames a sheet.
5. A plain-English rewrite, and a cleaner formula only if there is a real improvement.
Where my description of the ranges is not enough to be sure, say what you are assuming and tell me which cell to inspect to confirm. Do not pretend to know contents of sheets I haven't described.
Fill in: SPREADSHEET_APP FORMULA_TO_EXPLAIN CELL_PURPOSE REFERENCED_RANGES
Use before editing a model you did not build.
Best for: Moving workbooks between Excel and Google Sheets without breaking logic
You are a spreadsheet migration specialist fluent in both Excel and Google Sheets.
Source app and version: {{SOURCE_APP}}
Target app and version: {{TARGET_APP}}
The formula or formulas to convert: {{FORMULAS_TO_CONVERT}}
Any named ranges, tables or add-ins the formulas rely on: {{DEPENDENCIES}}
For each formula:
1. Give the converted formula in a code block.
2. Say whether the conversion is exact, approximate or impossible. If approximate, name the difference in behaviour (for example how blanks, text numbers, error handling or array spilling differ).
3. Flag functions that exist only in one app (for example Excel structured table references and TAKE; Sheets QUERY, IMPORTRANGE, ARRAYFORMULA and SPLIT) and give the closest alternative, or say there is none.
4. Note locale separators: commas versus semicolons.
Finish with a short test plan: three inputs I should try in both apps and the matching outputs. State assumptions, and don't claim a function exists in a version you aren't certain about; tell me to check the official function list for my exact version.
Fill in: SOURCE_APP TARGET_APP FORMULAS_TO_CONVERT DEPENDENCIES
Run the same three test inputs in both apps and compare.
Cleaning & Preparing Messy Data
Most analysis time goes on preparation, so these prompts plan the cleanup before you change anything. They cover duplicates, inconsistent labels, text dates and numbers, split columns, missing values, input validation and repeatable Power Query imports. Every one assumes you keep an untouched copy of the raw data and check row counts afterwards.
Best for: Planning a cleanup so you fix causes in the right order
You are a data quality analyst. I will give you a small sample of a messy table; produce a cleaning plan before anyone touches the data.
Source of the data and what it represents: {{DATA_DESCRIPTION}}
Headers and about ten sample rows (keep the mess in): {{SAMPLE_ROWS}}
What the cleaned data will be used for: {{DOWNSTREAM_USE}}
Tool I will use: {{CLEANING_TOOL}}
Produce:
1. A defect log: every problem you can see in the sample (inconsistent spelling, stray spaces, mixed formats, placeholder values like N/A or 9999, merged cells, duplicate-looking rows, impossible values), each with the row number as evidence.
2. For each defect, the proposed fix, whether it is safe to automate or needs a human decision, and the risk if I get it wrong.
3. A fix order that avoids rework.
4. Checks to run after cleaning (row counts, totals, distinct values) so I can prove nothing was lost.
Only report defects visible in the sample; mark anything that is a guess as "suspected". Tell me to keep an untouched raw copy and never overwrite the original.
Fill in: DATA_DESCRIPTION SAMPLE_ROWS DOWNSTREAM_USE CLEANING_TOOL
Keep the mess in your sample; a pre-cleaned sample hides the problems.
Best for: Deduplicating customer, product or transaction lists without losing data
You are a data analyst who treats duplicate removal as a decision, not a button.
Table description and what one row should represent: {{ROW_MEANING}}
Headers and sample rows including suspected duplicates: {{SAMPLE_ROWS}}
Tool: {{TOOL}}
Which record to keep when duplicates differ (newest, most complete, highest value): {{KEEP_RULE}}
Do this:
1. Define "duplicate" for this table: which columns together identify the same real-world thing. Offer two definitions (strict and loose) and say what each would catch or miss in my sample.
2. Classify the suspected duplicates in my sample as exact, near (spelling, spacing, case), or different things that merely look alike.
3. Give step-by-step instructions in my tool to flag duplicates first, review them, and only then remove or merge. Include a formula or query to count duplicates before and after.
4. Explain what happens to values in the rows that get dropped, and what I lose.
Do not delete anything in your advice before a flag column exists. List what I must check by hand, and ask me before assuming a keep rule I haven't stated.
Fill in: ROW_MEANING SAMPLE_ROWS TOOL KEEP_RULE
Flag first, delete second, and keep a copy of the original.
Best for: Collapsing inconsistent category, vendor or job-title labels
You are a data standardisation specialist.
Column to standardise and what it contains: {{COLUMN_DESCRIPTION}}
The distinct values currently in it, one per line, with a count if I have it: {{DISTINCT_VALUES}}
The master list of allowed values, if one exists: {{ALLOWED_VALUES}}
Produce a mapping table with three columns: original value, standard value, confidence (high, medium, low). Rules:
- Group variants that clearly refer to the same thing (case, punctuation, abbreviations, typos).
- If I gave an allowed list, map to it only; send everything else to "Needs review" rather than inventing a category.
- If no list exists, propose a short one and explain how you chose it.
- Never merge two values that could be genuinely different (for example two similarly named branches or products); mark them "Ask owner".
After the table, give (1) a count of how many original values collapse into each standard value, (2) a lookup formula or find-and-replace method to apply the mapping at scale, and (3) a list of the low-confidence rows I must confirm. Finally tell me how to keep the mapping table so new messy values get caught next month.
Fill in: COLUMN_DESCRIPTION DISTINCT_VALUES ALLOWED_VALUES
Paste distinct values rather than whole rows; it keeps sensitive data out.
Best for: Fixing text dates, currency symbols and regional number formats
You are a data cleaning specialist for dates and numbers that arrive as text.
Source system or how the file was produced (CSV export, copy from PDF, manual entry): {{SOURCE_OF_DATA}}
Ten example values from the problem column, exactly as they appear: {{EXAMPLE_VALUES}}
Locale or region the data came from: {{LOCALE}}
Tool: {{TOOL}}
Do this:
1. Classify the example values into the formats you see (for example 03/04/2026, 3 Apr 2026, 2026-04-03, 1,234.50, 1.234,50, $1,200, (500), 12%).
2. Point out any value that is ambiguous (03/04 could be 3 April or March 4) and say what extra information would settle it. Don't guess silently.
3. Give conversion steps in my tool: a formula, Power Query step, or short script, whichever fits, handling each format.
4. Provide an error-catching check: a formula that flags any cell that did not convert, so failures can't hide.
5. Warn me about Excel's own automatic conversions on import (gene-like names becoming dates, leading zeros dropped) and how to import safely.
Finish with a test: the converted value I should see for each of my ten examples, and a mark on any that need human judgement.
Fill in: SOURCE_OF_DATA EXAMPLE_VALUES LOCALE TOOL
List ambiguous dates separately and settle them from the source, not by guessing.
Best for: Splitting names, addresses and codes into usable columns
You are a data preparation specialist who splits combined fields reliably.
The column and what it holds (for example full address, "Surname, First", product code with size and colour): {{COLUMN_DESCRIPTION}}
Eight sample values, including awkward ones, and the fields I want out: {{SAMPLES_AND_TARGET_FIELDS}}
Tool: {{TOOL}}
Do this:
1. Describe the pattern or patterns you can see, and list the sample values that break the main pattern.
2. Choose a method (Text to Columns, a formula, Flash Fill, Power Query split, or a short script) and justify it. Prefer repeatable methods over one-off ones, and say why Flash Fill is not repeatable.
3. Give exact steps or formulas, including how to handle missing parts and extra separators.
4. Show a table of my samples with the output fields as your method would produce them, marking any that come out wrong.
5. Suggest a validation: a count of rows where the number of parts is not what I expect.
Do not guess how to split ambiguous values (for example a three-word surname); put them on a "needs review" list. Remind me to work on a copy of the column.
Fill in: COLUMN_DESCRIPTION SAMPLES_AND_TARGET_FIELDS TOOL
Include the nasty cases in your samples, such as missing parts and extra separators.
Best for: Deciding what to do about blanks before they skew your analysis
You are an analyst who is careful about what missing data means.
Dataset and what it records: {{DATASET_DESCRIPTION}}
A per-column summary: column name, data type, percent blank, and what blank might mean (not collected, not applicable, zero, error): {{COLUMN_SUMMARY}}
The analysis I plan to run: {{PLANNED_ANALYSIS}}
For each column with missing values:
1. Propose the likeliest reason for the gaps, labelled as a hypothesis, and one question I could ask the data owner to find out.
2. Say whether the gaps look random, related to other columns, or related to the missing value itself, because that changes which treatment is safe. Then recommend a treatment: leave blank, label as unknown, fill with a constant, fill from a related column, impute, or drop the rows. Give the cost of each option for my planned analysis (bias, loss of sample, false precision).
3. Say which treatment is reversible and which is not.
Then give a short rule of thumb for my case, a way to record which values were filled (a flag column), and a before-and-after check I can run on my headline numbers to see how much the treatment moved them.
Don't give percentage cut-offs as though they were rules. If I haven't provided enough to decide, tell me what to check. Never fill in values for me.
Fill in: DATASET_DESCRIPTION COLUMN_SUMMARY PLANNED_ANALYSIS
A flag column recording filled values lets you undo or test the decision later.
Best for: Designing entry sheets that reject bad data at the source
You are a spreadsheet designer building an input sheet that other people will fill in.
App and version: {{SPREADSHEET_APP}}
Columns on the entry sheet, with what each should contain: {{COLUMN_SPEC}}
Who will fill it in and how careful they are likely to be: {{USERS}}
Mistakes that have already happened: {{PAST_ERRORS}}
For each column, specify:
1. The validation rule (list, whole number, decimal range, date range, text length, or custom formula) with the exact settings or formula to enter.
2. The error alert style (stop, warning or information) and the message text, written in plain, helpful language.
3. An input prompt, if it helps.
4. A conditional formatting rule that highlights entries that pass validation but still look wrong (for example a date far in the future).
Also cover: how to build dropdown lists from a separate list sheet so they update, how to protect formula cells, and the limits of validation (pasted values bypass it, so suggest a check column). Finish with a short checklist to test the sheet by deliberately entering five bad values.
Fill in: SPREADSHEET_APP COLUMN_SPEC USERS PAST_ERRORS
Base the rules on the mistakes that have really happened, not hypothetical ones.
Best for: Replacing monthly copy-and-paste cleanup with a refreshable query
You are a Power Query (M) and Excel data-prep specialist.
Source type and location pattern (CSV folder, Excel sheet, web export, SharePoint): {{SOURCE_TYPE}}
Headers and five sample rows from the raw file, including the junk at the top or bottom: {{RAW_SAMPLE}}
The tidy table I want at the end, with column names and types: {{TARGET_TABLE}}
How the file changes from month to month: {{CHANGES_BETWEEN_FILES}}
Write the transformation as numbered Applied Steps. For each step, give the ribbon command to click and the M code line, and say what could go wrong with it.
Cover: removing header and footer junk, promoting headers, setting data types after the text clean-up (and reviewing the type step Power Query adds automatically after promoting headers), trimming and cleaning text, unpivoting wide months into rows, filtering out subtotal rows, and adding an error-check column.
Then explain how to make it survive next month's file: avoiding hard-coded column names where possible, using a folder query for multiple files, and what breaks if the source adds or renames a column. Note which steps need Excel 2016 or later or Power Query in Microsoft 365, and tell me to verify menu names in my version.
Fill in: SOURCE_TYPE RAW_SAMPLE TARGET_TABLE CHANGES_BETWEEN_FILES
If your source is a folder of monthly files, say so; the structure changes.
Example output · Claude (example written by The Prompt Index)
Sample inputs (fictional)
- SOURCE_TYPE: Folder of monthly CSV exports, one file per month.
- RAW_SAMPLE: Row 1 "Regional Sales Report"; row 2 "Exported 03-Oct-2026"; row 3 headers Product,Jan,Feb,Mar; rows: Widget,120,135,90 / Gadget,80,75,110 / Total,200,210,200.
- TARGET_TABLE: Product (text), Month (text), Units (whole number), plus a Check column.
- CHANGES_BETWEEN_FILES: Month columns differ in each file, products are added or dropped, the junk rows stay at the top.
Applied steps (numbered; M code shown for a single-file query)
1. Source. Data > Get Data > From Text/CSV, UTF-8. Risk: wrong delimiter.
Source = Csv.Document(File.Contents("C:\Reports\Sales_2026-03.csv"), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])
2. Remove the top junk. Home > Remove Rows > Remove Top Rows, 2. Risk: if a file ever has three junk rows, this silently removes the wrong rows, so the check below matters.
RemoveTop = Table.Skip(Source, 2)
3. Promote headers. Transform > Use First Row as Headers. Risk: Power Query may add a type-change step right after; review it.
Promote = Table.PromoteHeaders(RemoveTop, [PromoteAllScalars=true])
4. Trim text. Transform > Format > Trim on Product. Doing this before the filter means " Total " with stray spaces is still caught. Risk: non-breaking spaces survive Trim.
Trimmed = Table.TransformColumns(Promote, {{"Product", Text.Trim, type text}})
5. Drop the Total row and blanks. Filter Product to exclude "Total". Risk: a product that is literally named "Total".
NoTotal = Table.SelectRows(Trimmed, each [Product] <> null and [Product] <> "Total")
6. Unpivot. Select Product, then Transform > Unpivot Other Columns. Risk: none for new months, because it does not name them.
Unpivot = Table.UnpivotOtherColumns(NoTotal, {"Product"}, "Month", "Units")
7. Set types, after cleaning. Month = Text, Units = Whole Number. Risk: a value that is not a number, such as "n/a", becomes an error cell (a blank stays null), and the error propagates into any column that reads it.
Typed = Table.TransformColumnTypes(Unpivot, {{"Month", type text}, {"Units", Int64.Type}})
8. Error-check column. Add Column > Custom Column. try ... otherwise catches the error, so errors and blanks both show "Check"; testing [Units] = null alone would return an error for an "n/a" cell, not "Check".
Check = Table.AddColumn(Typed, "Check", each if (try [Units] otherwise null) = null then "Check" else "OK")
Surviving next month
Replace the single file with Get Data > From Folder, Combine & Transform, and put the Skip/Promote steps in the sample-file transform, so every file gets them. Step 6 survives new or renamed month columns because it names only Product. It breaks if the Product column is renamed, if the junk row count changes, or if a month column holds text such as "n/a" (that row shows Check; the Units cell itself still holds an error, so filter or fix it before loading to a PivotTable). Reconcile after refresh: the sum of Units should equal the sum of the Total row (610 in this sample: 200 + 210 + 200 = 120+135+90+80+75+110).
Versions: these steps work in Excel 2016 and later and in Microsoft 365; menu names vary slightly, so check the ribbon in your version.
Pivot Tables & Summary Tables
Design a pivot for a real question, build SUMIFS summaries that update, rank and Pareto your items, compare periods without mixing partial and full months, and reconcile two lists that should agree. Each prompt includes a check that the table adds back to a total you trust.
Best for: Turning a question into a pivot layout and checking it is not misleading
You are an analyst who designs pivot tables that answer one question clearly.
Business question I need answered: {{BUSINESS_QUESTION}}
Source table headers with data type for each, and three sample rows: {{SHEET_STRUCTURE}}
Approximate number of rows and whether data is added regularly: {{DATA_SIZE_AND_REFRESH}}
App and version: {{SPREADSHEET_APP}}
Give me:
1. A pivot layout: which field goes in Rows, Columns, Values and Filters, and which summary (sum, count, average, or a distinct count, which needs the Excel Data Model or COUNTUNIQUE in Sheets) for each value, with a sentence on why.
2. Preparation: any column that needs fixing first (dates stored as text, blank headers, merged cells) and how to turn the data into a Table or named range so the pivot grows.
3. Click-by-click steps for my app.
4. Grouping advice for dates, ages or numeric bands.
5. Two ways the pivot could mislead (blanks counted as zero, averaging averages, double counting joined data) and a check for each against a total I can calculate separately.
If my question can't be answered from the columns listed, say what column is missing instead of improvising. Ask first if the structure is unclear.
Fill in: BUSINESS_QUESTION SHEET_STRUCTURE DATA_SIZE_AND_REFRESH SPREADSHEET_APP
Reconcile the pivot grand total with a number you trust before presenting it.
Best for: Building summary tables that update as data is added
You are a spreadsheet specialist who builds summary tables that stay correct when data is added.
App and version: {{SPREADSHEET_APP}}
Data sheet: headers and three sample rows: {{SHEET_STRUCTURE}}
The summary I want (rows, columns, measure, and any filters): {{SUMMARY_SPEC}}
Data range expectations (will rows be added, are there blanks or totals inside the range): {{RANGE_NOTES}}
Do this:
1. Propose the layout of the summary table with the row and column labels coming from the data (for example UNIQUE) or a typed list, and say which is safer here.
2. Write the one formula that goes in the top-left cell and can be filled right and down, with correct absolute and mixed references. Explain each criterion.
3. Show how to handle criteria with wildcards, dates between two cells, and "not equal" or "not blank".
4. Add a reconciliation row that proves the table sums to the total of the data, and tell me what it means if it doesn't.
5. Name two reasons these formulas return zero when the data looks right.
State assumptions. End with a three-cell test.
Fill in: SPREADSHEET_APP SHEET_STRUCTURE SUMMARY_SPEC RANGE_NOTES
Use the reconciliation row every time; criteria mismatches fail silently.
Best for: Finding the products, customers or causes behind most of the total
You are a sales-and-operations analyst who finds the few items that account for most of the result.
Data headers and three sample rows: {{SHEET_STRUCTURE}}
The item to rank and the measure to rank it by: {{ITEM_AND_MEASURE}}
How many to show and how to handle ties: {{TOP_N_AND_TIES}}
App and version: {{SPREADSHEET_APP}}
Provide:
1. The steps to aggregate the data to one row per item first (pivot or formula), because ranking raw transaction rows is a common mistake.
2. A formula for rank that handles ties the way I asked, and a Top N list formula that updates automatically.
3. A Pareto calculation: cumulative share of the total, sorted from largest, with the formula for each column.
4. How to chart it as a bar-plus-line Pareto and what the chart will and won't show.
5. Plain-English caveats: ranking by revenue ignores margin; a long tail can matter; the 80/20 shape is common but not a law, so report the actual split in my data rather than assuming it.
End with a test showing the expected top three from my sample.
Fill in: SHEET_STRUCTURE ITEM_AND_MEASURE TOP_N_AND_TIES SPREADSHEET_APP
Aggregate to one row per item before ranking.
Best for: Month-on-month and year-on-year tables that don't compare unlike periods
You are an analyst building a comparison that a manager can read in ten seconds.
Data headers and three sample rows, including the date column format: {{SHEET_STRUCTURE}}
The measure and the comparison I want (month on month, same month last year, quarter to date versus plan): {{COMPARISON_SPEC}}
How the calendar works for this business (calendar months, fiscal year start, 4-4-5 weeks): {{CALENDAR_RULES}}
App and version: {{SPREADSHEET_APP}}
Do this:
1. Define each period precisely and say how partial periods will be handled, since comparing a half-finished month to a full one misleads.
2. Give the helper columns or formulas that assign each row to a period and, if needed, a fiscal period.
3. Build the comparison table: current, prior, absolute change and percentage change, with formulas, and the rule for when the prior value is zero or blank (show "n/a", not an error or 100%).
4. Add a check that both periods contain the same number of trading days or flags when they don't.
5. Offer a chart suggestion in one sentence.
Mention one trap: percentages of small bases. State your assumptions.
Fill in: SHEET_STRUCTURE COMPARISON_SPEC CALENDAR_RULES SPREADSHEET_APP
Settle fiscal-calendar rules up front; they change every number.
Best for: Matching two exports, ledgers or inventories and listing the differences
You are a reconciliation specialist for two spreadsheets that should agree but don't.
List A: name, headers and three sample rows: {{LIST_A}}
List B: name, headers and three sample rows: {{LIST_B}}
The key that should match (single column or combination), and the amount or field that should also agree: {{MATCH_KEY_AND_FIELDS}}
Acceptable tolerance for differences (for example rounding): {{TOLERANCE}}
Give me a method, not a guess about my data:
1. Preparation: how to make keys comparable (trim, case, leading zeros, number-versus-text) and a helper-column formula that builds a combination key if needed.
2. Formulas, or in Excel a Power Query merge, that classify every row as: in both and agreeing, in both but different, only in A, only in B. Include the formulas.
3. A summary count table for the four classes and a check that A's count equals matched plus only-in-A, and likewise for B.
4. A short list of the usual causes of each kind of mismatch for me to investigate, written as hypotheses.
5. How to document what I decided about each exception.
Don't state which list is right; say how to find out.
Fill in: LIST_A LIST_B MATCH_KEY_AND_FIELDS TOLERANCE
The count identity in step 3 proves no rows fell through.
SQL, Python & Automation
For the point where a spreadsheet stops being the right tool. Write and debug SQL from a business question, use window functions, load and reshape data with pandas, port spreadsheet logic into code, and draft VBA or Apps Script with safety notes. They ask for your schema rather than guessing it.
Best for: Turning a question into a query you can check
You are a data analyst who writes clear, correct SQL and explains it.
SQL dialect (PostgreSQL, MySQL, SQL Server, BigQuery, SQLite, Snowflake): {{SQL_DIALECT}}
Table definitions: names, columns with types, keys, and how tables join: {{SCHEMA}}
The question in business terms: {{BUSINESS_QUESTION}}
Anything about the data I know is odd (nulls, duplicates, soft deletes, time zones): {{DATA_QUIRKS}}
Write the query. Rules:
- Use only tables and columns I listed. If something you need is missing, ask rather than invent a column name.
- Use explicit JOINs and say which kind and why. Point out if a join can multiply rows and how I should check for that.
- Handle NULLs deliberately and say where.
- Format it readably with a short comment for each CTE or non-obvious clause.
- State your assumptions about the definition of the metric (for example what counts as an active customer).
Output: the query, a plain-English walk-through, three sanity checks I can run to confirm the numbers (row count against the base table, a known total, a spot check of one record), and one way the result could be wrong. Remind me to try it on a small date range first and never to run untested queries that change data.
Fill in: SQL_DIALECT SCHEMA BUSINESS_QUESTION DATA_QUIRKS
Give types and join keys, not just table names, and test on a narrow date range first.
Best for: Finding why a query is wrong, slow or confusing
You are a senior SQL reviewer. I will give you a query that errors, runs slowly, or returns numbers I don't trust.
SQL dialect: {{SQL_DIALECT}}
The query: {{SQL_QUERY}}
Schema notes (columns, keys, approximate table sizes): {{SCHEMA_NOTES}}
What is wrong: the error text, or the result I expected versus what I got: {{PROBLEM}}
Do this:
1. Explain what the query does, clause by clause, in logical order of execution (FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY).
2. Identify the problem. For wrong numbers, check the usual suspects: join fan-out duplicating rows, filters on the outer table turning a LEFT JOIN into an inner join, COUNT versus COUNT(DISTINCT), NULL comparisons, integer division, time zone boundaries in date filters.
3. Give a corrected query, with the changed lines marked.
4. Provide a small check that would have caught the bug.
5. If it is slow, suggest likely causes (functions on indexed columns, missing filters, SELECT *) but tell me to check the real execution plan, because you cannot see my data or indexes.
If you need more information to be certain, say exactly what. Don't present a guess about performance as a fact.
Fill in: SQL_DIALECT SQL_QUERY SCHEMA_NOTES PROBLEM
Paste the actual error text or both numbers (expected and returned).
Example output · Claude (example written by The Prompt Index)
Sample inputs (fictional)
- SQL_DIALECT: PostgreSQL
- SQL_QUERY:
SELECT c.region, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.region;
- SCHEMA_NOTES: customers(customer_id PK, region). orders(order_id PK, customer_id, order_date, total) where total is the whole order value. order_items(item_id PK, order_id, product, qty). An order has one or more items.
- PROBLEM: North shows 350 but finance's report says 150. Check case: customer 7 (North) has order 1 (total 100, 3 items) and order 2 (total 50, 1 item).
1. What the query does, in logical order
FROM/JOIN: start with customers, attach each customer's orders, then attach every item row of each order. WHERE: keep orders dated 1 January 2026 or later. GROUP BY: one row per region. SELECT: add up o.total in each region.
2. The problem
Join fan-out. After the join to order_items there is one row per item, not per order. Order 1 now appears 3 times, so its total of 100 is added three times (300), and order 2 once (50): 350. The total is an order-level number being summed at item level. Your query does not use any item column, so that join only multiplies rows.
3. Corrected query (changed lines marked)
SELECT c.region, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
-- removed: JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.region;
If you also need item information, aggregate the items first in a subquery or CTE (one row per order_id, for example COUNT(*) AS item_count) and join that, so each order stays one row. Do not use SUM(DISTINCT o.total): two different orders with the same total would be collapsed and the answer would be silently wrong.
4. Check that would have caught it
Run SELECT COUNT(*), COUNT(DISTINCT o.order_id) on the joined rows before grouping. If the two numbers differ, the join has multiplied rows. For customer 7 you would see 4 and 2. Also compare the grand total with SELECT SUM(total) FROM orders WHERE order_date >= '2026-01-01'.
5. Performance
You did not report slowness, so I have not commented. If it is slow, check the real execution plan; I cannot see your indexes or table sizes.
Date note: if order_date is a timestamp, the >= filter is fine for a start date, but use a half-open range (< next day) for end dates.
Best for: Running totals, ranks and row-to-row changes without self-joins
You are a SQL tutor-analyst who uses window functions to solve real reporting problems.
SQL dialect: {{SQL_DIALECT}}
Table and columns (with types) and two or three sample rows: {{TABLE_SAMPLE}}
The result I want, for example running total, rank within group, previous-row difference, moving average, first or last value per customer: {{RESULT_NEEDED}}
Write the query. Then:
1. Explain PARTITION BY, ORDER BY and the frame clause (ROWS versus RANGE) as they apply here, with the plain-language meaning of each.
2. Say how ties are treated and whether ROW_NUMBER, RANK or DENSE_RANK fits, with why.
3. Show the expected output for my sample rows, computed by hand so I can compare with the real output.
4. Explain how to filter on a window result, since it can't go in WHERE, and show the pattern.
5. Mention a version difference if my dialect lacks a feature you used, and offer a fallback.
State assumptions. If the sample doesn't show the ordering column clearly, ask. Warn me about two traps: a running total with no ORDER BY in the window returns the partition total on every row, and with ORDER BY but no frame clause the default RANGE frame gives tied order values the same running total.
Fill in: SQL_DIALECT TABLE_SAMPLE RESULT_NEEDED
Compute the sample by hand once; it is the best way to learn the frame clause.
Best for: Writing pandas that loads, cleans and summarises with built-in checks
You are a Python data analyst who writes readable pandas code.
Python and pandas versions if known: {{PYTHON_ENVIRONMENT}}
File type and columns with types, plus three sample rows: {{DATA_STRUCTURE}}
What I want to end up with (a tidy table, a pivot, a grouped summary, a merged dataset): {{TARGET_RESULT}}
Known problems (mixed types, odd date formats, large file): {{KNOWN_ISSUES}}
Write a complete snippet. Rules:
- Use only the column names I gave. Ask me if any are unclear.
- Load with explicit dtypes and date parsing where relevant, and explain why.
- Prefer method chaining with clear step names; avoid chained assignment and inplace operations.
- Comment each step in plain English.
- Add assert or shape checks after merges and filters so a silent row explosion or loss is caught.
- Handle missing values explicitly and say how.
Output: the code, a table of what each step changes (row count and columns), a quick test on my three sample rows with the expected output, and a note about any pandas feature that depends on version. Tell me not to upload confidential files to online code tools I don't control.
Fill in: PYTHON_ENVIRONMENT DATA_STRUCTURE TARGET_RESULT KNOWN_ISSUES
Run the shape checks; row counts after a merge are the commonest silent error.
Best for: Moving a model from Excel into a script or database
You are an analyst who migrates spreadsheet logic into code without changing the answers.
Target: {{TARGET_LANGUAGE}}
The spreadsheet logic, described cell by cell or as formulas (for example a VLOOKUP, SUMIFS and an IF chain): {{SPREADSHEET_LOGIC}}
The columns involved, with types and three sample rows: {{DATA_STRUCTURE}}
Expected result for those sample rows, taken from the spreadsheet: {{EXPECTED_RESULT}}
Do this:
1. Restate the spreadsheet logic as numbered plain-English rules, so we agree on the intent before any code is written. Flag any rule that is ambiguous.
2. Write the code that implements the rules, with a comment tying each block to the original formula.
3. List behaviours that differ between spreadsheet and code: text numbers and implicit conversion, blank versus zero, case-insensitive matching in lookups, approximate-match defaults, and rounding.
4. Give a reconciliation test: run both on the same sample and compare to my expected result, row by row.
5. Say what I should verify on the full dataset, not just the sample.
Do not assume the spreadsheet is correct; tell me if any rule looks like it contains an error.
Fill in: TARGET_LANGUAGE SPREADSHEET_LOGIC DATA_STRUCTURE EXPECTED_RESULT
Reconcile against the spreadsheet's output on real rows before retiring the sheet.
Best for: Automating copy-paste routines with a script you can read
You are an automation developer who writes small, safe spreadsheet scripts.
Platform: {{PLATFORM}}
The task done by hand today, step by step: {{MANUAL_STEPS}}
Workbook layout (sheet names, header row, key columns, approximate row count): {{WORKBOOK_LAYOUT}}
Who will run it and how often: {{USER_AND_FREQUENCY}}
Write the script. Rules:
- Show only what is needed for the task; no feature extras.
- Work on a copy: the script must not overwrite source data, and writes results to a new sheet or clearly marked columns.
- Read ranges into an array and write back once, not cell by cell, for speed.
- Include simple error handling and a message that says what the script did.
- Comment each block.
Output: the code, steps to install and run it (where to paste it, how to enable macros or authorise a script), a safety checklist (back up the file, test on a dummy copy, review the code before you run it), and what I should check afterwards. Warn me that macros and scripts can change or delete data and that I should never run code I don't understand. If the task could be done with a built-in feature instead, say so.
Fill in: PLATFORM MANUAL_STEPS WORKBOOK_LAYOUT USER_AND_FREQUENCY
Always test on a copy of the file first.
Exploring Data & Statistical Sense-Checks
Plan an exploratory analysis around a decision, profile a dataset, pick an appropriate test, sanity-check an A/B result including its power, investigate outliers, test a correlation claim, build a cohort retention table, segment customers, analyse survey results, choose a simple forecast, interpret regression output, and read an uploaded file in a staged, checkable way. These prompts show their working and tell you what to verify in a tool you trust.
Best for: Starting an unfamiliar dataset with a plan tied to a decision
You are a data analyst planning the first day with an unfamiliar dataset.
Where the data came from and what each row represents: {{DATA_ORIGIN_AND_ROW_MEANING}}
Column list with types and a sentence on each: {{COLUMN_LIST}}
The decision this analysis will inform: {{DECISION_CONTEXT}}
Time available: {{TIME_AVAILABLE}}
Produce a plan, not results:
1. Questions to ask the data owner first (how it was collected, what is missing, what changed over time).
2. A data profile checklist: row count, unique keys, ranges, distributions, missingness, duplicates, and impossible values, with the formula, pivot or code to check each.
3. Five to eight analysis questions ranked by how much they bear on the decision, each with the columns needed, the simplest summary or chart that answers it, and what result would change the decision.
4. Pitfalls specific to my description (survivorship, selection, seasonality, mixed units).
5. A stopping rule: what I should have by the end of my time budget.
Do not invent findings or typical values for my data. If something in my description makes a question unanswerable, say so.
Fill in: DATA_ORIGIN_AND_ROW_MEANING COLUMN_LIST DECISION_CONTEXT TIME_AVAILABLE
Do this before opening any charts; it stops aimless digging.
Best for: Spotting suspicious columns from describe() or a pivot summary
You are a careful data analyst reviewing summary statistics for signs of trouble.
What the dataset is: {{DATASET_DESCRIPTION}}
Summary table for each column (count, missing, min, max, mean, median, standard deviation, distinct count; add top values for text): {{SUMMARY_STATS}}
Units and expected ranges I know of: {{EXPECTED_RANGES}}
Review each column and report:
1. Red flags: min or max outside plausible range, mean far from median (skew or outliers), counts that don't match the other columns, distinct counts suggesting IDs that aren't unique or categories that aren't consistent.
2. For each red flag, a hypothesis for the cause (data entry, unit mix-up, placeholder value like 0 or 9999, join error), labelled as a hypothesis, and the quick check to test it.
3. Columns that look fine, and why, in one line each.
4. Which columns I should not use for the planned analysis until the issues are resolved.
Only comment on what the numbers show. If I haven't supplied the stat you'd need, list it as a request instead of assuming. Do not estimate values for any column. Summary statistics can hide problems, so end by listing three visual checks (for example histograms) I should still run.
Fill in: DATASET_DESCRIPTION SUMMARY_STATS EXPECTED_RANGES
Paste summary statistics, not the raw rows; it is faster and keeps individual records out.
Best for: Picking a test you can defend and understanding its limits
You are an applied statistician who helps non-statisticians pick an appropriate method and understand its limits.
The question in plain English: {{RESEARCH_QUESTION}}
Variables involved, with type (numeric, categorical, ordered, date) and role (outcome, group, predictor): {{VARIABLES}}
How the data were collected (experiment, survey, system logs), sample size per group, and whether observations are independent or paired: {{DATA_COLLECTION}}
Tool I will use: {{TOOL}}
Do this:
1. Recommend a primary test or method and one alternative, with the assumptions each requires, and how to check those assumptions with my data.
2. Say what to do if assumptions fail (a non-parametric option, transformation, or bootstrap).
3. Explain exactly what the p-value and confidence interval will and will not tell me, in two plain sentences. Include effect size.
4. Give the steps or function names in my tool, without inventing function names; if unsure of an exact name in my version, tell me to look it up.
5. Say whether my sample size is enough to detect an effect of the size that matters, whether I am running many comparisons (which raises false positives), and the biggest threat to a causal reading given my collection method.
If the sample size or design looks too weak for the question, say so plainly. Recommend consulting a statistician for high-stakes decisions.
Fill in: RESEARCH_QUESTION VARIABLES DATA_COLLECTION TOOL
Describe how data were collected, not just what columns exist; the design drives the test.
Best for: Checking an experiment, and whether it was big enough, before you say it won
You are a sceptical experimentation analyst. Review my result before I report it.
What was changed and the outcome measured: {{TEST_DESCRIPTION}}
Numbers for each variant (units, conversions, or sum and spread of the metric): {{VARIANT_NUMBERS}}
Run length, assignment method, daily traffic, and anything else that changed in that period: {{TEST_CONDITIONS}}
The decision this feeds and the smallest change that would matter: {{DECISION_AND_THRESHOLD}}
Review in this order:
1. Recompute the rates and the difference from my numbers, showing the working.
2. Check validity: sample ratio mismatch, whole weekly cycles, early peeking, many metrics or segments, novelty effects.
3. Give the suitable test for my metric type and the confidence interval for the difference, and compare the interval with my threshold. Tell me to confirm the calculation in a tool I trust.
4. Power: state the smallest difference my sample could reliably detect (80 percent power, 5 percent two-sided unless I say otherwise), the sample per variant needed to detect my threshold, and that sample in days at my daily traffic.
5. State what I can conclude, in language a manager can repeat, and recommend: ship, extend to a pre-set sample size, rerun with a larger effect, or decide on other grounds.
Never call a result significant without the calculation.
Fill in: TEST_DESCRIPTION VARIANT_NUMBERS TEST_CONDITIONS DECISION_AND_THRESHOLD
Fix the stopping sample size before you start a test, not after you see results.
Example output · Claude (example written by The Prompt Index)
Sample inputs (fictional)
- TEST_DESCRIPTION: New checkout button wording (B) versus current wording (A). Outcome: completed purchase per visitor.
- VARIANT_NUMBERS: A: 5,000 visitors, 400 purchases. B: 5,000 visitors, 450 purchases.
- TEST_CONDITIONS: Ran Monday to the following Tuesday (9 days). Random assignment by visitor ID, intended 50/50. About 1,100 visitors a day in total. I looked on day 4 and nearly stopped. A promotional email went out on day 6.
- DECISION_AND_THRESHOLD: Roll out B if it improves the purchase rate by at least 0.5 percentage points.
1. Arithmetic
A: 400 / 5,000 = 8.0%. B: 450 / 5,000 = 9.0%. Difference +1.0 point. The standard error of the difference is about 0.56 points, so z is about 1.79 and the two-sided p-value is about 0.07.
2. Validity
- Split 5,000 / 5,000 matches the intended 50/50, so no sample ratio concern.
- Nine days is one full week plus Monday and Tuesday, so those days are over-weighted. Prefer whole weeks.
- You looked on day 4. That is fine if the stopping point was fixed in advance and you did not stop because it looked good.
- The day-6 email may have changed who visited. Randomisation protects the comparison, but the effect might not repeat in a normal week.
3. Interval
95% interval for B minus A: about -0.1 to +2.1 points. It includes zero and also your 0.5-point threshold. Please confirm in a tool you trust.
4. Power
With 5,000 per variant, this test can reliably detect (80% power, 5% two-sided) a difference of only about 1.6 points, so it could not have resolved a 0.5-point effect. To detect 0.5 points from an 8% baseline you need about 47,500 visitors per variant. At about 550 per variant per day that is about 86 days. Extending to 14 days (about 7,800 per variant) would only bring the detectable difference down to about 1.3 points.
5. What you can conclude and what to do
"B converted 1.0 point higher in this test, but the test was too small to separate that from chance, and it was never able to prove a 0.5-point gain." Options: (a) run to a pre-set 47,500 per variant if 0.5 points really matters; (b) raise the threshold to something this traffic can detect, such as 2 points (the detectable difference at this size is about 1.6); or (c) if the change is free and easily reversed, ship it on business grounds and say it is not a proven win. Do not extend by a few days and re-test; that adds a second look at the same data.
Best for: Deciding whether an extreme value is a mistake or the interesting part
You are an analyst who investigates outliers before deciding what to do with them.
Dataset, metric and unit: {{METRIC_AND_UNIT}}
The unusual values with their row context (id, date, group and neighbouring values; dummy or anonymised): {{OUTLIER_ROWS}}
The normal range as far as I know, and how the number is produced: {{NORMAL_RANGE_AND_PROCESS}}
Analysis the outliers will affect: {{AFFECTED_ANALYSIS}}
Do this:
1. Propose a short list of explanations for each outlier, split into error (entry, unit, duplicate, system glitch) and real (rare event, big customer, seasonal peak), with the clue in the row that supports each. Label all as hypotheses.
2. Give one check per explanation that I can run, or a question to ask the person who owns the data.
3. Compare rule options for detection (IQR fences, z-score, percentiles, domain limits) and warn where each fails, for example with skewed data.
4. Show the effect on my planned analysis of three treatments: keep, cap, remove. Recommend a way to report results both with and without the outliers.
5. Tell me how to document the decision.
Never recommend deleting a value just because it is extreme. If rows are not enough to judge, say so.
Fill in: METRIC_AND_UNIT OUTLIER_ROWS NORMAL_RANGE_AND_PROCESS AFFECTED_ANALYSIS
Keep the row context; an outlier without its neighbours can't be judged.
Best for: Stress-testing a finding before someone makes a decision on it
You are a critical-thinking analyst reviewing a claim that one thing drives another.
The claim or pattern I found: {{CLAIM}}
Variables, how each was measured, and the time order between them: {{VARIABLES_AND_TIMING}}
The data's scope: who or what is included and excluded, and the period: {{DATA_SCOPE}}
What decision might follow if the claim is true: {{DECISION_AT_STAKE}}
Evaluate it like a referee:
1. Restate the claim as a testable statement and the evidence it rests on.
2. List alternative explanations: reverse causation, a common cause (confounder), selection or survivorship in who is in the data, chance with many comparisons, seasonality or trend shared by both series, and measurement artefacts. For each, say what in my description makes it more or less likely, and name the data I could check.
3. Identify the weakest link in the argument.
4. Suggest checks I can do in a spreadsheet: split by subgroup, compare before and after, control for the likely confounder, look at the scatter plot instead of the coefficient.
5. Say what kind of evidence, such as a controlled test, would be needed to support a causal claim, and what wording is fair if I only have correlation.
Do not provide numbers I haven't given you. Be direct if the evidence is thin.
Fill in: CLAIM VARIABLES_AND_TIMING DATA_SCOPE DECISION_AT_STAKE
Run the subgroup split in step 4 before repeating the claim in a meeting.
Best for: Simple, honest forecasts with an error measure you can show
You are a forecasting analyst advising someone who works in a spreadsheet, not a statistics package.
What I am forecasting, the time unit, and how many periods of history I have: {{SERIES_DESCRIPTION}}
Features of the history I can see (trend, seasonality, promotions, one-off events, gaps): {{PATTERNS}}
How far ahead I need to forecast and what the forecast is used for: {{HORIZON_AND_USE}}
App and version: {{SPREADSHEET_APP}}
Do this:
1. Recommend the simplest method that fits: naive or seasonal naive, moving average, linear trend (FORECAST or TREND, which exist in both apps), FORECAST.ETS for seasonal data (Excel only; Google Sheets has no equivalent, so say so), or something outside a spreadsheet. Justify it and warn if my history is too short.
2. Give the setup steps and formulas, and the parameter settings to check (seasonality length, handling of missing periods).
3. Explain how to hold back the last few periods, forecast them and measure the error, with MAPE or MAE formulas, so I can compare methods honestly.
4. Show how to present a range, not a single number, and what the range does not cover.
5. List what would make the forecast unreliable (structural change, outliers, a new product).
Never present the output as a guarantee. Tell me to check function availability in my version.
Fill in: SERIES_DESCRIPTION PATTERNS HORIZON_AND_USE SPREADSHEET_APP
Always compare against a naive forecast; if a fancy method can't beat it, don't use it.
Best for: Making sense of Excel's Data Analysis ToolPak or Python regression output
You are a statistician explaining regression output to a manager who must make a decision from it.
What I modelled: outcome, predictors, units, and sample size: {{MODEL_DESCRIPTION}}
The output pasted from my tool (coefficients, standard errors or p-values, R-squared, residual information): {{REGRESSION_OUTPUT}}
The decision or question I need to inform: {{DECISION_CONTEXT}}
Explain:
1. What each coefficient means in plain words with its unit, for example "each extra unit of X goes with a change of ... in Y, holding the others fixed", and the uncertainty range around it. Use only numbers from my output.
2. What R-squared does and does not say, and why a high value can still mislead.
3. Which predictors look reliably different from zero, which don't, and what "not significant" does not prove.
4. Problems to check that the output hints at or that I should test: multicollinearity, non-linearity, outliers with high influence, residual patterns, extrapolating beyond the data range.
5. A two-sentence summary I could say aloud, with its caveats.
If anything in the output is missing or looks inconsistent (for example impossible values), point it out instead of guessing. Do not infer causation.
Fill in: MODEL_DESCRIPTION REGRESSION_OUTPUT DECISION_CONTEXT
Read the residual plot as well as the table; the table alone can look fine when the model is wrong.
Best for: Seeing whether newer customers stay as long as older ones
You are a product or marketing analyst building a cohort retention analysis.
Tool: {{TOOL}}
Data columns and a few sample rows (customer ID, first activity or sign-up date, activity dates or orders): {{DATA_STRUCTURE}}
What counts as an active period (any order, a login, a paid renewal): {{ACTIVITY_DEFINITION}}
Period size (week or month) and the date range available: {{PERIOD_AND_RANGE}}
Do this:
1. Define a cohort and an activity period precisely, using my definitions, and note edge cases such as customers whose first activity predates the data.
2. Show how to compute each customer's cohort and the number of periods since then, as formulas, SQL or code in my tool.
3. Build the cohort table: cohorts down the side, periods since start across the top, with customers active and the percentage retained. Give the formulas.
4. Explain the traps: the most recent periods are incomplete, small cohorts are noisy, and survivors are not representative of the cohort.
5. Explain how to read the triangle and what questions it can and cannot answer.
6. Give a check: period zero must equal the cohort size.
Don't give benchmark retention rates.
Fill in: TOOL DATA_STRUCTURE ACTIVITY_DEFINITION PERIOD_AND_RANGE
Check period zero equals cohort size before reading any rates.
Best for: Segmenting customers by recency, frequency and spend
You are an analyst who builds segments a team can act on.
Tool: {{TOOL}}
Columns available for each customer (ID, last purchase date, number of orders, total spend), with a few sample rows: {{DATA_STRUCTURE}}
The snapshot date for measuring recency: {{SNAPSHOT_DATE}}
What the business will do differently for each segment: {{ACTIONS_PLANNED}}
Do this:
1. Define recency, frequency and monetary value from my columns, and explain why recency uses the snapshot date, not today's date.
2. Score each on a 1 to 5 scale using quantiles (PERCENTILE or QUARTILE in a spreadsheet, NTILE in SQL), and show how to handle ties and customers with one order, where quantile cut-offs collapse.
3. Propose five to seven named segments from score combinations, each with a rule and the action I said I would take. If there are too many segments to act on, say so and merge them.
4. Give a table template with segment size, share of customers and share of spend.
5. Explain what the method ignores (product mix, margin, channel) and one alternative: simple rules such as "ordered twice in 90 days".
Show the formulas or code for my tool, and a check that segment counts add to the customer total.
Fill in: TOOL DATA_STRUCTURE SNAPSHOT_DATE ACTIONS_PLANNED
Choose segments by the action you will take, not by the number of boxes in the grid.
Best for: A staged, checkable read of a real file in a tool that runs code
You are a data analyst working in an AI tool that can run code on the file I upload. If you cannot execute code on it, say so now and stop; do not estimate numbers.
File contents: {{FILE_DESCRIPTION}}
The business question: {{BUSINESS_QUESTION}}
Things I already know about the file (units, junk rows, definitions): {{KNOWN_ISSUES}}
Work in stages and wait for me after stage 1:
1. Inspect: report the number of rows and columns, each column's type, the share of blanks, duplicate rows, and any value that looks impossible. Don't change anything. Ask me how to treat each problem.
2. After I answer: descriptive statistics for the numeric columns (count, mean, median, spread, minimum, maximum) and frequency tables for the categorical columns.
3. Cuts and trends: two or three comparisons across groups or over time that bear on my question, each with a table and a suggested chart.
4. Summary for a manager: five bullets, each tied to a number shown above, followed by what is confirmed versus what is only suggested.
For every figure, show the code or formula so I can reproduce it. Never invent a number. Remind me to upload only data I am allowed to share with this tool.
Fill in: FILE_DESCRIPTION BUSINESS_QUESTION KNOWN_ISSUES
Use only with files you are permitted to upload; otherwise use dummy rows.
Best for: Turning survey exports into honest, group-by-group findings
You are a survey analyst who reports results without overclaiming.
Survey description: questions, answer scales, number of invitations and responses, and how people were chosen: {{SURVEY_DESCRIPTION}}
The data layout (one row per respondent, column names, coding of answers, don't-know codes), with a few sample rows: {{DATA_LAYOUT}}
What I want to know or compare: {{QUESTION_OF_INTEREST}}
Tool: {{TOOL}}
Provide:
1. Preparation: recode answers as numbers, handle reverse-worded items and don't-know codes, and remove incomplete responses only by a stated rule.
2. Response rate, and the non-response bias risk given how respondents were chosen.
3. How to summarise each Likert item: the share of respondents in each category and the "top two box" share, with a note on when a mean is misleading for ordered categories.
4. How to compare groups, with a warning about small group sizes and a suggested minimum size to report.
5. If there is an NPS question: promoters score 9 or 10, passives 7 or 8, detractors 0 to 6, and NPS is the percentage of promoters minus the percentage of detractors.
6. A method for coding open-text answers into themes, including a check on a sample.
7. Wording for the report that does not overstate the findings.
Give formulas or code for my tool.
Fill in: SURVEY_DESCRIPTION DATA_LAYOUT QUESTION_OF_INTEREST TOOL
Report the number of respondents next to every percentage.
Charts, Dashboards & KPIs
Choose a chart from the message you want to make, critique one before you present it, specify a dashboard before building it, write metric definitions nobody can argue with and set up conditional formatting.
Best for: Picking a chart that makes your point and doesn't mislead
You are a data visualisation designer who starts with the message, not the chart type.
The single point I want the viewer to take away: {{KEY_MESSAGE}}
Data: columns, types and number of categories or time points: {{DATA_SHAPE}}
Audience and where they will see it (slide, printed report, dashboard, email): {{AUDIENCE_AND_MEDIUM}}
App: {{SPREADSHEET_APP}}
Recommend:
1. The best chart type and a reason tied to the message, plus two alternatives and when each would be better. Say which common choices to avoid for my data (for example pie charts with many slices, 3D effects, dual axes).
2. How to set it up: which fields go on which axis, sorting order, and what to do with long category names.
3. What to emphasise (one highlight colour, direct labels instead of a legend) and what to remove (gridlines, borders).
4. Axis decisions: where the baseline should start for my chart type, and why.
5. A title written as a finding, not a topic label, based on my key message only; do not insert numbers I didn't give.
6. Accessibility: colour-blind safe choices and text size.
Fill in: KEY_MESSAGE DATA_SHAPE AUDIENCE_AND_MEDIUM SPREADSHEET_APP
Write down the one message first; the chart type follows.
Example output · Claude (example written by The Prompt Index)
Sample inputs (fictional)
- KEY_MESSAGE: Support ticket backlog grew each month this year, and most of the growth is in the Billing category.
- DATA_SHAPE: 12 months x 4 categories (Billing, Login, Shipping, Other); one numeric column, open tickets at month end.
- AUDIENCE_AND_MEDIUM: Department heads, one slide in a monthly review.
- SPREADSHEET_APP: Excel 365
1. Recommendation
A line chart with four lines, Billing in a strong colour and the other three in light grey. Your message is about change over time and one category standing out; a line chart shows the trend, and the colour contrast carries the "Billing" part. Alternatives: a stacked area chart if the total backlog is the main point (harder to read each category's trend), or a small-multiples set of four mini line charts if the categories need equal attention. Avoid pie charts (no time dimension) and 3D effects.
2. Set-up
Months on the horizontal axis in order, tickets on the vertical axis, one series per category. Label the end of each line directly and delete the legend.
3. Emphasis and removal
One highlight colour for Billing. Remove gridlines and the chart border; keep light horizontal guides at most.
4. Axis
Start the vertical axis at zero. Line charts allow a non-zero baseline, but with a backlog count a zero baseline avoids exaggerating growth.
5. Title (finding, not label)
"The ticket backlog grew every month, led by Billing". I have not added numbers because none were supplied; add the Billing figures from your data.
6. Accessibility
Use a dark blue for Billing and mid-grey for the rest, and make Billing's line thicker so it does not depend on colour alone. Use at least 12-point labels for a slide.
Best for: A tough second opinion on charts for slides and reports
You are a sceptical data visualisation reviewer. Review my chart as if you were a busy executive who has ten seconds.
Chart description: type, axes with units, series, colours, title, labels, annotations: {{CHART_DESCRIPTION}}
The data behind it (values or a summary): {{CHART_DATA}}
What I want the audience to conclude: {{INTENDED_CONCLUSION}}
Audience: {{AUDIENCE}}
Review and report:
1. What a viewer will conclude in ten seconds, based on the description. Does it match my intended conclusion?
2. Honesty problems: truncated axis on a bar chart, inconsistent scales between panels, cherry-picked date range, dual axes that imply a relationship, area or size encoding that exaggerates, missing sample sizes or uncertainty.
3. Clarity problems: clutter, legend lookups, too many colours, unreadable labels, order of categories.
4. A prioritised list of changes, most important first, with the exact setting in Excel or Sheets where I can make each.
5. A rewritten title and annotation.
Base your review only on the description I gave; say what you cannot judge without seeing the picture, and list anything I should describe more precisely.
Fill in: CHART_DESCRIPTION CHART_DATA INTENDED_CONCLUSION AUDIENCE
Describe the chart in detail, or check whether your AI tool can accept an image of it, and use dummy data if the figures are sensitive.
Best for: Briefing a dashboard build so it answers real decisions
You are a business intelligence analyst writing a dashboard specification so the build goes right the first time.
Who will use the dashboard and the decisions they make with it: {{USERS_AND_DECISIONS}}
Data sources and how often each is refreshed: {{DATA_SOURCES}}
Tool (Excel, Google Sheets with Looker Studio, Power BI, Tableau): {{DASHBOARD_TOOL}}
Questions the dashboard must answer at a glance: {{KEY_QUESTIONS}}
Write the specification:
1. Purpose in one sentence and the three decisions it supports.
2. A metric list: for each metric the name, exact definition, formula, source field, time grain, and owner. Flag any definition that is ambiguous and needs a business decision.
3. Page layout in text: what sits top-left (the headline numbers), which charts follow and why, filters and their defaults.
4. Interactions: drill-downs, date selectors, what happens with a blank filter.
5. Data requirements: tables, joins, refresh schedule, and checks that the dashboard total matches a trusted report.
6. Performance and access: row volume concerns, who can see which rows.
7. Out of scope, and open questions for the stakeholder.
Don't invent targets or benchmarks.
Fill in: USERS_AND_DECISIONS DATA_SOURCES DASHBOARD_TOOL KEY_QUESTIONS
Settle metric definitions with stakeholders before any charts are built.
Best for: Ending disputes about what 'active', 'churn' or 'conversion' means
You are an analytics lead who writes metric definitions that remove ambiguity.
The metric name and what people currently think it means: {{METRIC_NAME_AND_CURRENT_MEANING}}
Business context and who uses the number: {{BUSINESS_CONTEXT}}
The source tables or sheets and columns available: {{SOURCE_FIELDS}}
Known disagreements or past discrepancies: {{PAST_DISAGREEMENTS}}
Write a metric definition card:
1. Name, one-sentence plain-English definition, and the question it answers.
2. Exact calculation, with numerator, denominator, filters, inclusions and exclusions, time window and time zone.
3. Edge cases and how each is handled (refunds, test accounts, partial periods, duplicates, late-arriving data).
4. The formula or query to calculate it from the fields I listed, using only fields I gave; if a needed field is missing, say so.
5. Why this metric can mislead, and a companion metric that guards against that.
6. Version history line: date, change, who approved (leave blank for me to fill).
Where the definition depends on a business choice, show the options with consequences and tell me who should decide. Do not state benchmarks.
Fill in: METRIC_NAME_AND_CURRENT_MEANING BUSINESS_CONTEXT SOURCE_FIELDS PAST_DISAGREEMENTS
Get the owner to approve the card; agreeing on definitions is the real work.
Best for: Highlighting overdue, over-budget and unusual rows with correct formulas
You are a spreadsheet designer who uses conditional formatting sparingly and accurately.
App and version: {{SPREADSHEET_APP}}
Sheet layout: headers, columns that matter, and three sample rows: {{SHEET_STRUCTURE}}
What a reader should notice at a glance: {{WHAT_SHOULD_STAND_OUT}}
Thresholds or rules, and who owns them: {{THRESHOLDS}}
Specify up to five rules. For each:
1. The plain-English rule, the cell range it applies to, and the exact custom formula (Excel and Google Sheets both support one; with correct relative and absolute references for the first cell in the range).
2. The format to apply, and why it will be readable, including for colour-blind viewers (use icons or bold as well as colour).
3. A check using one of my sample rows to show whether the rule should fire.
4. Rule order: in Excel, rule priority and the Stop If True option; in Google Sheets, rules apply top to bottom and the first matching format wins. Say which applies to my app.
Add advice on common mistakes: highlighting entire columns, relative-reference drift when copying rules, too many colours, and rules that break when rows are sorted or inserted. If a threshold is missing, ask me instead of choosing one. Finish with how to list and audit all rules in the sheet.
Fill in: SPREADSHEET_APP SHEET_STRUCTURE WHAT_SHOULD_STAND_OUT THRESHOLDS
If you can't say what should stand out, you don't need conditional formatting.
Models, Trackers & Power BI
Goal Seek, data tables and scenario selectors for what-if questions; trackers and templates designed from a description; a clean-up that turns fragile ranges into Tables and named ranges; and DAX measures for Power BI or Power Pivot that you test against a hand calculation.
Best for: Answering 'what would it take?' and 'what if?' questions in a model
You are a spreadsheet modelling coach who knows which what-if tool fits which question.
App and version: {{SPREADSHEET_APP}}
The model: output cell, the input cells that drive it, and the calculation chain in plain words: {{MODEL_DESCRIPTION}}
The what-if question: {{WHAT_IF_QUESTION}}
Inputs that are uncertain, with the low, base and high values I consider plausible: {{INPUT_RANGES}}
Do this:
1. Choose the tool: Goal Seek (one input to hit a target output), a one- or two-variable Data Table (a grid of outcomes), Scenario Manager, or the Solver add-in (several inputs with constraints). Say why. These are Excel features. For Google Sheets, say what exists (Goal Seek is an add-on; there is no built-in Data Table or Scenario Manager), give a formula-driven alternative, and tell me to verify.
2. Give the click path and the exact cells to select.
3. Design a scenario selector: a dropdown of Low, Base, High feeding INDEX or CHOOSE, with a results table that updates.
4. Explain limits: Goal Seek returns one answer and can miss others, and a Data Table's input cells must be on the same sheet as the table.
5. Give a restore test: set the inputs back to base and confirm the output equals the original.
Use only my ranges; label any result you compute as illustrative.
Fill in: SPREADSHEET_APP MODEL_DESCRIPTION WHAT_IF_QUESTION INPUT_RANGES
Keep a copy of the original inputs; Goal Seek overwrites the input cell.
Best for: Budget, project, inventory or task trackers designed properly the first time
You are a spreadsheet designer who builds simple trackers that stay clean as they grow.
App and version: {{SPREADSHEET_APP}}
What the tracker is for and who uses it: {{TRACKER_PURPOSE}}
The information to record for each item: {{FIELDS_NEEDED}}
Rough volume and who edits it: {{VOLUME_AND_EDITORS}}
Reports or answers it must give back: {{REPORTS_WANTED}}
Design the workbook:
1. Sheet list: a data sheet with one row per item and no merged cells, a lists sheet for dropdown values, a summary sheet, and an instructions sheet.
2. Column spec for the data sheet: header, data type, validation rule, and whether it is typed or calculated.
3. The calculated columns and summary formulas, written for my app. Use Excel Tables and structured references in Excel; in Google Sheets use bounded ranges or ARRAYFORMULA, and say which.
4. Conditional formatting for overdue or incomplete items.
5. Protection: which cells to lock, and what protection does not secure.
6. Five dummy rows I can paste to test every formula, with the summary result I should see.
Keep it to the fields I gave; list any field you think is missing as a suggestion, not an addition.
Fill in: SPREADSHEET_APP TRACKER_PURPOSE FIELDS_NEEDED VOLUME_AND_EDITORS REPORTS_WANTED
Test with the dummy rows before entering real records.
Best for: Making formulas robust when rows are added, deleted or sorted
You are a spreadsheet maintainer who removes fragile cell references.
App and version: {{SPREADSHEET_APP}}
How the data is laid out today (location, headers, blank rows, totals inside the range): {{CURRENT_LAYOUT}}
Five formulas that reference it, exactly as typed: {{FORMULAS_USING_IT}}
What breaks today (new rows missed, shifted references, wrong ranges): {{CURRENT_PROBLEMS}}
Produce a migration plan:
1. Prepare the range: remove blank rows, merged cells and inline totals, and make headers unique.
2. Convert it: in Excel, to a Table (Ctrl+T) with a sensible name; in Google Sheets, use a named range or, if tables are available in my version, a table. Tell me to confirm which my version offers.
3. Rewrite each of my five formulas to use table column references or named ranges, showing before and after.
4. Explain the behavioural changes: tables expand when rows are added, references auto-adjust, formulas inside tables fill themselves, and names are workbook-wide.
5. List what to check afterwards: charts and pivots pointing at the old range, other files linking to it, and named range scope.
Flag any formula where the rewrite changes the result and explain why.
Fill in: SPREADSHEET_APP CURRENT_LAYOUT FORMULAS_USING_IT CURRENT_PROBLEMS
Do the conversion on a copy, then compare results formula by formula.
Best for: Writing and testing DAX measures without guessing at filter context
You are a Power BI and Power Pivot modeller who writes DAX measures that behave correctly under filters.
Product: {{POWER_PRODUCT}}
Data model: tables, key columns, relationships (one-to-many direction), and which table holds dates: {{DATA_MODEL}}
The measure in business language, including exclusions: {{MEASURE_IN_WORDS}}
How it will be sliced (by month, region, product) and any filter it must ignore: {{SLICING_AND_FILTERS}}
Write the measure. Rules:
- Use only the tables and columns I listed; if one is missing, ask.
- Write a measure, not a calculated column, unless you explain why a column is needed.
- Use VAR and RETURN for readability, DIVIDE for ratios, and CALCULATE where the filter context must change. Explain each in one sentence.
- For time intelligence, say that it needs a proper date table marked as one.
- Explain how row context and filter context apply to this measure.
Output: the measure, the explanation, and a test: a small table of figures I can calculate by hand and the value the measure should return in a visual filtered to each. Name two ways it could return a wrong total (for example a many-to-many relationship) and how to check.
Fill in: POWER_PRODUCT DATA_MODEL MEASURE_IN_WORDS SLICING_AND_FILTERS
Test every new measure against a hand calculation in a filtered table visual.
Explaining, Documenting & Auditing
Turn findings into a recommendation for non-technical readers, write a memo someone else can reproduce, scope vague data requests, document a workbook and review a spreadsheet model for errors. They flag gaps in your own notes instead of smoothing them over.
Best for: Turning analysis into a clear recommendation for decision-makers
You are an analytics communicator who translates findings into decisions.
The audience, their role and how much they know about data: {{AUDIENCE}}
The findings, in my own words with the key numbers: {{FINDINGS}}
The decision they need to make or the action I want: {{DECISION}}
Caveats and limitations I know about: {{CAVEATS}}
Write a one-page summary with:
1. A headline sentence that states the conclusion and the recommended action.
2. Three supporting points, each one sentence with a number from my findings and what it means for the business. Use only numbers I provided.
3. A "How confident should you be" paragraph in plain language, using my caveats; avoid statistical jargon.
4. What we don't know and what it would take to find out.
5. Recommended next step with an owner and timeframe left as blanks for me to fill.
Style rules: short sentences, no unexplained acronyms, no hedging stacked on hedging, no claims of causation unless I said the evidence supports it. Then add a ten-line "questions they are likely to ask, with a straight answer" section; where my notes don't contain the answer, write "I need to check" rather than invent one.
Fill in: AUDIENCE FINDINGS DECISION CAVEATS
Add the real caveats; a summary with none will not survive a sceptical question.
Best for: A reproducible write-up your future self or a reviewer can follow
You are a senior analyst writing a memo that a colleague could audit and repeat.
Question being answered and why it matters: {{QUESTION_AND_CONTEXT}}
Data used: source, date pulled, filters, row counts: {{DATA_SOURCE_DETAILS}}
What I did, step by step, in my notes (rough is fine): {{METHOD_NOTES}}
Results: the key numbers and charts I have: {{RESULTS}}
Produce a memo with these headings:
- Summary (three sentences: question, answer, confidence).
- Data (source, date, inclusion rules, known gaps).
- Method (numbered steps clean enough to reproduce, including every exclusion and its row count).
- Results (stated with the numbers I gave; no new figures).
- Limitations (what could make this wrong, ordered by impact).
- Recommendation and next steps.
- Appendix list (files, queries and sheets someone would need).
Constraints: if my method notes have a gap (a filter without a reason, a number that doesn't reconcile, an unexplained exclusion), list these as "Gaps to close before sending" at the top rather than papering over them. Keep the tone plain and neutral. Do not claim more certainty than my notes support.
Fill in: QUESTION_AND_CONTEXT DATA_SOURCE_DETAILS METHOD_NOTES RESULTS
The 'gaps to close' list is the most valuable part; fix those before sharing.
Best for: Scoping a vague request so you build the right thing once
You are a consultant-analyst skilled at scoping vague data requests.
The request as it was given to me, word for word: {{ORIGINAL_REQUEST}}
Who asked and what I know about their goal: {{REQUESTER_AND_GOAL}}
The data I believe is available: {{AVAILABLE_DATA}}
Deadline and how the result will be used: {{DEADLINE_AND_USE}}
Help me turn the request into a brief:
1. Restate what you think they are really asking, in two different readings if the request is ambiguous.
2. Write the ten most important clarifying questions, grouped as: purpose and decision, definitions (period, population, metric), data and sources, format and audience, and constraints. Mark the three I must ask before starting.
3. For each of the three, say what goes wrong if I assume the answer.
4. Draft a short message I could send to the requester, polite and under 150 words, with the questions numbered.
5. Draft a one-paragraph scope statement I can confirm in writing once they answer, with explicit "out of scope" lines.
Don't answer the analysis itself. Don't assume what the data contains beyond what I listed.
Fill in: ORIGINAL_REQUEST REQUESTER_AND_GOAL AVAILABLE_DATA DEADLINE_AND_USE
Send the three must-ask questions even if it feels slow; rework costs more.
Best for: A README tab that stops a model becoming a black box
You are a modelling standards reviewer who writes documentation that people actually read.
Purpose of the workbook and who uses it: {{WORKBOOK_PURPOSE}}
Sheets, what each contains, and how they connect: {{SHEET_INVENTORY}}
Inputs a user may change, and where they are: {{INPUT_CELLS}}
Key outputs, and external links or data sources: {{OUTPUTS_AND_SOURCES}}
Draft a README or About sheet containing:
1. Purpose and version, with owner and last-updated placeholders for me to fill.
2. A map of sheets in one line each, using a colour code for inputs, calculations and outputs (suggest a simple convention).
3. How to use it: step-by-step instructions for the monthly update, including where to paste new data and what to refresh.
4. Key assumptions and definitions, in a table.
5. Checks built in, and what a failing check means.
6. Known limitations and things not to do (for example do not insert rows above row 5).
7. Change log table with date, change, person.
Then suggest ten quick structural improvements based on my inventory, such as named ranges, locked formula cells, removing hard-coded numbers from formulas. Mark anything you cannot know from my description as "to confirm".
Fill in: WORKBOOK_PURPOSE SHEET_INVENTORY INPUT_CELLS OUTPUTS_AND_SOURCES
Fill in the owner and date placeholders yourself; they are the first thing people check.
Best for: Second-pair-of-eyes review of a model that people will rely on
You are an independent spreadsheet reviewer, in the habit of checking models for structural and logical errors.
Purpose of the model and the decision it supports: {{MODEL_PURPOSE}}
Layout: sheets, input areas, calculation areas, outputs: {{MODEL_LAYOUT}}
Representative formulas from each area, with their cell addresses: {{KEY_FORMULAS}}
App and version: {{SPREADSHEET_APP}}
Produce an audit plan and findings from what I gave you:
1. Risk map: which areas are most likely to hide errors, and why.
2. For my pasted formulas, list problems: hard-coded numbers inside formulas, inconsistent formulas across a row, ranges that stop short of the data, mixed units, circular references, volatile functions, links to other files, hidden sheets or rows.
3. A checklist of tests I can run in the file: totals cross-checks, a sensitivity test where I change an input by a known amount and confirm the output moves as expected, Excel tools such as Go To Special, Trace Precedents and Evaluate Formula (if I use Google Sheets, name the nearest alternative or say there is none).
4. Which findings are confirmed from my pasted formulas versus suspected.
5. A priority list of fixes.
Do not claim you checked parts of the workbook that I didn't paste. State the limits of this review at the end.
Fill in: MODEL_PURPOSE MODEL_LAYOUT KEY_FORMULAS SPREADSHEET_APP
Paste formulas with cell addresses; the review is only as good as what it can see.