From A Messy Trial Balance to Clean Financial Statements in 15 mins using Claude+Excel.

If you work in finance, you already know the pain point.

You export a trial balance from an ERP or accounting system, open the file, and before you can do any real analysis, you lose hours just fixing the structure. Blank categories, duplicate account names, inconsistent formatting, numbers stored as text, stray subtotals, messy labels. By the time the data is usable, half the job already feels done.

This is exactly where AI inside Excel becomes genuinely useful.

Instead of treating AI like a gimmick, you can use it for a practical FP&A workflow: take a messy trial balance, clean it, map the chart of accounts, and turn it into an income statement, balance sheet, and cash flow statement in a fraction of the usual time.

The workflow here uses Claude inside Excel, but the bigger point is not just the tool. It is the process. Once that process is clear, you can start applying the same logic to your own finance data and reporting work.

Want to see this whole thing happen live instead of reading about it?

I ran the full workflow, messy trial balance to three finished statements, in a recent Lightning Lesson. You can watch the recording here and follow along with your own file.

What this workflow actually does

The full process has three main steps:

Clean the raw trial balance
Map accounts to financial statement line items
Generate the financial statements and validate them

In the example, the input is a sample trial balance for the current year along with a prior year trial balance for comparison. The current year file looks like what finance teams often get from source systems: usable in theory, but not ready for analysis.

Before anything else: how to set up Claude inside Excel

If you want to follow the same workflow, the setup is fairly straightforward.

You need:

Excel 2016 or newer, ideally Excel 365
A paid Claude account, because the Excel add-in does not work with the free version at the moment

Inside Excel, go to the add-ins section and search for Claude. Make sure you install the official Microsoft 365 add-in from Anthropic, not an unofficial lookalike.

Once installed, the add-in appears as a panel on the right side of Excel. You then sign in to your Claude account, authorize the Microsoft add-in, copy the authorization code, and paste it into the add-in. That part usually takes less than a minute.

The biggest thing to watch for is company IT policy. If you are on a work laptop and external add-ins are blocked, the installation may fail unless IT approves it. In that case, either get permission or test on a personal device first.

What usually goes wrong with a raw trial balance

Before asking AI to clean anything, it helps to be clear about what makes the data messy in the first place.

In the sample file, the issues were very typical:

Inconsistent account naming
Missing or blank categories
Duplicate or overlapping account descriptions
Formatting inconsistencies
Currency formatting problems
Potential numbers stored as text
Subtotal rows mixed in with actual line items

None of these issues are unusual. In fact, that is exactly why this use case matters. It is repetitive, time-consuming, and rules-based enough for AI to help.

Prompt 1: audit and clean the trial balance

The first prompt is focused on data quality. The goal is not yet to build reports. The goal is simply to create a clean version of the trial balance that is fit for downstream analysis.

A good prompt here asks Claude to:

Review the full sheet for data quality issues
Look for inconsistent account names
Identify formatting errors
Fix numbers stored as text
Handle merged cells or structural issues
Remove or separate subtotal rows
Consolidate duplicate account lines where appropriate
Create a new clean output sheet rather than overwrite the original

One important lesson from this workflow is that specific prompts produce better outputs. A generic instruction like “clean this trial balance” may still work, but a more detailed prompt gives you better control over what gets fixed and how.

That first step took about four to five minutes in the demo. The result was a second sheet containing a much cleaner version of the data, ready for classification and reporting.

There was one small issue left in the example: the currency symbol defaulted incorrectly because of the local Excel license settings. That was easy to fix manually. And that is a good reminder that even when the output looks strong, a quick review still matters.

Why breaking the job into steps works better than one giant prompt

Could you ask AI to do everything in one shot?

Yes, absolutely. You could ask it to clean the trial balance and immediately build all three statements from that same prompt.

But there is a trade-off.

When you separate the workflow into stages, you keep more control over:

The cleanliness of the source data
The account mapping logic
The format of the final statements
The review and validation process

That extra structure matters in finance. A single all-in-one prompt may save a step, but it also makes it harder to see where something went wrong.

Prompt 2: Map the chart of Accounts to Reporting lines

Once the trial balance is clean, the next step is to create a mapping table.

This table links each account to the financial statement line item it should roll up into. For example, accounts need to be classified under the right income statement, balance sheet, or cash flow categories based on your reporting logic.

This step is extremely useful because it acts like a bridge between the raw accounting detail and the reporting format you actually want.

The prompt here tells Claude to create a financial statement mapping table that assigns each account to the correct reporting bucket. You can keep this fairly open-ended, but the better approach is to provide some guidance around how your rollups work.

That way, the mapping reflects your accounting structure instead of a generic template.

This second step took another five minutes or so. The output was a separate mapping table that could then be reused to build the statements.

This is also the step that gives you more control than going directly from trial balance to final statements. If you skip the mapping layer, AI can still generate the statements, but you are much more dependent on how it interprets your account groupings.

Prompt 3: Create the Income Statement and Balance sheet

With the clean trial balance and mapping table in place, the next prompt is where things start to feel powerful.

Now the AI has enough structure to prepare formal outputs.

In the example, the prompt asked Claude to build an income statement and a balance sheet using:

The prior year trial balance
The cleaned current year trial balance
The mapping table

The prompt also specified the desired statement structure. That matters because if you leave the format open, the result may still be correct but not in the layout you prefer.

By giving a defined structure, you can ask for items like:

Current year and prior year columns
Absolute variance
Percentage variance
Specific section groupings and subtotal placement

The result was a formatted income statement and balance sheet with year-over-year comparison and validation checks.

That step took around five to seven minutes.

Then the Cash Flow statement

After that, the cash flow statement was created with a simpler prompt.

Unlike the income statement and balance sheet, the cash flow prompt did not force a very specific layout. It simply instructed Claude to build the cash flow using the income statement, balance sheet, and opening trial balance.

Even with a simpler instruction, the output tied back correctly to the ending cash balance on the balance sheet. That tie-out gave an added layer of comfort that the statements were internally consistent.

Still, a tie-out is not the same thing as full correctness. It only tells you that the pieces are consistent with each other, not that every classification is perfect.

How much time this can save?

For the full workflow, the AI-assisted process took about 15 to 20 minutes end to end, with each prompt taking around five to seven minutes.

Without AI, the same job would typically take two to four hours, depending on:

How messy the original file is
How familiar you are with the account structure
How much formatting is required
Whether you are building the statements from scratch

That does not mean the task suddenly becomes zero-effort. It means the mechanical, repetitive part shrinks dramatically.

A more realistic way to think about it is this:

AI handles the heavy lifting in 15 to 20 minutes
You spend another 10 to 15 minutes reviewing, validating, and adjusting

That is still a huge improvement over doing the whole thing manually.

The most important rule: do not blindly trust the output

This is the part finance professionals need to take seriously.

AI can act like a very capable assistant. It cannot take accountability for your numbers.

Even when the output looks correct, you still need to review:

Account classifications
Statement structure
Balance sheet tie-outs
Cash flow logic
Signs, subtotals, and variance calculations
Anything unusual or business-specific

Your accounting and finance judgment is still the final control layer.

In the example, the statements matched the expected output, which was a strong result. But the right mindset is still: use AI for speed, not for blind reliance.

Choosing the right Claude model inside Excel

The Excel add-in lets you choose between different Claude models, and it helps to use the right one for the job.

Opus is better for deeper analysis and heavier tasks
Sonnet sits somewhere in the middle
Haiku is more suitable for lighter, faster back-and-forth interactions

For a workflow like messy trial balance cleanup, mapping, and financial statement creation, the stronger model makes sense because there is more structure, more logic, and more room for subtle mistakes.

If you are just asking quick questions or doing light support tasks, a lighter model may be enough.

Useful add-in features worth knowing

Beyond the prompting itself, a few features in the Excel add-in are worth paying attention to.

1. Edit Control mode

You can choose whether Claude should:

Accept all edits automatically, or
Ask before every edit

If you are experimenting on sample data, automatic acceptance is fine.

If you are working on a client file or real company data, asking before every edit gives you more control and is usually the safer option.

2. Attachments

You can attach other files such as PDFs, additional Excel workbooks, photos, and documents for Claude to reference inside the workflow. That opens up a lot of possibilities for reconciliation, policy interpretation, and supporting schedule analysis.

3. Voice Input

If you prefer speaking your prompt instead of typing it, voice input is available as well.

4. Saved chat history

The add-in can retain past chats, which is useful if you close a workbook and want to recover the prior prompt trail or reuse an earlier workflow.

5. Appearance and settings

You can customize the ribbon appearance and access settings, including options related to how the add-in behaves and how chats are stored.

When to use specific prompts and when to keep it generic

There is no single right way to prompt.

If you care about exact output format, detailed classifications, and consistent structure, then specific prompts are the better route.

If you just need a quick draft or rough first pass, generic prompts can work too.

For example, you could use one broad instruction like:

Clean this trial balance
Prepare an income statement, balance sheet, and cash flow statement

That will often produce something usable.

But if you want tighter control over how accounts are grouped or how each statement is laid out, splitting the work into cleaner steps is the better finance workflow.

That is the workflow. The mechanical part shrinks from hours to minutes, and your judgement stays exactly where it should be: on top.

If you would rather watch me do it step by step, the full session recording is here. Same trial balance, same prompts, start to finish. [Watch the recording]

A quick caution on confidential data

This part is non-negotiable.

Do not upload confidential company data into AI tools unless your organization has approved it and the right enterprise controls are in place.

If you are working with sensitive financial information, make sure:

IT has approved the tool
Your company policy allows it
You understand the data handling implications
You use anonymized or non-confidential data when testing

Experiment safely first. Then scale responsibly.

FAQ’s

Q1 Can I use Claude inside Excel with a free Claude account?

No. At the time of this workflow, the Excel add-in requires a paid Claude subscription, with the basic Pro plan being the minimum level needed.

Q2 What version of Excel do I need?

You need Excel 2016 or newer. Excel 365 works well for this setup.

Q3 Can AI build all three financial statements from a trial balance in one prompt?

Yes, it can. But separating the process into cleaning, mapping, and reporting gives you better control and makes review easier.

Q4 How much time can this save compared with doing it manually?

In the example, the AI-assisted workflow took roughly 15 to 20 minutes, while the manual version would likely take two to four hours. You should still reserve additional time for review.

Q5 Is the output reliable enough to use directly in work?

Not without review. AI can produce very strong first drafts and even well-structured statements, but the final validation still needs to come from your finance and accounting expertise.

Q6 What kinds of issues can AI fix in a messy trial balance?

Common examples include inconsistent account names, blanks, duplicate descriptions, formatting issues, numbers stored as text, and subtotal lines mixed into raw account detail.

Q7 Should I use accept all edits or ask before every edit?

For testing and experimentation, accepting all edits can be fine. For real business work, asking before every edit gives you more control and is usually the safer choice.

Q8 Is it safe to use confidential financial data in AI tools?

Only if your organization has approved it and the necessary enterprise safeguards are in place. If that approval does not exist, stick to non-confidential or anonymized data.

Join my next free session and learn to put AI to work inside your spreadsheets [register here].