Help me build or fix an Excel solution for [GOAL]. My workbook has [SHEET AND TABLE LAYOUT]. The relevant columns, data types, and example values are [COLUMNS AND EXAMPLES]. I use [EXCEL VERSION AND LOCALE]. The desired output and where it should appear are [DESIRED OUTPUT]. Known data issues or business rules are [DATA RULES]. First, restate the source table’s grain and identify whether formulas should calculate per row, per group, or as a report total. Do not assume column letters if I provide table names or headers. Produce: 1. A recommended workbook structure, naming input tables, calculation columns, summary areas, and any PivotTable or Power Query step only when it is the better fit. 2. Exact formulas ready to paste, with structured references where possible. State the cell or column where each formula goes, whether it should spill, and how to fill it down. 3. A plain-English explanation of each formula’s logic, including how blanks, text values, zeros, errors, duplicate records, and future rows are handled. 4. A validation section with at least three checks: a control total, spot-check method, and an error flag or conditional-formatting rule. 5. If a formula has a version limitation, provide a compatible alternative and explain the trade-off. Use Excel functions that are available in my stated version. Ask up to 3 clarifying questions only if a required input is missing. Before answering, self-check formula references, absolute versus relative references, criteria ranges, date boundaries, and whether the result can silently change when rows are added or filtered. Do not invent sheets, columns, financial values, or unsupported Excel functions.
Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [GOAL] | Describe the calculation, cleanup, report, or workbook problem you need to solve. | Calculate monthly recurring revenue by customer and flag accounts with a 20% month-over-month drop |
| [SHEET AND TABLE LAYOUT] | Name the sheets and tables and explain what each row represents. | Sheet Data contains Excel table Subscriptions; one row per subscription invoice with customer, invoice date, amount, and status. Sheet Dashboard is for the summary |
| [COLUMNS AND EXAMPLES] | List relevant headers, data types, and a few representative example values. | Customer text: Northline Studio; InvoiceDate date: 8/1/2026; Amount number: 450; Status text: Paid, Void, Pending |
| [EXCEL VERSION AND LOCALE] | State your Excel edition and regional formula conventions if relevant. | Microsoft 365 for Windows, US English formulas with commas |
| [DESIRED OUTPUT] | Describe the exact result, format, and destination cell, table, or report area. | A monthly customer table beginning Dashboard!A5 with current MRR, prior-month MRR, percent change, and a drop flag |
| [DATA RULES] | List business rules, exclusions, duplicate handling, and known data-quality issues. | Only Paid invoices count; Void invoices never count; a customer can have more than one paid invoice in a month; blank amounts should be flagged |
How to use
- Convert the source range into an Excel Table and use its real table and header names in the prompt.
- Paste several typical rows plus any troublesome values such as blanks, dates stored as text, and duplicates.
- Test formulas on a known month and compare the result against a hand-calculated control total.
- Follow up with: “Add a formula-based audit column that explains why each row is included or excluded.”
Variations
Formula debugging
Use this when a formula returns an error or a value that does not match your expectation.
Debug this Excel formula: [FORMULA]. It is in [CELL OR TABLE COLUMN] and should calculate [EXPECTED RESULT] from [SOURCE COLUMNS]. The actual result or error is [ACTUAL RESULT], and representative rows are [EXAMPLE ROWS]. Explain the most likely cause, then give a corrected formula for [EXCEL VERSION]. Check text-versus-number values, dates, blank cells, absolute references, criteria ranges, hidden spaces, and row expansion. Provide one quick test formula or helper column that verifies the fix. Do not assume unlisted columns or values. Ask up to 3 questions only if needed.
Data cleanup
Use this when imported spreadsheet data needs to be standardized before reporting.
Create an Excel cleanup plan for [SOURCE DATA]. The table is [TABLE NAME AND COLUMNS], and the known problems are [ISSUES]. I need a cleaned output in [DESTINATION] while preserving the raw import. Recommend whether formulas, Power Query, or both fit best for [EXCEL VERSION]. Give exact steps or formulas for trimming spaces, normalizing text categories, parsing dates, handling blanks, identifying duplicates, and retaining an audit reason for changed rows. Include a row-count and control-total check. Do not delete or overwrite source data without an explicit rule.
Management dashboard
Use this when you need a compact recurring report rather than a one-off calculation.
Design an Excel dashboard for [AUDIENCE] to monitor [BUSINESS PROCESS]. Source data is [TABLES AND GRAIN], reporting cadence is [CADENCE], and decisions supported are [DECISIONS]. Specify 5 to 7 KPIs with exact definitions, the summary table or PivotTable logic, recommended charts, date filtering behavior, and alert thresholds. Include formulas or refresh steps appropriate for [EXCEL VERSION]. Add reconciliation checks between the dashboard and source totals. Do not choose targets or thresholds unless I supply them; label them as open decisions instead.
Tips
- Use Excel Tables instead of fixed cell ranges so new rows are included without editing formulas.
- Keep raw imported data separate from cleanup columns and reporting outputs; this makes errors traceable.
- For date-based formulas, define inclusive and exclusive boundaries explicitly, especially around month end.
- Add a control total near every summary: count of included rows, sum of amounts, or distinct ID count depending on the metric.
FAQ
Should I use a formula, PivotTable, or Power Query?
Use formulas for row-level calculations and small interactive summaries, PivotTables for grouped exploration, and Power Query for repeatable imports and transformations.
Why does my SUMIFS return zero when values look correct?
Common causes are text stored as numbers, dates stored as text, mismatched criteria text, or ranges with different sizes. A helper check can reveal which condition fails.
Can AI guarantee an Excel formula is correct?
No. It can propose a formula and its checks, but you should test it against known rows and a control total in your workbook.