If you work with spreadsheets regularly, you already know the frustration. You're staring at two data sets that should match, manually cross-referencing rows, hunting for discrepancies. Maybe it's a bank reconciliation, maybe it's checking supplier invoices against purchase orders. Either way, it's tedious, error-prone, and it eats into time you could spend on higher-value work.
Here's something I've been using with clients that genuinely changes the game: Claude AI can write Excel formulas for you. You describe what you need in plain English, and it gives you a working formula — often a better one than you'd have written yourself. No coding required, no expensive enterprise tools. Just a conversation.
I want to walk you through exactly how this works, using a bank reconciliation as the example. But more importantly, I want to show you the control checks you need to put in place — because trusting AI output blindly with financial data is never the right move.
All You Need Is a Claude Subscription
This isn't about buying expensive software or hiring a developer. A Claude Pro subscription is all you need. You open the Claude chat interface, describe the problem you're trying to solve, and it writes the formula for you.
What makes this particularly useful for finance and admin work is that Claude understands spreadsheet logic well. You can describe your data layout in plain English — "I have dates in column A, descriptions in column B, amounts in column C" — and it will generate formulas that reference those columns correctly. No need to learn complex syntax from scratch.
It's not just formulas either. You can ask Claude to explain what a formula does, troubleshoot why one isn't working, or suggest a better approach to a calculation you've been doing manually. Think of it as having a very patient spreadsheet expert available whenever you need one.
Enable Privacy Mode First — This Is Non-Negotiable
Before you paste any business data into Claude, there's one step you must take. Go to your Claude settings and enable Privacy mode. This is sometimes labelled as "Don't use my conversations for training" depending on which plan you're on.
This ensures your sensitive financial data — bank transactions, invoice amounts, customer details — isn't used to train AI models. For any business use, this is non-negotiable. It takes thirty seconds to set up and it protects your data going forward.
If you're on a Claude Team or Enterprise plan, this is typically enabled by default. But check it anyway. Your business data is your responsibility.
A Worked Example: Bank Reconciliation
Let's make this concrete. Say you've exported your bank statement as a CSV and opened it in Excel. You've also got your sales ledger — the invoices you've issued to customers. The job is to match bank transactions against invoices and find anything that doesn't tie up.
Your bank statement (Sheet1) has four columns: Date (A), Description (B), Amount (C), and Reference (D). Your sales ledger (Sheet2) has five columns: Invoice No (A), Customer (B), Amount (C), Date Issued (D), and Reference (E).
Prompt 1: Matching Transactions
Here's an example prompt you could paste straight into Claude:
"I have a bank statement in Sheet1 with columns: A (Date), B (Description), C (Amount), D (Reference). I have my sales ledger in Sheet2 with columns: A (Invoice No), B (Customer), C (Amount), D (Date Issued), E (Reference). Can you write me a formula to put in Sheet1 column E that matches each bank transaction against my sales ledger based on the Reference column, and shows the matching Invoice No? If there's no match, it should show 'UNMATCHED'."
Claude will typically respond with an XLOOKUP or INDEX-MATCH formula — something like =IFERROR(XLOOKUP(D2,Sheet2!E:E,Sheet2!A:A),"UNMATCHED"). It will also explain what the formula does: look up the bank reference in the ledger's reference column, return the matching invoice number, and show "UNMATCHED" if nothing is found.
That single formula, dragged down your bank statement, does in seconds what might take you an hour to do manually.
Prompt 2: Building a Reconciliation Summary
Now take it further. Ask Claude:
"Now can you create formulas for a reconciliation summary showing: total number of matched items, total number of unmatched bank transactions, total value of unmatched transactions, and the net difference between total bank statement amount and total ledger amount?"
Claude will give you a set of COUNTIF and SUMIF formulas to build a clean summary table. You'll see at a glance whether everything ties up — and if it doesn't, exactly where the gaps are.
Control Checks: Always Maintain Human-In-The-Loop
This is the section I care about most. AI-generated formulas can be impressively accurate, but they can also be subtly wrong. With financial data, "subtly wrong" is not acceptable. Here are the control checks I always put in place:
Check your totals before and after. Before applying any AI-generated formula, note down the grand total of your bank statement and your ledger. After applying the formula, check those totals again. They should be identical. If anything has shifted, something has gone wrong.
Spot-check manually. Pick 5-10 matched items at random and verify them yourself. Does the bank transaction actually correspond to that invoice? Does the amount match? Does the date make sense? This takes five minutes and catches formula errors before they propagate.
Add a differences column. Create a helper column that shows the before-and-after impact of any formula. For a reconciliation, this might be a column that calculates the difference between the bank amount and the matched invoice amount. Anything that isn't zero deserves a closer look.
Ask Claude to explain the formula. Before you apply it, ask: "Can you explain what this formula does step by step?" If the explanation doesn't match what you expect, that's a red flag. You should understand what any formula is doing before it touches your data.
Never skip the human review. AI is a tool. You are the expert. The formula does the heavy lifting, but the final sign-off must always be yours. This is especially true for anything involving money, compliance, or client-facing data.
Beyond Reconciliation: The Broader Opportunity
Bank reconciliation is just one example. Once you're comfortable with this approach, you can use Claude to help with virtually any spreadsheet challenge. Cash flow forecasting, variance analysis, data clean-up, conditional formatting rules, pivot table logic — the list goes on.
The pattern is always the same: describe your data layout, explain what you're trying to achieve, and let Claude suggest the formula. Then apply your control checks before using the output. It's not magic — it's a practical tool that saves you hours of formula research and trial-and-error.
If you've been meaning to explore how AI tools can help with your day-to-day admin and finance work, this is a low-risk, high-reward place to start. Pair it with automating invoice chasing once your reconciliation process is solid. One subscription, one conversation, and you'll see the potential immediately.