Stop rebuilding the same report by hand every month. Describe your manual process, source structure, and expected outputs to ChatGPT, then use it to draft SUMIFS, XLOOKUP, FILTER, or Table-based formulas that you can verify against known totals before replacing the manual report.
I have a monthly Excel report I currently build manually. Here is the exact process: [describe each manual step — e.g. "copy revenue by region from the raw export into a summary table", "delete rows where Status = Closed"] Source data has these columns: [list them] The final report shows: [describe output] Excel version: [Microsoft 365 / 2024 / 2021 / 2019 / 2016] Known control total or sample result I can use to validate the new model: [enter one if available] Review this process and tell me: 1. Which steps are genuinely rule-based and can be replaced with formulas 2. The best formula/table approach for each and why 3. What source-data changes are required before formulas are safe 4. Which functions are compatible with my Excel version 5. How I should validate each replacement against the current manual result 6. Whether any report labels/categories (for example Region) also need to expand dynamically when new source values appear Do not turn a human-judgment step into a formula unless I give you an explicit rule. If I currently "delete" rows, prefer a non-destructive filtered output unless deletion is truly required. Keep the answer concise and limited to the six requested items. Stay within Excel formulas and Tables unless I explicitly ask for another approach. Do not recommend Power Query, VBA, macros, Office Scripts, dashboards, or external automation. Do not invent business rules, filters, status logic, date logic, historical-comparison logic, or reporting definitions that I have not provided. If a required rule is missing, write "Rule required" and state the missing rule in one sentence instead of making an assumption.
Start here before writing any formulas, then paste the prompt into ChatGPT.
I want to replace this manual step with a formula: "I copy total revenue by region from the raw data into a summary table." Source sheet: Raw_Data Headers are in row 1. Column A header = Region Column E header = Revenue Summary!A5 contains the region to total. Output formula starts in Summary!C5 and will be filled down. The number of source rows changes every month. I want the source converted to an Excel Table named RawData if that is the safer design. Please give me: 1. The recommended formula for Summary!C5 2. The exact Table setup required, including the expected header names 3. A fallback formula if I cannot use an Excel Table 4. A validation check I can run against one known region total 5. Any data-type issue (e.g. Revenue stored as text) that would make the result wrong 6. If new regions can appear, an optional formula to generate the region list dynamically in supported Excel versions, plus a fallback for older versions. Use only the criteria explicitly stated above. Do not add filters or conditions that are not part of the manual step. Do not assume that Status, dates, or any other source column should affect the calculation. Prefer the simplest correct formula and do not add alternative methods beyond the six requested items. If you give a dynamic spilled Region list, make sure the Revenue output also expands for newly added regions rather than relying on a fixed set of filled-down formulas. Keep the answer concise.
Use this once you know which single step you want to automate first.
The report is now formula-based. Workbook structure: - Source sheet/table: Raw_Data / RawData - Summary formula starts at Summary!C5 - Summary!A5:A7 contains region names - Main formula: =SUMIFS(RawData[Revenue],RawData[Region],A5) Write a short maintenance guide for a "Documentation" tab that explains: 1. How to replace the monthly source data without leaving old rows behind 2. Which headers/table name must not change 3. What Summary!C5:C7 does 4. How to verify row count and one control total after each import 5. What to check if totals are blank, zero, or wrong 6. What to do if a new Region appears in the source but is not listed in Summary!A5:A7 Document only the workbook structure and maintenance tasks stated above. Do not redesign the model, introduce new formulas, or propose alternative architectures. Do not invent refresh steps, validation rules, ownership, frequencies, business rules, or source-cleaning procedures that are not stated above. If required information is missing, write "[define this]" instead of filling it in yourself. Keep the guide short and practical.
Run this last, once the model works, so the next person can maintain it without you.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Source | Current method | Problem |
| 2 | Revenue by region | Raw_Data | Copy / paste totals | Repeats monthly |
| 3 | Exclude Closed records | Raw_Data | Delete rows manually | Destructive / easy to miss one |
| 4 | Exception review | Summary | Manager judgment | Needs context |
| 5 | Regional total | Summary | 24,500 typed | No source link |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Keep? | Replacement | Why |
| 2 | Revenue by region | Automate | SUMIFS | Repeatable criteria |
| 3 | Exclude Closed records | Automate | Formula criterion / FILTER | Keep source intact |
| 4 | Exception review | Keep manual | Human review | Needs judgment |
Automate: • Revenue by region → SUMIFS • Exclude Closed records → add a Status criterion to report formulas, or use FILTER when an extracted row list is needed; keep Raw_Data intact Keep manual: • Exception review / sign-off If a rule is missing, flag it instead of inventing one. ✓
| A | B | C | |
|---|---|---|---|
| 4 | Region | Target | Revenue |
| 5 | North | £23,000 | £24,500 |
| 6 | South | £20,000 | £18,200 |
| 7 | West | £30,000 | £31,750 |
| A | B | C | |
|---|---|---|---|
| 4 | Region | Target | Revenue |
| 5 | North | £23,000 | £24,500 |
| 6 | South | £20,000 | £18,200 |
| 7 | West | £30,000 | £31,750 |
Recommended:
=SUMIFS(RawData[Revenue],
RawData[Region],
A5)
Setup:
• Convert Raw_Data source to an Excel Table (Ctrl+T)
• Name the table RawData
• Keep headers exactly Region and Revenue
Copy C5 down to C7.
Structured references expand with the table as rows are added.
Fallback without a Table:
=SUMIFS(Raw_Data!$E:$E,Raw_Data!$A:$A,A5)
If region names can change:
• Microsoft 365 / Excel 2024 / Excel 2021:
put =SORT(UNIQUE(RawData[Region])) in the first Region output cell,
then point the revenue formula at the spilled region list.
• Excel 2019 / 2016:
maintain the Summary region list manually or use another compatible approach.
Validate at least one region total against the source before going live. ✓
| A | B | |
|---|---|---|
| 1 | Documentation | Notes |
| 2 | Where do I paste monthly data? | — |
| 3 | What does Summary!C5 do? | — |
| 4 | What if totals look wrong? | — |
| A | B | |
|---|---|---|
| 1 | Maintenance item | Instruction |
| 2 | Monthly data | Clear the current RawData table body, then paste the new month's rows; do not leave prior-month rows behind. |
| 3 | Keep unchanged | Table name RawData; headers Region and Revenue. |
| 4 | Summary!C5:C7 | SUMIFS totals RawData[Revenue] for the region listed in Summary column A. |
| 5 | After import | Verify source row count and one known regional control total. |
| 6 | New regions | If Summary!A5:A7 is manual, add new region names; or use SORT(UNIQUE(RawData[Region])) in Microsoft 365/Excel 2024/2021. |
| 7 | If totals look wrong | Check table headers/name, region spelling, Revenue data type, and stale/extra source rows. |
1. Monthly input Clear the current data rows inside Excel Table RawData, then paste the new month's rows; keep the headers/table name. 2. Main formula Summary!C5:C7 uses: =SUMIFS(RawData[Revenue],RawData[Region],A5) 3. Region list If Summary!A5:A7 is fixed, add any new regions manually. On Microsoft 365 / Excel 2024 / Excel 2021, you can use: =SORT(UNIQUE(RawData[Region])) 4. Validation Check RawData row count and one known regional control total. 5. Troubleshooting Check table name/headers, region spelling, Revenue is numeric, and no stale rows remain from the prior import. ✓
Write down every step you currently do — be specific. "Copy column D from the export into column F", "Delete rows where Status = Closed", "Add subtotal by region". This list is your starting point for ChatGPT.
Describe your source data — columns, location, update frequency — and the final report — what it shows and who uses it. Paste small samples of both if possible. Be specific about the facts ChatGPT may use, and explicitly state any criteria it must not infer.
Use ChatGPT to separate rule-based steps from steps that should remain manual, then review that classification yourself. If a required business rule is missing, ChatGPT should flag it rather than infer one.
Dynamic reports need clean, consistent source data. Prefer an Excel Table when monthly row counts change, because structured references adjust as rows are added or removed. Also decide whether report categories such as Region can change: a dynamic source does not automatically make a fixed Summary label list dynamic. Once the model matches historical control totals, use ChatGPT to draft a maintenance guide and verify it against the actual workbook structure.
SUMIFS, XLOOKUP, and FILTER that pull data from a standardized raw data sheet. ChatGPT can write these formulas once you describe your current manual process.SUMIFS, COUNTIFS, XLOOKUP, FILTER, and UNIQUE can replace many manual aggregation, lookup, filtering, and category-list steps when they match your Excel version and business rules.SUMIFS aggregates numbers that meet criteria and works in every modern Excel version (2007 and later). FILTER returns the matching rows and is available in Microsoft 365, Excel 2024, and Excel 2021. It is not available in Excel 2019/2016, so tell ChatGPT your Excel version before it proposes a dynamic-array solution.