Categorize bank transactions in Excel with Copilot, Claude or ChatGPT

Copilot in Excel, Claude for Excel and ChatGPT for Excel can all read a bank export inside a workbook and propose a general ledger account for each line, and each vendor documents a review step before the result is used. The tool choice changed in September 2026: Microsoft retired the COPILOT worksheet function on September 14, 2026, so the cell-by-cell formula approach that many bookkeepers tested during the preview no longer works, and Microsoft now points users to the Copilot pane for classification tasks. Requirements and limits below were checked against each vendor's official documentation on November 7, 2026.

The task itself is old and familiar. A bank or card export arrives with cryptic descriptions such as "ADOBE CREATIVE CLD" or "ACH ACME LOGISTICS INV 4471", and someone has to decide which account each line belongs to before the month can close. AI tools are good at reading those descriptions and matching them to a chart of accounts, and they are weak at the accounting judgment hidden behind some of them: whether a payment settles a bill already recorded in accounts payable, whether a transfer moves cash between the company's own accounts, or whether a loan payment needs a split between principal and interest.

The worked example in the following sections uses thirteen illustrative transactions and gives the correct category totals, so the output of any assistant can be checked line by line. The most expensive error in the example is easy to miss: if the assistant books the accounts payable payment and the savings transfer as expenses, the month's expenses are overstated by $6,980.00 on a true expense total of $754.49.

··········

⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣

WHY FINANCE TEAMS CATEGORIZE BANK TRANSACTIONS OUTSIDE THE LEDGER.

Most accounting systems have bank rules and feed matching, and many teams still categorize in a spreadsheet. The usual reasons are practical: a new client arrives with months of uncategorized history, a card program exports to CSV before it is connected to the ledger, a controller wants to review coding in bulk before import, or a cleanup project needs a reviewer's column next to the proposed account. A spreadsheet gives one view of every line, the proposed account, the reason and the reviewer's decision, which a feed screen rarely does.

The output of the exercise is an import-ready list or a set of journal entries, so the categorization must respect the chart of accounts exactly. An assistant that invents a plausible account name such as "Software Expense" when the chart says "6100 Software subscriptions" creates an import error or, worse, a new account. The account list therefore belongs in the workbook as a named table, and every prompt should require the assistant to choose only from it. The same discipline applies to the expense classification used in reporting, which is discussed in how to classify expenses by function or by nature in the income statement.

Categorization also feeds the bank reconciliation. A line coded to the wrong balance sheet account still reconciles to the bank statement, so a clean reconciliation does not prove the coding is right. The two controls work together, as described in the month-end close checklist and bank reconciliation with Copilot in Excel, Claude and ChatGPT.

··········

COPILOT, CLAUDE AND CHATGPT IN EXCEL: DOCUMENTED REQUIREMENTS.

Each tool runs inside the workbook and each has its own entitlement rules. The table lists only what the vendors' documentation states.

........

Tool	Documented access	Platforms	Documented limits
Copilot in Excel	Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Copilot subscription, or a Copilot Chat-eligible business or enterprise plan	Windows, Mac, web, iPad, iPhone, Android; edit mode on Windows, Mac and web	Calculation Options must be Automatic; Strict Open XML files unsupported
COPILOT function	Retired September 14, 2026	None	Cached results remain; recalculated cells return #NAME?
Claude for Excel	Claude Pro, Max, Team and Enterprise	Excel on the web, Windows (Microsoft 365, build 16.0.13127.20296 or later), Mac (16.46 or later)	No data tables, macros or VBA; not on iPad or Android
ChatGPT for Excel	Generally available across ChatGPT plans since May 5, 2026; at launch, off by default in Enterprise and Edu workspaces	Excel add-in; Google Sheets	Asks approval before workbook changes; edits can be undone
ChatGPT data analysis	File upload in a ChatGPT conversation	ChatGPT	Spreadsheets about 50 MB; 10 files per conversation

........

Microsoft's FAQ for Copilot in Excel says Copilot can make mistakes and advises users to "avoid using Copilot for decisions in sensitive areas such as finance, legal, or medical topics", and to review and verify anything it creates. Anthropic states that Claude for Excel is not recommended for audit-critical calculations without verification or for final deliverables without human review. OpenAI's launch material notes that beta outputs may need cleanup and that complex cases may need manual refinement. All three positions point to the same workflow: the assistant proposes, a person approves, and the totals are checked with ordinary formulas.

Model choice inside Copilot is also documented. Microsoft states that switching models requires a commercial Copilot or Microsoft 365 Premium subscription, and that enterprise admins must enable Anthropic as a Microsoft sub-processor before users can select Claude inside Copilot. That is a separate route from the Claude for Excel add-in, which signs in with a Claude account.

··········

WORKED EXAMPLE: CATEGORIZING AN OCTOBER BANK EXPORT IN EXCEL.

The dataset below is illustrative. It represents one month of a small US company's operating account, with outflows as negative amounts. The fourth column is the correct account, which a reviewer would reach with knowledge of the business: Acme Logistics invoice 4471 was already recorded in accounts payable in September, the Stripe transfer is a payout of customer invoices already in receivables, and the transfer to account 4412 moves cash to the company's own savings account.

........

Date	Description	Amount (USD)	Correct account
10/01	ADOBE CREATIVE CLD	-59.99	6100 Software subscriptions
10/02	DELTA AIR 0062345	-412.60	6200 Travel
10/03	STRIPE TRANSFER ST-8842	3,250.00	1200 Accounts receivable
10/06	STAPLES 00123	-86.45	6400 Office supplies
10/07	ACH ACME LOGISTICS INV 4471	-1,980.00	2100 Accounts payable
10/09	UBER TRIP 7731	-38.20	6200 Travel
10/10	MONTHLY SERVICE FEE	-25.00	6500 Bank fees
10/14	ONLINE TRANSFER TO SAV 4412	-5,000.00	1050 Transfer clearing
10/15	SLACK T0123	-87.50	6100 Software subscriptions
10/20	STARBUCKS #2231	-14.75	6300 Meals
10/22	LOAN PMT 0098	-1,250.00	Needs review
10/28	AMAZON MKTPLACE	-129.99	Needs review
10/30	WIRE FEE	-30.00	6500 Bank fees

........

Two lines are deliberately left for review. The loan payment needs the lender's statement to split principal from interest, and the marketplace purchase could be office supplies or a small equipment item depending on what was bought. A good assistant flags both; a weak one assigns them confidently.

Put the export in an Excel table named Bank and the chart of accounts in a table named Accounts, then give the assistant a prompt that fixes the account list, the review rule and the output columns. The same prompt works in the Copilot pane, in the Claude for Excel sidebar and in ChatGPT for Excel.

Categorize each row of the Bank table. Use only accounts that appear in the Accounts table, written exactly as listed.
Add three columns to the right: Proposed account, Reason (one short sentence), Confidence (High, Medium, Low).
Payments that reference a vendor invoice number settle accounts payable, not an expense.
Transfers between our own accounts go to 1050 Transfer clearing.
Payouts from Stripe settle customer receivables in 1200.
If a row needs a document to decide (loan payments, mixed purchases), write "Needs review" and say which document is needed.
Do not change the Date, Description or Amount columns.

A second prompt asks the assistant to summarize its own work in a way that can be tested against formulas, which makes errors visible quickly.

Build a summary on a new sheet named Check: one row per proposed account with the count of rows and the total amount, plus a row for "Needs review".
Add a final row with the total of all amounts in the Bank table and the total of the summary.
List any row whose proposed account is not in the Accounts table.

··········

[[ADPLUS_300x250_1]]

EXPECTED RESULT OF THE WORKED EXAMPLE.

The correct summary, computed independently of any assistant, is shown below. Amounts are signed as in the bank export.

........

Account	Rows	Total (USD)
1050 Transfer clearing	1	-5,000.00
1200 Accounts receivable	1	3,250.00
2100 Accounts payable	1	-1,980.00
6100 Software subscriptions	2	-147.49
6200 Travel	2	-450.80
6300 Meals	1	-14.75
6400 Office supplies	1	-86.45
6500 Bank fees	2	-55.00
Needs review	2	-1,379.99
Total, equal to net bank movement	13	-5,864.48

........

Expenses that are certain total $754.49 across five expense accounts. Balance sheet lines net to an outflow of $3,730.00: the $5,000.00 transfer and the $1,980.00 vendor payment less the $3,250.00 Stripe payout. The two review items total $1,379.99. Outflows for the month are $9,114.48 and inflows $3,250.00, which gives the net movement of -$5,864.48.

The figures give three tests that any assistant output must pass. The summary total must equal the bank total, the row count must be 13, and the expense accounts must total -$754.49 before the review items are resolved. An output that shows expenses of $7,734.49 has booked the vendor payment and the savings transfer to expense, and an output that shows revenue of $3,250.00 has treated the Stripe payout as new sales instead of collection of receivables already recognized.

The checks are plain Excel formulas and do not depend on the assistant. With the proposed account in column E of the Bank table, the formulas below test the totals and catch invented accounts.

=SUM(Bank[Amount])
=SUMIFS(Bank[Amount], Bank[Proposed account], "6100 Software subscriptions")
=SUMPRODUCT(--(LEFT(Bank[Proposed account], 1)="6"), Bank[Amount])
=COUNTIF(Accounts[Account], [@[Proposed account]])=0

The third formula returns -754.49 on the correct coding. The fourth, entered as a column inside the Bank table, returns TRUE for any row whose proposed account does not exist in the chart, including "Needs review", which should be the only TRUE value. Teams that prefer code can reproduce the same checks with Python in Excel, as shown in how to use Python in Excel for finance.

··········

REVIEW CONTROLS FOR AI EXPENSE CATEGORIZATION.

The errors that matter in this task are predictable, so the review can be targeted. The table lists the common failure patterns in the example and the check that catches each one.

........

Failure pattern	Effect in the example	Check that catches it
Vendor payment coded to expense	Expenses overstated by $1,980.00	Search descriptions for invoice numbers; match to open AP
Own-account transfer coded to expense	Expenses overstated by $5,000.00	Transfer clearing nets to zero across both accounts
Processor payout coded to revenue	Revenue overstated by $3,250.00	Match payout to receivables or the processor report
Invented account name	Import fails or creates a new account	COUNTIF against the Accounts table
Confident guess on mixed items	Loan interest and principal misstated	Require "Needs review" with the document named
Sign reversed on refunds	Expense and refund both counted	Compare summary total to bank total

........

Three practices reduce the review effort. Keep the assistant's proposal in its own column and add a separate Approved account column for the reviewer, so the audit trail shows what was suggested and what was accepted. Sort by the Confidence column and review Low and Medium rows first, while still sampling High rows, because a confident label is no evidence of a correct one. Feed approved decisions back as rules for the next month by adding a short table of description patterns and accounts, and tell the assistant to apply that table before proposing anything new.

Posting is a separate step. Once the coding is approved, the lines become journal entries or an import file, and the controls for that step are covered in journal entries with Copilot, Claude or ChatGPT: draft and check before posting. The person who approves the coding should not be the only person who approves the posting for material amounts.

··········

⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁣⁤

DATA PRIVACY WHEN AI READS BANK EXPORTS.

Bank exports contain counterparty names, account fragments and sometimes employee names on card lines, so the data-handling terms of each tool belong in the decision. Anthropic's documentation for Claude for Excel states that, when a user signs in with a Claude account, conversations are sent to Anthropic, including workbook content the assistant reads and attached files; that inputs and outputs are deleted on the backend within 30 days except in cases described in its retention policy; that chat history is stored locally in the browser; that the add-in does not inherit an organization's custom retention settings; and that its activity is not included in Enterprise audit logs, although Enterprise organizations with the Compliance API enabled receive the sessions there.

Anthropic also warns that files from external sources, such as vendor files and downloaded templates, can contain hidden instructions that try to make the add-in extract data or modify records, and recommends using it only with trusted spreadsheets. A bank export from the bank's own portal is lower risk than a spreadsheet received from a third party, and the confirmation prompts for risky operations should be read before they are approved.

OpenAI describes ChatGPT Enterprise controls including SAML SSO, SCIM, role-based access, audit logs, encryption in transit and at rest, and data residency options, and states that Enterprise data is not used to train its models by default. For file uploads to ChatGPT data analysis, OpenAI's help page states that each conversation's code environment is destroyed within 13 hours of the conversation becoming inactive. Microsoft directs work and school users of Copilot in Excel to its Data, Privacy, and Security for Microsoft Copilot documentation, and notes that changes saved to a workbook are visible to anyone with access to the file. Account numbers can be masked or removed from the export before any of these tools read it, since the description and amount are enough to categorize.

··········

AI BANK TRANSACTION CATEGORIZATION IN EXCEL: WHAT THE TOOLS DO AND WHERE REVIEW STAYS HUMAN.

The documented capabilities support a clear division of labor. Copilot in Excel, Claude for Excel and ChatGPT for Excel can read descriptions, propose accounts from a defined chart, explain each choice and build a summary sheet, and Microsoft has replaced the retired COPILOT function with the Copilot pane for this kind of classification. Entitlements differ by vendor and plan, and the documentation from Microsoft and Anthropic tells users to verify the output before relying on it.

The judgment calls stay with the accountant: whether a payment settles a recorded liability, whether a transfer is internal, whether a receipt is collection or new revenue, and how a loan payment splits. In the example those calls cover $11,609.99 of the $12,364.48 gross movement, compared with $754.49 of routine expense coding, which is why the reconciliation formulas, the invented-account check and a separate approval column belong in every categorization workbook. How much review time the assistants save depends on the quality of the chart of accounts, the rules table carried from month to month and the share of transactions that need a document to decide, none of which the vendors quantify.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org