ChatGPT for Accounting With Excel: Seven Tasks, Each With Its Check

An accountant can use ChatGPT with Excel to clean bank exports, write lookups, build pivots, bucket ageing, draw up depreciation schedules, explain variances and tick-and-tie schedules, provided every result is checked against an independent total on a copy of the file. The formula is the deliverable; the check is what makes it safe.

ChatGPT can read uploaded spreadsheets (.xls, .xlsx and .csv) and, for some tasks, writes and runs Python to analyse them, according to OpenAI's help page. The same page warns that a file can upload successfully yet be too large or poorly structured for complete analysis, that the number and complexity of formulas can affect processing, and that ChatGPT may pick a method that does not match your intent. Both warnings point to one habit: for each of the seven tasks, know what to ask, what a realistic result looks like and the one check that tells you whether to trust it.

Follow me on Instagram@sagnikteaches

Ground rules for all seven tasks

Work on a copy, never the original. Save "Client_Recon_COPY.xlsx" and keep the original read-only, so that you can always compare what you started with. Anonymise before you upload: swap names and account numbers for codes, and delete columns the task does not need. If your engagement terms or the client's policy do not allow uploading data to a third party, ask ChatGPT for the formula using a three-row made-up sample and run it yourself in Excel.

Connect on LinkedInSagnik Bhattacharya

Ask for formulas and steps, not finished numbers. A formula can be audited in the cell; a number in a chat window cannot. And decide the independent total before you start: the bank statement closing balance, the ledger control account, the trial balance. Each task below ends by tying to one of those. If you want a method for a deeper reconciliation workflow, automating bank reconciliation and checking the matches goes further.

Subscribe on YouTube@sagnikteaches

Task 1: clean a bank export

Bank CSVs arrive with text dates, amounts in two columns, stray spaces and sometimes a header repeated halfway down. Describe the layout rather than pasting real transactions: "Column A holds dates as text like 03/11/2026, column B description with trailing spaces, column C debits, column D credits, both blank when not used."

I have a bank export in Excel: A = date as text (dd/mm/yyyy), B = description with trailing spaces,
C = debit (blank if credit), D = credit (blank if debit).
Give me formulas for helper columns E to G: clean description, real date, signed amount (credits positive, debits negative).
Assume the data starts in row 2. Explain each formula in one line.

A realistic answer gives =TRIM(B2) for the description, a date formula using DATE with MID or DATEVALUE depending on regional settings, and =N(D2)-N(C2) or =SUM(D2)-SUM(C2) for the signed amount. The usual flaw is the date: DATEVALUE depends on the computer's regional settings and can silently swap day and month. Check: filter the real-date column for the earliest and latest dates and make sure they sit inside the statement period, then confirm that the sum of the signed column equals closing balance minus opening balance on the statement. If both agree, the clean-up is safe.

Task 2: lookups that match ledger to bank or invoices to payments

Ask for XLOOKUP or INDEX/MATCH by describing the two tables: which column is the key, which column you want back and what to do when there is no match. Tell it what a missing key means in your business, because "not found" is often the finding rather than the error.

Sheet Bank has references in column B. Sheet Ledger has references in column A and amounts in column D.
In Bank column F, return the ledger amount for each reference, or "NOT IN LEDGER" if missing.
Then give me a formula that shows the difference between the bank amount (column C) and the ledger amount.
Warn me about duplicate references.

Expect an XLOOKUP with an if_not_found argument and a subtraction. If you are on Excel 2016 or 2019, which Microsoft says lack XLOOKUP, say so in the prompt and ask for INDEX/MATCH. The failure that matters is the duplicate key: a lookup returns the first match and ignores the second, so two payments with the same reference look like one. Check with =COUNTIF(Ledger!A:A, B2)>1 flagged in a column, and count the "NOT IN LEDGER" rows: your matched plus unmatched must equal the bank row count, or rows have been dropped.

Task 3: pivot tables and the summary behind them

A pivot is quick to build by hand, so the useful ask is setup advice for awkward data: which fields go in rows, columns and values, how to group dates by month and how to stop blank rows breaking the table. ChatGPT can walk you through the clicks for the Excel version you describe.

I have 4,200 expense rows with columns Date, Supplier, Category, Cost Centre, Amount.
I want spend by Category per month with a grand total, and a second view of top ten suppliers.
Give me step-by-step Excel pivot instructions and a formula to check the grand total against the source.

A good answer says: insert the pivot from a table, drag Category to Rows, Date to Columns grouped by Months and Years, Amount to Values as Sum, and use a Top 10 filter on Supplier. The classic trap is numbers stored as text, which a pivot counts instead of summing. Check: the pivot grand total must equal =SUM(Table[Amount]) on the source, and the row count in the pivot (set Values to Count) must equal the table row count. If the Sum differs but the Count matches, some amounts are text.

Task 4: receivables ageing buckets

Ageing is simple logic that is easy to get subtly wrong, usually at bucket boundaries and with the as-at date. Fix the as-at date in one cell so the report does not shift when someone reopens it tomorrow.

Invoices are in A:E: Customer, Invoice No, Invoice Date, Due Date, Balance.
Put an as-at date in cell H1. Give me a days-overdue formula and a bucket formula returning
"Current", "1-30", "31-60", "61-90" or "90+". Then a summary table with SUMIFS for each bucket.

A sound answer uses =MAX(0,$H$1-D2) for days overdue and a nested IF or IFS for buckets, plus SUMIFS by bucket. Ask whether ageing is by due date or invoice date, because the answer changes every number and is a policy choice, not a formula choice. Illustration: three invoices of $1,200, $800 and $2,500, overdue 12, 45 and 95 days, fall into 1-30, 31-60 and 90+. Check: the bucket totals must add to the receivables control account in the ledger, and an invoice exactly 30 days overdue must sit in the bucket your policy says. If you want to rank who to chase, letting AI rank who to chase first picks up where this leaves off.

Task 5: depreciation schedules

Depreciation is formula-driven and has right answers, so it is a good test of whether ChatGPT is following your method. Name the method and give the inputs. For a straight-line asset costing $24,000, residual value $4,000, five-year life, the annual charge is $4,000, which Excel's =SLN(24000,4000,5) returns. For 20 per cent reducing balance on the same cost, year one is $4,800 and year two is $3,840, leaving a net book value of $15,360.

Build a depreciation schedule layout for an asset register with columns: Asset, Cost, Purchase Date, Residual Value, Life (years), Method.
Use SLN for straight line and a reducing-balance formula at a rate in column G.
Show year-by-year charge, accumulated depreciation and net book value for five years.
State your assumption on part-year charges: I want a full year in the year of purchase.

The assumption sentence matters. Models guess at part-year rules, and your policy may differ from their default. Check: recompute one asset by hand using the figures above, confirm the final-year net book value equals the residual value for straight line, and tie total accumulated depreciation to the fixed-asset ledger. If the totals do not tie, look first at assets bought mid-year.

Task 6: variance commentary

Variance analysis combines a calculation, which Excel should do, and an explanation, where ChatGPT can help you draft questions. Compute the variances with formulas in the sheet and ask ChatGPT only to organise them.

Here is a table of budget vs actual by line with variance and variance %: [paste anonymised table].
Flag lines where the variance exceeds $500 and 10 per cent.
For each flagged line, write one question a finance manager should ask the budget holder.
Do not suggest causes as if they were facts.

Illustration: rent is $12,600 against a budget of $12,000 (a $600 or 5.0 per cent variance, not flagged because it fails the percentage test), while marketing is $5,400 against $4,500 (a $900 or 20 per cent variance, flagged). The model's question for marketing might be "Was the extra spend committed in a signed order, and does it bring forward spend from later months?" Check: recalculate two flagged variances by hand and confirm that the thresholds were applied with "and", not "or". Variance commentary that offers causes without evidence should go in your notes as hypotheses, not in the pack.

Task 7: tick-and-tie of a schedule to the trial balance

Tick-and-tie means agreeing every figure in a schedule to its source and footing the totals. ChatGPT can design the check layout and spot mismatches in a pasted pair of tables, but you must supply both sides.

Table 1 is a prepayments schedule (Account, Amount). Table 2 is the trial balance extract (Account, Balance).
Show me formulas to compare them by account, flag differences over $1, and total each side.
Also tell me how to prove the schedule foots (adds up) independently of the formulas.

A good answer gives a lookup per account, a difference column, conditional formatting for non-zero differences and a note that you can foot by comparing =SUM of the column with a calculator total or with a second method such as a pivot. Check: every account on the TB extract appears on the schedule or is explained, because a lookup from schedule to TB will never reveal TB accounts missing from the schedule. Do the lookup both ways and count the unmatched on each side.

184 bank lines: a month-end reconciliation through the tasks

Illustration only. Say a small practice reconciles a client's current account: 184 bank lines and 176 ledger lines. Task 1 cleans the export; the signed amounts total a net movement of $18,430.55, matching the statement's closing minus opening balance. Task 2 matches by reference, returning 169 matches, 15 bank lines "NOT IN LEDGER" (169 + 15 = 184, so no rows are lost) and 7 ledger lines with no bank match. The duplicates check flags two references appearing twice, both genuine split payments.

Of the 15 unmatched bank lines, 11 are bank fees and interest not yet posted, 3 are timing items, and 1 is a $1,150 receipt with a typo in the reference, which a person finds by sorting amounts. The reconciliation then ties: after the fees and interest are posted and the mistyped reference is corrected, the ledger and bank balances differ only by the 3 timing items and the 7 ledger entries not yet on the statement, each of which a person has reviewed. Nothing in this relied on trusting ChatGPT; it wrote formulas, the totals proved them. The time saving was in writing and debugging formulas, not in the judgement about the 15 items.

Compare with another approach in Claude for Excel for accountants, which makes a similar point about reviewer sign-off, and with Xero's bank reconciliation assistant if your client's books live in accounting software rather than a workbook.

When the check fails: what to look at first, task by task

A failed check is the system working. The question is where to look, and the table below gives the first suspect for each task.

Tell ChatGPT what failed rather than asking it to "fix it". "The signed total is $412.30 higher than the statement movement; here are three sample rows" gives it something to diagnose. Then test the repaired formula on a copy again; repairs are where fresh errors enter.

Small examples that show the pattern

The same habits apply to jobs outside the seven. A bookshop's stock-take file has item codes in two formats ("BK-0042" and "bk0042"); ask for a formula that normalises both, then check by counting distinct codes before and after, which must not increase. A florist's weekly takings workbook has dates as text: after conversion, the sum of daily takings by week must equal the sum of the original weekly totals. A driving school's fuel log needs cost per lesson: compute total fuel divided by lessons in the sheet and check one month by hand against receipts.

Each follows the same three beats: describe the layout, ask for a formula, prove it against something independent. A photography studio's deposit tracker might use the ageing approach on unpaid deposits. In each case the checks take longer to describe than to run, usually a couple of minutes, and they are what separates a quick draft from something you would put your name to.

Prompt hygiene: describing data without exposing it

You rarely need real data to get a good formula. Build a three-to-five row sample with invented names and round numbers that has the same shape as the real sheet, including the awkward rows: a blank, a duplicate, a negative, text in a number column. Ask ChatGPT for the formula on that sample, test it on the sample, then copy the formula across to the real workbook inside Excel. You get most of the benefit and expose none of the client's figures.

Where uploading the real file is allowed and useful, remove what the task does not need. For a lookup, you may need only the reference and amount columns. For a pivot, you may not need supplier names; a code will do. Record in your method sheet what was removed. Spreadsheets can also hold hidden sheets, comments and document properties with names in them, so inspect the file before uploading and delete what does not belong.

Keep the prompts themselves tidy. Include the Excel version or the product (desktop, web), the regional date format, the cell references and the policy choices (bucket basis, depreciation convention, rounding). A prompt that spells these out gets a formula that fits first time more often, and the prompt doubles as documentation for the next person who picks up the file.

Choosing which task to start with

If you are adopting this in a small practice, do not start with all seven. Begin with the bank-export clean-up and the lookups, because their checks are the simplest (a net movement and a row count) and the saving is felt every month. Add the pivot and ageing work next. Leave depreciation and tick-and-tie until the team is comfortable with the copy-first habit, since they touch figures that feed the financial statements and the review trail matters more.

Time yourself on one real month before and after. Illustratively, a bank clean-up that takes 25 minutes by hand might take 10 with formulas written for you, plus 5 minutes to run the two checks. If the saving is under a third once checks are included, the task is not worth changing for that client.

How to keep a trail a reviewer will accept

If a manager or partner reviews your file, they will ask how the numbers were produced. Keep a "Method" sheet in the workbook with four lines per task: the question you asked ChatGPT in summary (not client data), the formula it suggested, any change you made and the check you ran with its result. Date it and initial it. A reviewer can then rerun the check without trusting anything said in a chat.

Save the final formulas in the workbook, not only the chat, because chats can be deleted or changed and files are what you retain. If your firm has an AI-use policy, note the tool, the plan type and whether the data was anonymised. OpenAI's enterprise privacy page says business-plan data is not used for training by default, while on Plus or Pro the "Improve the model for everyone" switch under Data controls stays on until someone turns it off; confirm which applies to your account before any client data is involved.

Where ChatGPT misleads accountants most

Four patterns recur. It assumes a clean layout: merged cells, subtotals inside the data and blank rows break formulas it writes confidently. It rounds, so totals that should tie to the cent differ by a cent or two; ask for ROUND in the formulas and test to the cent. It invents function names or arguments occasionally, which Excel shows as #NAME?, an easy catch. And it explains a result smoothly even when the result is wrong, so a plausible explanation is never evidence.

The defence for all four is the same habit: independent total, copy first, formula visible. For broader error spotting in books, see how accountants use AI to spot errors in client books, and keep a short list of the checks above beside the keyboard until they are routine.

Questions accountants ask about ChatGPT and Excel

Can I upload a client's workbook to ChatGPT?

Only with the client's agreement and a plan whose data terms suit the engagement. Safer: strip names and account numbers, or give ChatGPT a small anonymised sample, ask for the formula, and run it yourself on the real file inside Excel.

Does ChatGPT give correct formulas every time?

No. It often writes sound formulas but can pick the wrong lookup column, mishandle blanks or assume a layout you do not have. Always test on a copy of the workbook and tie the output to an independent total before using it.

Is it better to ask for a formula or for the answer?

Ask for the formula or steps. A formula is visible in the cell, can be audited and recalculates when data changes. A pasted answer from the chat cannot be reviewed, and a reviewer will ask how you got it.

Where does this fit with Excel's own AI features?

They overlap. ChatGPT is useful for reasoning through a method and writing formulas; Excel's own assistants work inside the file. Whichever you use, the checks in this tutorial stay the same, because they test the result, not the tool.

Further reads

• How to Use ChatGPT to Write Excel Formulas (With Real Examples) — The general guide to ChatGPT inside Excel work.
• AI Accounting for Ecommerce: Reconciling Payouts and Fees — Reconciling payouts and fees, a close cousin of task two.
• AI for Insurance Brokerage: Reconcile Insurer Commission Statements — Matching commission statements to what was expected.
• Trust Account Reconciliation for Law Firms: What AI May and May Not Do — Where reconciliation rules make AI use stricter.
• AI Rent Roll Analysis for Multifamily: Reconcile to the Bank — Reconciling a rent roll to the bank.
• How to Automate Client Onboarding in a Small Accounting Practice — Onboarding clients in a small accounting practice.
• AI Accounting Workflow: Client Upload to Reviewed Books, Step by Step — Five stages from client upload to partner review, each with an owner, tool and check, shown through one worked month for a small contractor client.
• ChatGPT for Auditors: Working Paper Drafting and Where It Stops — A small audit practice's guide to ChatGPT for working-paper drafting: what to draft, what to verify, and the evidence trail for reviewers.
• Fund Administration Software for a Small Administrator: What AI Adds — Where AI helps a small fund administrator (statements, reconciliations, review notes) and which checks must stay with a named human reviewer.
• Gemini for Accountants: Gmail, Sheets and AI() for Practice Tasks — Practice tasks for Gemini in Gmail and Sheets, and a tie-out step that checks every =AI() result against the source.
• Power BI for Accountants: Client Dashboards and Copilot Limits — Choose client metrics, reconcile the source and control sharing before paying for Copilot capacity or promising live dashboards.
• AI for Accountants in Canada: Privacy Rules and First Uses — Which privacy laws and CPA conduct rules apply when a Canadian firm uses AI, how to vet a vendor, and where to start safely.
• AI for Doing Tax Accounting: What to Hand Over and What to Review — A small-practice task split for document extraction, tax workpapers and client letters, with a named check before each output goes out.
• Can AI Select an Audit Sample? Document and Defend It — A small-practice method for AI-assisted audit sampling: a reproducible selection in Excel or Python and the documentation wording a reviewer can test.
• AI Accounting for Startups: Monthly Close, Burn and Runway — A founder's monthly close routine that connects checked books to cash burn, runway and accountant review.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: OpenAI help articles on data analysis and data controls, OpenAI enterprise privacy page, and Microsoft Support pages for XLOOKUP, DATEVALUE and SLN, checked 4 November 2026.