Coding 2,000 Uncategorised Transactions in Excel with ChatGPT — and Keeping the Answer Defensible

Guide · Updated September 2026 · ~14 min read

A client hands you a year of bank data with nothing coded. Two thousand lines, one narrative column, and a chart of accounts with forty-one codes in it. The work is not difficult; it is just two thousand times. This is the task most people are actually trying to solve when they go looking for a ChatGPT add-in for Excel.

There are five real ways to point ChatGPT at that column from inside the workbook, and they diverge sharply at 2,000 rows. One is metered against an allowance shared with everything else you do in ChatGPT, so a single bulk run can eat it. One writes an answer into every cell, re-bills you the next time somebody presses F9, and can quietly return a different category in December than it did in September. One needs an API key that must never end up inside a workbook you email back to the client.

The more useful finding is that you should almost never code 2,000 lines. Strip the variable tail off each narrative — card reference, date stamp, trailing digits — and 2,000 transactions usually collapse to 120 to 200 distinct stems. Classify the stems, review that short list like a human being, apply it with a lookup. It costs pennies instead of pounds, a reviewer can actually check it, and it produces the same answer next month, which is the part that decides whether any of this belongs in a working paper.

Two neighbouring posts, so this one does not repeat them: the wider field is in AI in Excel for accountants, and the Anthropic equivalent of this page is getting Claude working inside Excel.

The method: 150 rules, not 2,000 labels

Start with the method, because it decides which of the five routes you need. Column A is the date, column B the bank narrative, column C the amount. You want an account code in column D.

The naive version works, and it is worth doing once so you can see what the output looks like:

=GPT("Classify this bank narrative into exactly one of: Travel, Software,
Bank charges, Payroll, Supplier payment, Transfer, Other.
Reply with the category name only.", $B2)

Fill down, wait for the column to settle, then — before anything else — select D2:D2001, copy, and Paste Special > Values over itself. Sanity-check the distribution with =COUNTIF($D$2:$D$2001, F2) against your seven categories listed in column F. If "Other" is 40% of the rows, the prompt is wrong, not the data.

Seven tidy words are not what you need, though. The client's books do not have a category called Travel; they have 6200 Travel & Subsistence and forty other codes, and anything that does not come back as one of those is a row somebody re-types later. So paste the actual chart of accounts into the prompt and make the model choose from it, with an explicit escape hatch rather than a guess:

=GPT("Choose exactly one account code for this bank narrative.
Allowed codes, one per line:
" & $H$1 & "
Reply with the code only, no name and no punctuation.
If the narrative does not clearly indicate one of these codes, reply REVIEW.", $B2)

With $H$1 holding the code list as a single text block. That one change does more for the quality of the output than any amount of prompt polishing, because it turns an open question into a closed one.

The better pattern, once you have done this twice, is not to classify 2,000 rows at all. Strip the variable tail off each narrative — card reference, date stamp, trailing digits — and you will usually find 120 to 200 distinct stems behind 2,000 transactions. Classify the stems, review that short list like a human being, and apply it with XLOOKUP:

=XLOOKUP(TRUE, ISNUMBER(SEARCH(Rules[Stem], $B2)), Rules[Category], "REVIEW", 0, 1)

You review 150 rules instead of 2,000 labels, it costs a fraction as much, and — the part that matters for a workpaper — it reproduces exactly next month. The matching problem next door is covered in reconciling a bank statement in Excel with AI.

What to do with the REVIEW bucket. Filter column D to REVIEW and work the list top down; it is usually between two and five per cent of the rows, and it is the most valuable part of the job rather than the leftovers. Three things live in there. Genuinely new payees, which need a new rule adding to the table — do that rather than typing the code into the row, so next quarter gets it free. One-off transactions that will never recur, which you code by hand and leave without a rule. And narratives that are ambiguous to a human too: a transfer that could be drawings or a loan repayment, a card payment to a supplier who sells you both stock and repairs. Those are questions for the client, not for a better prompt, and the correct output of this step is a query list. Whatever you do, do not let REVIEW survive into the column you post from — count it before you hand the file on, and expect the count to be zero.

And the trap underneath all of it: coding is not posting. A bank narrative tells you, at best, who was paid and roughly for what. It does not tell you the VAT or sales-tax treatment, it does not tell you whether the cost is capital or revenue, it does not tell you which period it belongs in, and it cannot see a contract that makes the payment a prepayment rather than an expense. The model is picking an account code from a string; it is not making the judgement calls that sit on top of the account code. Treat the output as the first column of a coding sheet that a person still finishes, and be especially careful with the categories where the VAT treatment is not a function of the supplier — entertaining, fuel, anything cross-border, anything from a supplier who is not registered.

The rules table is the deliverable, not the coded column

The coded column is this quarter's answer. The rules table — stem in one column, account code in the next — is a client-specific asset that gets better every time you use it, and it is the thing worth keeping. Most people throw it away, re-run the classification from scratch next quarter, get a slightly different answer, and cannot explain why.

Treat it like any other standing paper. Give it its own tab, called Rules, and format it as a real Excel Table so the XLOOKUP above picks up new rows without anyone editing a range. Put a header block on the tab: client name, the date the table was last reviewed, who reviewed it, and which chart of accounts version the codes belong to — because when the client renames or merges an account, every rule pointing at the old code is now wrong and you want to be able to tell at a glance whether the table has been looked at since. Keep a column for notes, and use it: "All payments to this supplier are stock, not repairs — confirmed with client Jan" is worth more than the rule beside it, because it stops the next person undoing the decision.

Version it rather than overwriting it. The simplest workable discipline is one sheet per period-end, copied forward at the start of each quarter, so you can answer the question a reviewer will eventually ask: was this line coded under the same rule last time, and if not, what changed? That is not bureaucracy for its own sake. A rules table is the only artefact in this whole process that makes an AI-assisted coding run reproducible, and reproducibility is the difference between a working paper and a screenshot of one.

Then hand it to the client. A firm that has built a 200-row stem-to-code table for a client has built something the client cannot easily get elsewhere, and giving it to them is not giving away the work — the work is the judgement in the notes column and the queries that came out of the REVIEW bucket. It also means the bookkeeping software's own rules can be set from the same list, which cuts next quarter's uncoded pile at source. The best outcome of an AI coding run is that there is less to code next time.

Five ways to run the coding — and what each does to the workpaper

Same five routes everyone lists, judged against this one job rather than in the abstract. Two questions decide it: can it handle 2,000 rows without falling over or bankrupting the allowance, and will it give the same answer in November that it gave in September.

1. OpenAI's own ChatGPT for Excel add-in — first-party, a sidebar, gated by your ChatGPT plan. Bulk: no. Reproducible: not by itself, but it is the best place to build the rules table.
2. Marketplace add-ins that put a =GPT() function in the grid — your OpenAI key or the vendor's credits. Bulk: yes, this is the only route designed for it. Reproducible: only once you have converted the results to values.
3. VBA or Office Scripts calling the API yourself — total control, and every problem becomes yours. Bulk: yes. Reproducible: as reproducible as the code you write, which is the point of building it.
4. Power Query calling the API during a refresh — possible, and usually the wrong tool. Bulk: yes, and it re-bills the whole lot on every refresh. Reproducible: no, which is disqualifying here.
5. Desktop add-ins that bundle a model — and, in the accounting ones, connect to the ledger. Bulk: depends on the product. Reproducible: same caveat as any AI output, but they are the only class that can check a code against the real chart of accounts rather than trusting a pasted list.

Route 1: the official add-in — good at writing the rules, wrong tool for 2,000 rows

OpenAI launched ChatGPT for Excel in beta in March 2026, extended it to Google Sheets in April, and made it generally available across plans in May 2026. You install it from the Microsoft Marketplace like any other Office add-in and sign in with the OpenAI account attached to your plan; organisations that cannot reach the store can have an admin deploy it from the manifest. Because it is packaged to the Microsoft 365 add-in standard, it runs where Office add-ins run — desktop Excel on Windows and Mac, and Excel in the browser.

It is a sidebar working on the open workbook rather than a copy of it, and it behaves agentically: several steps, editing in place. For this job that means you can point it at the narrative column and the chart of accounts tab together and ask it to propose the stem list, which is genuinely useful and hard to do any other way.

The limits worth knowing before you build a process on it:

• It is metered against your plan, not free. Free and Go tiers get limited access; Plus and Pro draw on the plan's agentic usage limit, which is shared with other agentic features rather than reserved for spreadsheets, and a large multi-step edit eats far more of it than a one-line question. The free preview for Business, Enterprise, Edu and K-12 ran to 2 June 2026, after which usage follows each plan's credits and usage terms.
• Charting is basic. Multi-element dashboards and dynamic KPI layouts are not what this is for yet.
• It reads the workbook, not your accounting system. The export from QuickBooks, Xero or Zoho Books still has to be in the file before the add-in can see it. Intuit's own QuickBooks connector for ChatGPT and Claude is compared with the other routes in QuickBooks MCP server options compared.
• Admin controls apply. Access varies by plan, region, workspace settings and tenant policy, so check OpenAI's help centre page for where it stands on your plan rather than trusting any third-party summary — including this one.

One clarification, because the two get conflated constantly: this is not Microsoft Copilot. Copilot is Microsoft's own pane, licensed through Microsoft 365 and routing across model providers on its own terms; the OpenAI add-in is a separate product you install and sign into separately. What Copilot in Excel can't do for accountants covers that side of the fence.

So the finding for this job is a split. Use the sidebar to build and argue about the rules table — show it the narratives and the account codes, have it propose stems, push back on the ones that are too broad, get it to spot the payees it cannot place. That is a conversation, it takes a handful of exchanges, and it is the best route available for it. Do not use it to fill a column. A large multi-step edit across two thousand rows draws hard on an agentic usage limit you share with everything else you do in ChatGPT, and you will spend the month's allowance on the one task that a worksheet function does for pennies.

Route 2: =GPT() down the column — the only route built for bulk

The first-party add-in is conversational. It is not a worksheet function, and if what you actually want is one prompt filled down two thousand rows, you need a third-party add-in.

Before you compare products, answer the firm question, because it decides the route on its own: who is going to own the API key? If the answer is a named person with somewhere sensible to keep it and the authority to put a spend cap on it, bring your own key and pay OpenAI directly. If the answer is "nobody, really" — which is the honest answer in most small practices — take the vendor's credits, accept the markup, and stop thinking about it. Choosing credits because nobody owns a key is a perfectly good decision; discovering six months later that the key is in a shared document because nobody owned it is not.

GPT for Work is the best-known of these. It installs from the Microsoft Marketplace, exposes =GPT() and companion functions in the grid, and is built for bulk: the vendor advertises up to 1,000 rows a minute and up to a million rows in a single run, writing results straight into the sheet. It runs on the vendor's own credits by default — free credits to start, then credit packs from $29 valid twelve months, or a subscription from $25 a month — with your own OpenAI API key an option, in which case you pay OpenAI directly for the tokens and the vendor charges a small per-token platform fee on top. Check their pricing page before budgeting; these numbers change.

That default matters more than it looks. If nobody at your firm wants to manage an API key, vendor credits are the safer arrangement; if you already have a key with a spend cap on it, bringing your own is cheaper at volume.

Why a live AI formula cannot stay in a working paper

Three hazards, and the first one is not a cost problem dressed up as a control problem. It is a control problem.

1. Recalculation re-bills you, and can change the answer. An AI function is a live formula. Every recalculation fires a fresh API call — open the file in November, press F9, and you have paid again. Worse, model output is not deterministic: the same narrative can come back "Bank charges" today and "Other" in December, and nothing in the sheet will tell you it moved. That is why the Paste Special step above is not housekeeping but the control itself. A figure going into a statement or a workpaper must be a value, not a prompt waiting to re-run.

Put it in reviewer's terms and it stops being a preference. Reperformance means a second person can get the same answer from the same inputs. A column of live =GPT() formulas fails that by construction: the reviewer's recalculation is not a check on your work, it is a fresh run that may disagree with it, and neither of you can say which one was right. Worse, a figure that changes when somebody opens the file is not evidence of anything — the coding you signed in September is not the coding in the file in March. Converting to values is the step that turns an output into a record. Do it in the same sitting, before the file is saved anywhere anyone else can reach it, and never leave a live AI formula in a file you are handing on.

2. The function cannot see your chart of accounts. =GPT("...", $B2) sees B2. Not the account list on the next tab, not the supplier master, not the sign convention two columns over, not the fact that this client codes all fuel to motor expenses. This is why the closed-list prompt earlier in this post pastes the codes into the prompt itself: if the categorisation depends on a list, the list has to be in the prompt or in a second range argument, or the model will invent a category name that looks plausible and matches nothing. And pasting the list has its own weakness — it is a copy, so it is right until somebody adds an account in the ledger and nobody updates $H$1. This route answers "classify this cell", and never "why does this sheet not tie out?" — that second question belongs to a sidebar with whole-workbook context, which is routes 1 and 5.

3. API keys, and the workbook you email back to the client. A key pasted into an add-in's settings is exactly as safe as that vendor's storage. The exposure that actually happens in a practice is smaller and stupider than a vendor breach: a key stored in the workbook — hidden sheet, defined name, VBA module — travels with the file the moment somebody sends the coding sheet back to the client, or to the bank, or into a shared folder the whole team can read. It is your key, your spend, and your account it is charged to, and you will not find out from the file. Three rules cover most of it: never put the key in the workbook; issue a separate restricted key per use with a spend cap on it, so a runaway fill-down costs a few pounds rather than a few hundred; rotate it when whoever set it up leaves. If your firm has nowhere sensible to keep a key, use vendor credits and accept the markup.

Route 3: build it yourself, if this has to run every month

Building your own is almost never worth it for one client's year of history. It starts to make sense at the point the job stops being a one-off: twenty bookkeeping clients on the same monthly cycle, the same rules tables, the same file layout coming out of the same bank feeds. At that volume you are not buying intelligence, you are buying a repeatable run — and a script you control produces the same output every time, which is exactly what the previous section says a live formula cannot.

The API is one HTTPS endpoint and VBA can reach it:

Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP.6.0")
http.Open "POST", "https://api.openai.com/v1/chat/completions", False
http.setRequestHeader "Authorization", "Bearer " & apiKey
http.setRequestHeader "Content-Type", "application/json"
http.Send "{""model"":""gpt-5-mini""," & _
    """messages"":[{""role"":""user"",""content"":""" & prompt & """}]}"
Debug.Print http.responseText

That works, and then the real work starts. VBA has no native JSON parser, so reading the reply cleanly means a parser class or string surgery. The prompt must be JSON-escaped, or the first quotation mark in a supplier name kills the request. The key has to live somewhere. And the model sees only what your code sends.

Office Scripts is the modern-looking alternative, and Microsoft does document fetch from a script — but read the platform limits first. Microsoft's external-calls page states plainly that "There is no way to sign in or use OAuth2 type of authentication flows. All keys and credentials have to be hardcoded (or read from another source)" and "There is no infrastructure to store API credentials and keys." It also warns that "External API calls fail when a script is run through Power Automate", and that external calls are unsupported for scripts stored on a SharePoint site. So the two things a finance team would want — a scheduled run, and a key that is not sitting in the script — are the two this route does not give you.

One trap to rule out early: Python in Excel cannot make the call. It runs in Microsoft's cloud sandbox without outbound internet access, so an HTTP request to any API fails by design. Check Microsoft's current documentation if that ever looks to have changed, but do not plan on it.

Route 4: don't put the call in the query — put the cleanup there

Power Query can post to an API during a refresh. The shape looks like this:

let
    Body = Json.FromValue([
        model = "gpt-5-mini",
        messages = {[ role = "user", content = "Classify: " & Narrative ]}
    ]),
    Response = Json.Document(Web.Contents("https://api.openai.com", [
        RelativePath = "v1/chat/completions",
        Headers = [#"Content-Type" = "application/json"],
        Content = Body
    ]))
in
    Response

Note the shape: a constant base URL with the variable part in RelativePath, which is the arrangement credentials and refresh are designed around — build the whole URL by concatenation and you will meet a refresh error later.

It is the wrong place for this job, for one disqualifying reason: every refresh re-runs every call. "Refresh All" on a 2,000-row table is a full re-bill and a fresh set of answers, which is the reproducibility failure above with an automation schedule attached to it. Bearer tokens are awkward too — the usual workaround is an explicit Authorization header with anonymous credentials, which puts the token in the query text where anyone with the workbook can read it.

Put the cleanup here instead, because the stem extraction is exactly the kind of work Power Query is built for and doing it by hand is the tedious half of the method. Load the bank export, trim the narrative, strip the card-scheme prefix and the trailing digits with Replace Values, upper-case what is left, then Group By the resulting stem to collapse 2,000 rows into the 150 distinct values you actually want to classify — and to give you the transaction count against each one, which tells you which rules are worth arguing about. Join the coded rules table back on at the end. Power Query for beginners covers the shaping; leave the calls to a route built for them.

Route 5: the route where the chart of accounts is already in the room

Every route so far shares one gap, and this job shows it up more sharply than most: the AI can see the spreadsheet, but not the system the spreadsheet came from. For an accountant that is the wrong half of the problem — the numbers live in QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks or Sage. A smaller class of paid desktop add-ins bundles the model access with the connectors, so there is no key to paste and the assistant can pull the data itself.

What that changes here is specific. Every other route works from a chart of accounts that somebody copied into the workbook, which means the model is choosing from a snapshot: right on the day it was pasted, quietly stale the moment an account is added, renamed, merged or archived. A route that can read the account list from the ledger is choosing from the codes that exist today, and can check a proposed code against them rather than producing a plausible string. That is the difference between a coding column you re-check against the client's books and one that was built from them.

Where HISAB 360 fits

HISAB 360 is one of those. It docks a chat panel in desktop Excel, reads the open workbook and applies the work rather than describing it — formulas into cells, Power Query queries, PivotTables, Power Pivot models and DAX measures, charts and VBA — and holds OAuth connections to QuickBooks Online, Xero, Zoho Books, Odoo and FreshBooks with read and write access, so "pull last month's unpaid invoices and age them" is one instruction instead of an export, an import and a formula session.

For readers of this post the relevant detail is the key question: you can bring your own provider key, your OpenAI key included, from $7/month or $60/year — precisely the arrangement many people searching for a ChatGPT Excel add-in are trying to assemble by hand. Otherwise $10–20/month on HISAB's own credits, or a $199 perpetual licence.

The limits, stated plainly: Windows desktop Excel only (Microsoft 365 or Excel 2016+ on 64-bit Windows) — not Mac, not Excel on the web, which is exactly where the official OpenAI add-in does work. It is paid; the 15-day trial is the full product with 50 credits and no card, but there is no free tier afterwards. Every write back to a ledger should be reviewed before you approve it. And if your work is entirely inside one workbook and never touches an accounting system, route 1 is the cheaper answer — take it.

The five routes against this one job

Same five routes, scored on the questions that decide the coding job rather than on general capability.

What coding one client's year actually costs

Three different meters. The official add-in has no separate bill — it draws down the allowance of the plan you already pay for. Marketplace add-ins sell credits or a subscription, in the region of $25 to $29 to get started. Your own API key is billed per token, and for bulk classification the small models are the right choice: at rates published in September 2026, gpt-5-mini sat at around $0.25 per million input tokens and $2.00 per million output, against roughly $5.00 and $30.00 for the flagship GPT-5.5.

Put numbers on the worked example: 2,000 narratives at about 60 input and 5 output tokens each is roughly 120,000 input and 10,000 output tokens — about five cents on the small model, under a dollar on the flagship. The cost is not the constraint; the recalculation you forgot to freeze is. Check OpenAI's current pricing page before you budget — model names and rates here change every few months, and the figures above are a snapshot, not a quote.

Now run the same arithmetic on the stem method, because it is the honest way to settle which is better. Classifying 150 stems instead of 2,000 rows is roughly 9,000 input and 750 output tokens — well under a cent on the small model, a few cents on the flagship. Across twenty bookkeeping clients four times a year that is the difference between about four dollars and about thirty cents, and anyone who tells you that is the reason to do it is selling something. On these numbers the money is noise either way.

What the stem method actually saves is the two things you cannot buy back. The first is review: 150 rules is a list a person can genuinely read, argue with and sign, and 2,000 labels is a list nobody reads, which means the second reviewer samples twenty-five rows and hopes. The second is the re-run. A live column of 2,000 formulas re-bills and re-answers every time the file recalculates, so the real comparison is not one run against one run — it is one deliberate run of 150 stems against however many accidental runs of 2,000 rows happen between now and the file being archived. That is where a five-cent job quietly becomes a recurring one, and where the answer in the file stops matching the answer you signed.

What the reviewer should test

If you are reviewing this rather than preparing it, the work is short and specific. Five tests, none of which needs you to re-code anything yourself.

1. Confirm column D holds values, not formulas. Click into three or four cells and look at the formula bar, or put =SUMPRODUCT(--ISFORMULA($D$2:$D$2001)) beside the range; it should return zero. A live AI formula anywhere in the coded column means the file you are signing can change after you sign it, and that is the finding — everything else on this list is secondary to it.
2. Run the distribution against the account codes. =COUNTIF($D$2:$D$2001, F2) down the list of codes used. You are looking for shapes that do not fit the client: a suspense or sundries code carrying hundreds of rows, a code with a single transaction in it that should probably sit somewhere else, an expense code with no entries at all in a business you know incurs that cost.
3. Count the REVIEW bucket. It should be zero in the final column. If it is not, the file is not finished; if it was zero on the first run, be suspicious rather than pleased, because it usually means the prompt was not given an escape hatch and the model guessed instead of flagging.
4. Sample twenty-five coded lines back to the narrative. Pick them across the value range rather than off the top, include at least a few from the largest code and a few from the smallest, and read the narrative against the code yourself. You are testing whether the rules are right, not whether the lookup worked.
5. Confirm the rules table is saved with the file. Open the Rules tab, check it has a reviewed-by and a reviewed-on, and check the account codes on it match the client's current chart of accounts rather than last year's. A coding run whose rules table has gone missing cannot be reperformed at all, which puts you back to reading 2,000 lines.

One thing to be explicit about with the preparer: none of the above tests the VAT treatment, the capital-versus-revenue calls, or the cut-off. Those were never in the narrative, so they were never in the model's answer, and they are still entirely yours.

Choosing, for this job

• You already pay for ChatGPT: use the official add-in to build and argue about the rules table, not to fill the column.
• One prompt applied to thousands of rows: a =GPT() marketplace add-in, results converted to values the moment you accept them — and classify the distinct stems, not every row.
• Twenty clients on the same monthly cycle: the API direct, with the key kept out of the workbook and a spend cap on it, because a repeatable run is worth building once.
• You were about to build this in Power Query: use it for the stem extraction and the join, and something else for the calls.
• The codes live in an accounting system and the answer goes back to one: the bundled-model, ledger-connected class is the only route that closes that loop — at the cost of being Windows-only and paid.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo and FreshBooks. The 15-day trial is the full product, no card required.