Replace Excel Macros With AI-Written Scripts for Accountants

Yes — you can hand a fragile Excel macro or a tedious monthly clean-up to ChatGPT, Claude or Microsoft Copilot and have it write the formula, Office Script or Google Apps Script for you in plain English. You describe the job, paste a few rows of dummy data, and iterate until it works. The skill is not coding; it is describing the task clearly, testing on a copy, and documenting the result so it doesn't become the next mystery macro nobody can fix.

This guide is for Australian accountants, bookkeepers and practice managers who have a spreadsheet chore they repeat every month — tidying a bank export, assembling a client reporting pack, or flagging missing GST codes — and want to know whether AI can genuinely write the automation. It can. Here's how to do it without creating a new liability.

Start with one real monthly chore, not a platform

The mistake is deciding you'll "automate the spreadsheets". Pick one specific, repeating job you could describe to a new grad in two minutes. Good candidates:

• Cleaning a bank CSV export — stripping trailing spaces, fixing date formats, splitting a description column, removing duplicate header rows.
• Flagging transactions with a missing or unlikely GST code before they reach the ledger.
• Building a monthly reporting pack — copying figures into a template, applying the same formatting, dropping in this month's date.
• Reconciling two lists to find what's in one and not the other.

If the job already lives in Xero, check whether it belongs there first. Our guides to Xero AI automation for accountants and AI bank reconciliation in Xero cover work that shouldn't be in Excel at all. This article is for the genuine spreadsheet jobs that fall outside your ledger software.

The privacy rule before you type anything

Never paste a real client ledger, bank export or anything containing names, ABNs, account numbers or transaction details into a public chatbot. Under the Privacy Act, entering personal information into an AI system is a use of that information, and the OAIC's general guidance is clear that commercially available chatbots should not be given personal information. Treat this as general guidance, not legal advice.

The workaround is simple and it makes the AI's job easier: give it fake data with the same shape. Ten invented rows — "Acme Pty Ltd", "$110.00", "2 Sept 2025" — teach the AI everything it needs about your columns without exposing a single real client. For the fuller picture on client data, see whether AI is safe for client data under the Australian Privacy Act. If you're using Copilot inside your firm's Microsoft 365 tenant, or Gemini inside Google Workspace, you're on far safer ground than a consumer chat — but the dummy-data-first habit is worth keeping regardless.

Can ChatGPT write an Excel formula for me?

Yes, and this is the best place to start because a formula lives in the cell — there's no code file to maintain. Open ChatGPT (the free tier is fine for formulas) or Claude, and describe the columns and the result you want. Be specific about column letters and edge cases.

Try a prompt like this:

"I have an Excel sheet. Column A is a transaction date, column B is a description, column C is an amount including GST. Write me a formula for column D that returns the GST component, assuming amounts are GST-inclusive at 10%. If column C is blank, return blank, not an error."

You'll get a formula and an explanation. Paste it into a copy of your sheet and check it against figures you already know. When the first answer is wrong — say it returns 0 instead of blank — don't start over. Tell it what happened:

"When column C is empty it's showing 0. I want the cell to stay completely blank."

That back-and-forth is the whole skill. Describe the wrong behaviour in plain English and let it correct itself. Ask it to explain the formula in one sentence so you understand what you're keeping. If a job needs more than a couple of nested formulas, that's your signal to move up to a script.

Office Scripts vs VBA: which should I use?

If your fragile macro is written in VBA, the honest answer for most firms in 2025 is: don't write new VBA. Here's the comparison.

For a new automation on Microsoft 365, choose Office Scripts. AI tools write clean TypeScript, the scripts live inside your tenant rather than in emailed files, and they don't trip the macro-security warnings that IT increasingly blocks. Keep VBA only where it already works and you have no plan to change it — and even then, ask AI to document what the old macro does before someone leaves and takes the knowledge with them.

To create one, open Excel, go to the Automate tab, and choose New Script. That opens the code editor. Then ask your AI:

"Write an Office Script (TypeScript) for Excel. On the active worksheet, delete any row where column B (Description) is empty, trim leading and trailing spaces from column B, and format column A as dd/mm/yyyy. Add a short comment above each step explaining what it does."

Paste the result into the editor, save it, and run it on a copy. When it errors, paste the error message straight back to the AI — it will read the error and fix it. That is genuinely how professionals debug too.

Google Apps Script for bookkeeping tasks

If your practice runs on Google Workspace instead of Microsoft 365, the equivalent is Google Apps Script — and it's arguably friendlier for beginners because it can also reach into Gmail and Google Drive. In Google Sheets, open Extensions > Apps Script to get the editor.

Apps Script suits jobs like: emailing a monthly summary tab to yourself as a PDF, copying a template sheet and stamping it with this month's date, or checking a column of GST codes against an allowed list and highlighting anything unexpected. A starting prompt:

"Write a Google Apps Script for this Sheet. Look at column E (GST code). Valid codes are GST, FRE, and N-T. Highlight in red any cell in column E that isn't one of those three. Add comments explaining each part."

Same loop as before: run it on a copy full of dummy data, describe anything wrong, paste back any error. Apps Script asks you to authorise it the first time — read what it's asking permission to access, and only grant it for a sheet of test data while you're building.

Not sure this is for you? Our plain-English explainer on what vibe coding actually is for business owners is the gentler starting point.

How do I test an AI-generated spreadsheet script safely?

This is where fragile macros are born — people run something once, it looks right, and it becomes load-bearing. Don't do that. Follow a fixed test routine every time:

1. Work on a copy, always. Duplicate the file. Never let a first run touch a real workbook.
2. Use dummy data with known answers. Build ten rows where you already know what the output should be, including the awkward cases — a blank cell, a negative amount, a weird date format.
3. Run it and check every edge case, not just the middle. Scripts usually handle the obvious rows and fall over on the empty one or the header row that shouldn't be deleted.
4. Reconcile totals. If it moved or summed money, check the totals match the source. A cleaning script should never quietly change a figure.
5. Run it a second time on the same data. A good script does nothing harmful when run twice. A bad one deletes another header row or double-counts.

Only after it passes on dummy data does it earn the right to run on real files — and even then, keep an untouched backup of the source. Anything that must be right every time, or that feeds a lodgement or a client-facing report, gets human review before it goes out. AI writes the tool; it does not sign off the numbers.

Keep it documented so it doesn't become the next fragile macro

The reason old macros are dreaded is that nobody knows what they do or who made them. Fix that at the point of creation. When your script works, ask the AI:

"Write a plain-English summary of what this script does, what columns it expects, what it changes, and how to run it. Aim it at a colleague who has never seen it."

Paste that summary somewhere findable — a comment at the top of the script, plus a note in your process documentation. Record four things: what it does, who built it and when, which AI tool and version wrote it, and the sheet layout it depends on. If a column moves, the script may break silently, so the layout note matters.

Two governance points for the firm. First, these tools live somewhere — an Office Script sits in your Microsoft 365 tenant, an Apps Script sits in the Google Sheet — so make sure it's on a firm account, not a personal one, or it walks out the door when the staff member does. Second, put this under your AI policy so it's not shadow IT. And know when to stop: if the tool starts handling logins, payments, or many interconnected files, hand it to a developer. Vibe coding is for the afternoon-sized job.

Copilot, ChatGPT or Claude — does the tool matter?

For writing formulas and short scripts, all three are capable. Claude and ChatGPT are strong at generating and debugging TypeScript and Apps Script from a plain-English brief. Microsoft Copilot has the advantage of sitting inside your tenant, which matters for anything touching real data — we weigh this up in Microsoft Copilot vs ChatGPT for accounting firms. Whichever you use, the workflow is identical: dummy data, describe the job, iterate on what's wrong, test on a copy, document.

If you'd rather see the whole picture of what's worth automating before you build anything, our free guide, 10 AI Workflows to Save 10+ Hours a Week, lays out the jobs that give the best return — so your first AI-written script replaces a chore that was actually costing you time.

Common questions

Can ChatGPT really write an Excel formula for me?

Yes. Describe your columns by letter, say what result you want, and note edge cases like blank cells. ChatGPT or Claude returns a working formula plus an explanation. Paste it into a copy of your sheet and check it against figures you already know. If it behaves wrongly, describe the problem in plain English and it will correct the formula.

Office Scripts vs VBA — which should I use for a new automation?

Choose Office Scripts for anything new on Microsoft 365. They run on web and desktop, store inside your tenant rather than in emailed macro files, avoid the security warnings IT now blocks, and can run on a schedule via Power Automate. Keep VBA only where it already works and you have no plan to change it.

Is it safe to use Google Apps Script for bookkeeping tasks?

It's a good fit for jobs like emailing a monthly summary, stamping a template with the current date, or highlighting unexpected GST codes. Build and test with dummy data only, and read the authorisation prompt carefully the first time you run it. Store the script on a firm Google account, not a personal one, and document what it does.

How do I test an AI-generated spreadsheet script without breaking anything?

Always work on a copy. Use ten rows of dummy data where you already know the correct answers, including awkward cases like blanks and negatives. Run the script, check every edge case, reconcile any totals against the source, then run it a second time to confirm it does no harm when repeated. Only then use it on real files.

Will an AI-written script become another fragile macro nobody understands?

Only if you skip documentation. When it works, ask the AI to write a plain-English summary of what it does, the columns it expects and how to run it. Record who built it, when, and which AI tool wrote it. Store it on a firm account under your AI policy, and hand anything complex to a developer.

Want these ideas working in your firm? We build controlled AI workflows for Australian professional services firms — starting with a free automation audit.