Categorize a bank statement by cash flow line in Google Sheets

A bank statement sorted into cash flow lines is the raw material for the cash flow report, the budget comparison and half of the questions management asks. Gemini in Google Sheets can categorize a month of transactions in about 20 minutes without copying the data into a chat. The method is the same as with any assistant: a clear category list, an "uncertain" flag, and a sample check. What changes in Sheets is how you run it and how you keep the result stable.

Prepare the sheet

Export the statement to CSV or Excel and open it in Google Sheets with columns date, counterparty, amount, payment reference. Replace counterparty names and account numbers with codes, keeping the payment reference, which is what the model reads. Headers in the first row, one data type per column, dates as real dates, amounts as numbers. The panel gives wrong answers on merged cells and text dates.

Write the category list

Write your cash flow categories with a short description of each: "Supplier payments: goods and materials for resale", "Payroll: salaries and advances", "Taxes: all payments to tax authorities". Descriptions settle the edge cases. Put the list in a separate range on the sheet so you can reference it.

Two ways to run it

The side panel works on a selected range and returns a table, which suits a one-off statement:

Role: you are an accountant assigning a bank statement to cash flow categories. Context: the company works in {{industry}}. Categories: {{list of categories with descriptions}}. The statement below has date, counterparty code, amount, payment reference. Task: assign a category to every row. If your confidence is below high, mark it "uncertain" and explain which categories you are choosing between. Format: a table date | counterparty code | amount | category | confidence (high or uncertain) | comment. After the table, totals by category and the list of rules you used to classify, so I can correct them. Statement: {{statement}}

The =AI() function fills a category column row by row, which suits a statement you update every month. It uses quota on every row and may give identical rows different answers, so reference the category list in the formula and freeze results as values once checked.

Check what matters

Check every row marked "uncertain" and ten random rows with a confident classification. Then filter negative amounts and check them separately: refunds and reversals often share wording with the original payment, and the model files them under the same category with the wrong sign logic.

The list of rules the model used is worth reading. It shows its reasoning ("payments mentioning 'rent' go to Rent"), and correcting one rule fixes every row it applied to. Add the corrected rules to the category descriptions for next month.

Totals by formula

Build the totals by category with SUMIF on the frozen values, not from the model's summary. The totals must add up to the statement's total movement; if they do not, a row is missing a category.

What to do

1. Export the statement with date, counterparty, amount and payment reference.
2. Replace counterparty names and account numbers with codes.
3. Write the cash flow categories with short descriptions in a separate range.
4. Run the side panel prompt or the =AI() function against the list.
5. Check every uncertain row, ten random confident rows and all negative amounts.
6. Correct the rules, freeze the result as values and build totals by formula.

Categorized

More than 90% of rows are correct in the sample check, and every uncertain row was flagged and reviewed by a person.

Plans

Gemini in Sheets comes with Workspace Business Standard at 14 USD per user with annual billing, where company data stays out of model training, or Google AI Pro at 19.99 USD per month for a personal account. See Gemini in Google Sheets pricing, the accountant's version of this task and Gemini in Google Sheets vs ChatGPT.