How to Use AI with Excel: A Practical Guide for Finance Professionals

Writing structured commentary

Paste your variance analysis numbers and ask AI to draft the management commentary. Give it context — budget period, any known drivers of the variances, the audience. Use the draft as a starting point, edit it to reflect your actual knowledge of what happened, and you’ve cut the time from an hour to fifteen minutes.

The output will be generic without your input. That’s expected. The value is in having a structured first draft you can refine rather than starting from a blank page.

What AI can’t do in Excel

A few things worth being clear on. AI can’t connect directly to your spreadsheet in real time (unless you’re using Microsoft 365 Copilot, which has that capability). You’re copying and pasting content to an external tool. It can’t run or test the code it generates — that’s your job. And it doesn’t know your specific model structure, your data layout, or the quirks of your particular version of Excel unless you tell it.

The more context you give, the better the output. Describe your data structure, name the columns, explain the business logic. The AI doesn’t know you’re working with a twelve-period rolling forecast with a hierarchical cost centre structure unless you say so.

Generating test data

Building a new model or template and need realistic test data to check it works? Ask AI to generate it. “Give me 50 rows of realistic project data for a construction company — project code, project name, budget holder, start date, end date, original budget, and forecast cost. Make the numbers plausible for mid-size infrastructure projects.” You’ll have test data in seconds that would take twenty minutes to create manually.

Writing structured commentary

Paste your variance analysis numbers and ask AI to draft the management commentary. Give it context — budget period, any known drivers of the variances, the audience. Use the draft as a starting point, edit it to reflect your actual knowledge of what happened, and you’ve cut the time from an hour to fifteen minutes.

The output will be generic without your input. That’s expected. The value is in having a structured first draft you can refine rather than starting from a blank page.

What AI can’t do in Excel

A few things worth being clear on. AI can’t connect directly to your spreadsheet in real time (unless you’re using Microsoft 365 Copilot, which has that capability). You’re copying and pasting content to an external tool. It can’t run or test the code it generates — that’s your job. And it doesn’t know your specific model structure, your data layout, or the quirks of your particular version of Excel unless you tell it.

The more context you give, the better the output. Describe your data structure, name the columns, explain the business logic. The AI doesn’t know you’re working with a twelve-period rolling forecast with a hierarchical cost centre structure unless you say so.

Analysing and explaining data

Copy a range of data and paste it into Claude or ChatGPT. Ask it to summarise the trends, spot anomalies, or explain what the numbers show. For commentary drafts or sanity-checking your own analysis, this is genuinely useful.

Be careful with sensitive data — paste only what’s appropriate to share with an external AI tool, and check your organisation’s data policies before doing this with confidential figures. For internal analysis work, keep it anonymised or use indicative numbers.

Generating test data

Building a new model or template and need realistic test data to check it works? Ask AI to generate it. “Give me 50 rows of realistic project data for a construction company — project code, project name, budget holder, start date, end date, original budget, and forecast cost. Make the numbers plausible for mid-size infrastructure projects.” You’ll have test data in seconds that would take twenty minutes to create manually.

Writing structured commentary

Paste your variance analysis numbers and ask AI to draft the management commentary. Give it context — budget period, any known drivers of the variances, the audience. Use the draft as a starting point, edit it to reflect your actual knowledge of what happened, and you’ve cut the time from an hour to fifteen minutes.

The output will be generic without your input. That’s expected. The value is in having a structured first draft you can refine rather than starting from a blank page.

What AI can’t do in Excel

A few things worth being clear on. AI can’t connect directly to your spreadsheet in real time (unless you’re using Microsoft 365 Copilot, which has that capability). You’re copying and pasting content to an external tool. It can’t run or test the code it generates — that’s your job. And it doesn’t know your specific model structure, your data layout, or the quirks of your particular version of Excel unless you tell it.

The more context you give, the better the output. Describe your data structure, name the columns, explain the business logic. The AI doesn’t know you’re working with a twelve-period rolling forecast with a hierarchical cost centre structure unless you say so.

Excel isn’t going anywhere. But the way you use it is about to change significantly — and for the better, if you know how to combine it with AI.

Let’s be clear about something first. AI won’t replace Excel. Finance runs on spreadsheets, and that’s not changing in the next decade. What AI does is remove the friction around Excel — the bits that eat your time without requiring your judgment.

Here’s how to put the two together properly.

Writing formulas faster

This is the most immediate win. You know what you want your formula to do — you just can’t always remember the exact syntax, or you’re building something complicated enough that getting it right first time takes effort.

Instead of digging through help documentation or trial and error, just describe it in plain English. Tell Claude or ChatGPT: “I need a formula that looks up a project code in column A, finds it in a reference table on another sheet called Projects, and returns the budget holder name from column C. If it can’t find a match it should return ‘Unknown’.” You’ll get a working XLOOKUP or INDEX/MATCH in seconds, with an explanation of how it works.

The same works for complex array formulas, LAMBDA functions, dynamic ranges, anything. Describe the logic, get the formula. Test it, adjust if needed.

Fixing broken formulas

Paste the broken formula, paste the error message, describe what you were trying to do. AI is remarkably good at diagnosing formula errors. It’ll spot mismatched brackets, wrong argument order, relative vs absolute reference issues, and tell you exactly what to change.

This used to mean either working it out yourself (slow) or asking a colleague (awkward if it’s something you feel you should know). Now it’s a 10-second fix with no judgment attached.

Building VBA and Power Query scripts

This is where the time savings get significant. If you’ve ever wanted to automate something in Excel but don’t know VBA well enough to write it from scratch, AI closes that gap entirely.

Describe what you want the macro to do in plain English. “Loop through all sheets in this workbook, copy the data from the range A2:H500 on each one to a summary sheet, add the sheet name in column I, and skip any sheet named ‘Summary’ or ‘Template’.” You’ll get working VBA code you can paste straight in. Test it on a copy of your file first — always — but it’ll usually work or need only minor tweaks.

Power Query M code works the same way. Most finance professionals either don’t know M syntax at all or find it cryptic. AI makes it accessible. You can now build data transformation steps that would previously have required a developer or significant self-study.

Analysing and explaining data

Copy a range of data and paste it into Claude or ChatGPT. Ask it to summarise the trends, spot anomalies, or explain what the numbers show. For commentary drafts or sanity-checking your own analysis, this is genuinely useful.

Be careful with sensitive data — paste only what’s appropriate to share with an external AI tool, and check your organisation’s data policies before doing this with confidential figures. For internal analysis work, keep it anonymised or use indicative numbers.

Generating test data

Building a new model or template and need realistic test data to check it works? Ask AI to generate it. “Give me 50 rows of realistic project data for a construction company — project code, project name, budget holder, start date, end date, original budget, and forecast cost. Make the numbers plausible for mid-size infrastructure projects.” You’ll have test data in seconds that would take twenty minutes to create manually.

Writing structured commentary

Paste your variance analysis numbers and ask AI to draft the management commentary. Give it context — budget period, any known drivers of the variances, the audience. Use the draft as a starting point, edit it to reflect your actual knowledge of what happened, and you’ve cut the time from an hour to fifteen minutes.

The output will be generic without your input. That’s expected. The value is in having a structured first draft you can refine rather than starting from a blank page.

What AI can’t do in Excel

A few things worth being clear on. AI can’t connect directly to your spreadsheet in real time (unless you’re using Microsoft 365 Copilot, which has that capability). You’re copying and pasting content to an external tool. It can’t run or test the code it generates — that’s your job. And it doesn’t know your specific model structure, your data layout, or the quirks of your particular version of Excel unless you tell it.

The more context you give, the better the output. Describe your data structure, name the columns, explain the business logic. The AI doesn’t know you’re working with a twelve-period rolling forecast with a hierarchical cost centre structure unless you say so.