To clean and summarise a monthly sales export, install the ChatGPT for Excel add-in from Home > Add-ins, work on a copy, define which transactions count as sales, and ask it to propose cleaning rules first. Then have it build a cleaned table and a formula summary, and check row counts, credits and totals against the untouched export.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowThe hard part is deciding what a row represents. Two lines with the same invoice number may be two legitimate products on one invoice, while a repeated invoice-and-line pair is probably an accidental duplicate. Give ChatGPT that distinction before asking it to remove anything, and keep the evidence beside the result.
How do you set up ChatGPT for Excel and a protected working copy?
Install ChatGPT for Excel from Excel's Home > Add-ins by searching for ChatGPT and adding it. Then start it from the ChatGPT button on the ribbon and log in with the account that holds your plan. If the button is missing or access is refused, the add-in repair checklist covers each error; in Enterprise workspaces, OpenAI ships the feature switched off until an admin enables it.
On plans, OpenAI's ChatGPT for Excel help article sets out three tiers: limited usage on Free and Go, access within each plan's agentic usage allowance on Plus and Pro, and usage counted against the plan's credits on Business and Enterprise. The pricing comparison shows the extensions as limited on Free and Go, included on Plus and expanded on Pro. A one-month export of a few hundred rows is a small job, so try it on your current plan before upgrading.
The add-in can edit the workbook, and OpenAI warns that an unclear request can lead it to change or delete data; its help article recommends duplicating the file first for important work. So save the export as received, then work in a copy laid out like this: RawSales (the export, never edited), Rules, CleanSales, Exceptions and MonthlySummary. The tab names are a suggested design, not product menus. OpenAI says the add-in asks permission before making changes and links its answers to the cells it used, which makes this layout easy to audit.
Data caution matters more here than in a chat window, because the add-in sees the whole workbook. OpenAI's help article says it processes your prompt, attachments and the relevant spreadsheet context, that some data logs may be kept by OpenAI for 30 days for safety and integrity, and that under Microsoft's Marketplace terms Microsoft may be able to read workbook content, attachments and prompts. On Business and Enterprise plans OpenAI does not train on your inputs by default; on Free, Plus and Pro you can opt out by turning off Improve the model for everyone in the Data controls settings.
In practice, a B2B software reseller needs customer IDs, product groups, transaction dates, statuses and signed amounts. Contact names, renewal notes and payment details add nothing to a monthly sales summary, so delete those columns from the copy before opening the pane. Agree that list once with whoever owns customer data, rather than relying on a prompt that asks ChatGPT to ignore them.
What should the sales definition say before you ask ChatGPT to clean?
Write a short data dictionary on the Rules tab before any cleaning request. It should hold the reporting month, currency, which date counts (invoice date or posting date), which statuses count, which column holds the amount, and the duplicate key. Say whether amounts are already signed or whether credits arrive as positive numbers in a separate column. A column called Total is ambiguous on its own: it could be a line amount, an invoice total repeated on every line, or a figure after discounts.
For the example that follows, all amounts are invented, in USD and in one month. One row is one invoice or credit line. Posted invoices count, posted credits reduce sales, drafts and voids do not count, and the duplicate key is document ID plus line ID. That is an example reporting policy, not an accounting standard.
Ask whoever owns sales reporting to approve the policy first. A perfect calculation of the wrong definition still produces a wrong report. If the business recognises revenue differently from when it invoices, label this output net invoiced sales and keep the accounting figure separate, so a convenient export never quietly redefines a number used in management meetings.
A mortgage broker shows why. Its export lists fee cases as Agreed, Completed or Cancelled. Counting every Agreed fee as this month's revenue is a policy decision, not a cleaning step, so the broker's finance lead decides which status and date belong in the measure, and the other rows stay visible on Exceptions with a reason.
How do you reconcile eight raw sales lines to five accepted lines?
Eight raw lines reconcile to five accepted lines once every row has a stable source ID, given before you change any label or sort anything. The main worked example is a reseller export with eight rows. The ID is separate from the business duplicate key: it lets you tell the two physical copies of a duplicated transaction apart and record which one you excluded.
| Source ID | Document / line | Status | Group | Signed amount |
|---|---|---|---|---|
| R1 | A101 / 1 | Posted invoice | Licences | $1,000 |
| R2 | B201 / 1 | Posted invoice | Licences | $600 |
| R3 | A101 / 2 | Posted invoice | Licences | $1,000 |
| R4 | CR401 / 1 | Posted credit | Licences | -$100 |
| R5 | B201 / 1 | Posted invoice (repeat of R2) | Licences | $600 |
| R6 | V501 / 1 | Void | Services | $0 |
| R7 | C301 / 1 | Posted invoice | Services | $400 |
| R8 | D601 / 1 | Draft | Services | $200 |
The raw signed sum is $3,700. Excluding the confirmed duplicate takes off $600, excluding the draft takes off $200, and removing the void changes the count but not the amount. Accepted net invoiced sales are therefore $2,900 across five lines. Both A101 lines stay because their line numbers differ: that is three invoice documents and one credit, not five invoices.
Put your own control figures on RawSales before ChatGPT says anything. With source IDs in column A, document IDs in B, line IDs in C, status in D, group in E and amounts in F, =COUNTA(A2:A9) returns 8 and =SUM(F2:F9) returns 3,700. In G2, =COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,C2) filled down returns 2 on R2 and R5 and 1 everywhere else, which flags the duplicate pair without deleting anything.
Read RawSales only. Make no changes yet.
Each row is a document line. Report the row count, signed total and
duplicate candidates using document ID plus line ID; a shared invoice
number alone is not a duplicate. Keep credits. List unclear dates,
statuses, amounts and customer codes. Propose rules for CleanSales and
Exceptions and wait for my approval before applying them.
A useful reply reads something like: “Eight rows, $3,700 raw total; R2 and R5 share document B201 line 1; A101 has two distinct lines.” If it calls A101 a duplicate invoice to remove, stop and correct the rule before anything is created.
Compare that with the one-line prompt OpenAI offers as an example in its help article, which asks ChatGPT to clean up the sheet by standardising formatting, fixing inconsistent labels and removing duplicates. It is fine for a tidy list, but on a sales export “remove duplicates” leaves the model to decide what a duplicate is: it might treat R3 as a copy of R1, since they share an invoice number and amount, and nothing would record the decision. The longer prompt above costs a minute and keeps every removal behind your approval.
Approve R5's exclusion only after checking the source system shows one B201 line, not two. If two rows share a key but have different amounts, treat them as a conflict for review rather than keeping whichever appears first. A duplicate candidate is a question; an approved exclusion is a decision with a reason.
How do you resolve messy fields without losing their original meaning?
Clean labels in new columns and keep the original value beside them, so the original meaning is never lost. Say the export has product groups “Licence”, “licences ” (with a trailing space) and “Software licence”. A reviewed mapping sends all three to Licences, and that mapping goes on the Rules tab so next month's operator applies the same choice instead of asking the model to invent a new one.
Customer identity needs stricter handling. An HR consultancy's export might contain customer codes 00124 and 0124 with similar organisation names. Do not strip leading zeros or merge them because the names look alike; check the customer register first. Keep the code column stored as text so no step converts 00124 into the number 124, and use a separate display-name column if the summary needs tidier names.
Ambiguous dates belong on Exceptions. A value such as 03/04 can mean two different days depending on the date format, so confirm the export's documented format or compare one known transaction with the source system. If you cannot tell, keep the raw text and leave that row out of period-based conclusions until it is resolved.
Amounts need the same care. “1,200.00” and “1200.00” are the same value; “1.200,00” follows a different number convention and must be converted deliberately. Ask ChatGPT to propose the conversion and show three converted examples before applying it, because a column that looks numeric afterwards can still be wrong by a factor of a hundred or a thousand.
Blank classifications should stay visible. If the $400 services line has no sales channel, label it Unassigned for channel reporting instead of spreading it across known channels; the channel table still has to total $2,900. For customer-name clean-up that goes beyond this monthly report, the CRM data cleaning tutorial covers it properly, with its own review and rollback.
How do you approve a change log, then build the summary with formulas?
Once the rules are approved, ask ChatGPT for CleanSales as a derived table and Exceptions as a record of every decision. Build the summary from CleanSales with formulas. RawSales stays untouched, and every accepted line carries its source ID.
Create CleanSales from RawSales using the approved Rules only.
Keep source IDs and original labels next to cleaned labels.
Exclude R5 (confirmed duplicate), R6 (void) and R8 (draft).
On Exceptions, record source ID, issue, decision and signed amount
for every exclusion. Then report counts and totals before and after.
Stop and ask if any amount cannot be read as a number.
The finished Exceptions tab has three rows: R5, duplicate, $600; R6, void, $0; R8, draft, $200. Label corrections are logged separately with zero monetary effect, because formatting changes should never explain a movement in sales. If a reconciliation ever needs an unexplained “cleaning adjustment”, find the rows behind it before the figures go anywhere.
Build MonthlySummary with formulas rather than a PivotTable. OpenAI's FAQ lists Pivot and Data Model features among the Excel features the add-in does not support, and a formula summary is easier to check anyway. With CleanSales laid out like RawSales (cleaned group in column E, amount in F, original label added in G), ask ChatGPT to write and explain formulas such as:
=SUMIFS(CleanSales!F:F,CleanSales!E:E,"Licences")returns 2,500, net licence sales.=SUMIFS(CleanSales!F:F,CleanSales!E:E,"Licences",CleanSales!F:F,">0")returns 2,600, gross licence invoices.=SUMIFS(CleanSales!F:F,CleanSales!F:F,"<0")returns -100, the credits.=SUMIFS(CleanSales!F:F,CleanSales!E:E,"Services")returns 400.
Showing gross invoices and credits in separate columns, with net as the difference, stops a falling total from hiding rising credits. Licences are $2,600 gross less $100 of credit, so $2,500 net; services add $400; combined net sales are $2,900.
Then check the narrative. A generated line such as “Services generated $400 and licences $2,500” is fair. “Customers increasingly prefer licences” is not: one month of data contains no comparison period and no evidence of preference. Rewrite it to describe the records you have, and note what data a trend claim would need.
If the formula summary is the part that slows you down, try The Complete Excel Course with AI Integration on Coding Liquids.
How do you check the sales total through three independent routes?
Check the total three ways: a reconciliation bridge, row counts and category totals. The bridge: $3,700 raw, less $600 duplicate, less $200 draft, equals $2,900 accepted. Check counts: eight raw rows less three exclusions equals five accepted. Check categories: $2,500 licences plus $400 services equals $2,900. Each catches a different error; the zero-value void, for example, breaks the count without moving the amount.
Put the bridge in a cell so it fails loudly. With exclusion amounts in column D of Exceptions, =SUM(RawSales!F2:F9)-SUM(Exceptions!D2:D4)=SUM(CleanSales!F2:F6) returns TRUE when everything ties. If someone later edits CleanSales by hand, it flips to FALSE.
The accepted source IDs should be R1, R2, R3, R4 and R7, each exactly once. If R2 appears twice and R3 is missing, the total could still look plausible after other changes, so ask a second person to tick the derived list against the raw export rather than nodding at a familiar-looking total. In a larger file, reconcile full counts and totals, then inspect every exception plus a deliberate sample: the largest invoice, a credit, a zero, and the first and last transactions of the month.
Test sign handling on purpose. If the minus $100 credit becomes positive, net sales show $3,100, a $200 overstatement. If the credit disappears, they show $3,000, a $100 overstatement. Those different gaps point to different faults, so keep both as test cases for next month's run. If your export is too big to review line by line, the sales analysis verification tutorial covers batch checks.
How should credits, currencies and late corrections be handled?
Handle a credit issued this month against an earlier invoice with an explicit period rule, because finance decides how the measure treats it. In this reseller's policy, current-month net invoiced sales include the credit, while a separate customer analysis links it to the original sale. Do not let ChatGPT move a credit to another month to make this one look cleaner.
Mixed currencies need their own tables unless you supply an approved conversion policy and rates. A $500 sale and a 500-unit sale in another currency are two amounts, not $1,000. When conversion is needed, keep the original currency, the rate, its source and date, and the converted amount side by side.
Late changes are why the raw export stays untouched. Suppose the source system posts a missing $300 invoice after your review. Pull a fresh export and compare it with the first by document and line. The accepted total becomes $3,200 only if that invoice meets the policy and nothing else changed. Label the report Revision 2 so two different totals never circulate unexplained.
Matching grand totals do not prove the grouping is right. A mistaken merge of two customers can leave $2,900 unchanged while giving an account manager the wrong customer ranking. Check the total and the grouping, and keep an Unresolved group until the customer register is fixed.
When should you reject a comparison with last month?
Reject a comparison with last month unless both months use the same transaction types, date basis and currency treatment. Run the same definition and checks on last month's export before adding it to the summary. A current $2,900 against an earlier $2,500 suggests a $400 increase, or 16%, but only if that holds.
Suppose last month's report excluded credits and this month's includes them. More persuasive wording will not fix that. Restate last month on the approved basis, or mark the comparison unavailable, and keep the original report beside the restated one with a note on why they differ.
Customer concentration needs the right denominator too. Suppose invoice A101 belongs to one customer: its two lines give that customer $2,000 of the accepted $2,900, or about 69%. The $200 draft must not enter the denominator just because it sits in RawSales. And a high share describes this month's dependence on one buyer; it does not show that the customer will leave or that prices should change, so note what evidence would answer those questions instead.
How do you package a monthly handover the next operator can reproduce?
The Rules tab is the handover the next operator can reproduce. OpenAI notes that spreadsheet chats do not sync with your main ChatGPT history and cannot use ChatGPT memory, so nothing about this month's decisions carries over by itself. Here is the reseller's, filled in:
| Rule | This month's setting |
|---|---|
| Measure | Net invoiced sales, USD, posting date in the reporting month |
| Counts | Posted invoices; posted credits reduce sales |
| Excluded | Drafts, voids, confirmed duplicates (logged on Exceptions) |
| Duplicate key | Document ID plus line ID, confirmed against the source system |
| Group mapping | Licence / licences / Software licence to Licences |
| Customer codes | Stored as text; no merging without the customer register |
| Control figures | 8 rows, $3,700 raw; 5 rows, $2,900 accepted |
| Approved by | Sales reporting owner, with date |
Next month, the opening request becomes “Read the Rules tab and apply it to the new RawSales; list anything the rules do not cover before changing anything.” New product groups, statuses or customer codes then surface as questions instead of guesses.
After the first run, note where the time went. If most of it went on unidentified customers, the fix belongs in the customer register or the export settings, not in a cleverer prompt. If it went on rewriting instructions, the Rules tab needs to be firmer.
- Keep the untouched export with its row count and signed total.
- Save the approved Rules tab with the reporting month.
- Keep CleanSales linked to source IDs.
- Resolve or explicitly carry forward every exception, with an owner.
- Record the reconciliation bridge and who approved it.
- Share MonthlySummary with its definition and revision label.
The handover line for this example reads: “Net invoiced sales $2,900; five accepted lines; three invoice documents and one credit; three exclusions logged; bridge check TRUE; no unresolved amounts.” That tells the next person exactly what was measured and which controls to repeat when the next export arrives.
If you want help fitting this into how your team actually reports sales, I offer one-to-one AI consultations for small businesses.
Further reads
- How to Create a Sales Dashboard in Excel Using ChatGPT — Turn the verified totals into a one-page KPI sheet.
- Six Questions to Ask ChatGPT About Your Sales Spreadsheet — Questions worth asking once the data is clean.
- How to Build a Seasonal Demand Forecast in ChatGPT and Test It — Use several clean months as forecasting history.
- How to Create a Monthly Management Pack With ChatGPT — Build a checked monthly P&L, explain budget variances and prepare management slides without losing the trail back to the ledger.
Browse all AI-for-business tutorials
Sources: OpenAI's ChatGPT for Excel and Google Sheets page and help article; ChatGPT pricing page; OpenAI help on how data is used to improve models; Microsoft Support pages for SUMIFS and COUNTIFS. Checked 18 November 2026.
