Home › Data analysis › Write an Excel macro or Apps Script: how to do it with AI
Write an Excel macro or Apps Script: how to do it with AI
From official vendor pages; selected tasks also include dated logged-out free-plan trials. Report an error.
Intermediate Risk: Medium 📊 Data analysis
Describe a repetitive spreadsheet chore and get an Excel VBA macro or a Google Apps Script that does it, with each step explained. AI writes this kind of code well, but a macro can change or delete many cells at once, so you test it on a copy first.
Who it’s for: Office workers, analysts, accountants and admins who repeat the same spreadsheet steps every week.
Why medium risk: Macros edit data in bulk and can't always be undone with Ctrl+Z; macro files from others can also carry malware.
ChatGPT has the highest score on our published rubric (94/100) of the 4 tools listed for this task. The pricing page lists Excel and Google Sheets extensions (limited on Free and Go, included on Plus) and data analysis (limited on Free) (chatgpt.com/pricing, checked Oct 23, 2026).
Step by step
- Write the chore step by step: which sheet, which columns, what should happen, and an example of before and after.
- Say whether you use Excel desktop (VBA), Excel on the web (Office Scripts) or Google Sheets (Apps Script).
- Ask for the code with comments and a list of what it changes or deletes.
- Make a copy of the workbook and run the macro on the copy first.
- Check the result against your before/after example; ask the AI to fix any error message you get.
- Save the file as .xlsm (Excel) and only enable macros from sources you trust.
AI prompts to write an Excel macro
Replace the [brackets] with your details. See all prompt examples
Write an Excel VBA macro for this task. Sheet: [name]. Columns: [A: Date, B: Client, ...]. Steps: [describe]. Requirements: add comments to every block, don't delete anything without asking with a message box, and stop with a clear message if the sheet or column isn't found. Then list every line that changes or deletes data.
Write a Google Apps Script for Google Sheets that [task]. Explain how to add it in Extensions > Apps Script, what permissions it will ask for, and how to run it from a custom menu.
My macro stops with this error: [error text] on this line: [line]. Here is the full macro: [paste]. Explain the cause and give a corrected version.
Explain what this macro does, step by step, in plain English, and flag anything risky (deleting, overwriting, sending email, connecting to the internet): [paste]
Illustrative example
You paste
Copy every row where column D says 'Overdue' from 'Invoices' to a sheet called 'Overdue', clearing the old 'Overdue' sheet first.
A good answer looks like
A VBA macro with comments that checks both sheets exist, asks 'Clear the Overdue sheet?' before clearing it, loops over used rows, copies matching rows and shows 'Copied 14 rows'.
Illustrative example written for this guide, not output captured from a specific tool.
Safe practice
- Test on a copy; keep a backup before every run.
- Ask the AI to list any line that deletes, overwrites, sends email or connects to the internet, and read those lines yourself.
Which tool to pick, and why
- ChatGPT (incl. Work & Codex): The pricing page lists Excel and Google Sheets extensions (limited on Free and Go, included on Plus) and data analysis (limited on Free) (chatgpt.com/pricing, checked Oct 23, 2026). Free plan can do this Visit official website ↗
- Claude (incl. Claude Code): The free plan can create files and documents (Claude Docs) and keeps up to 5 projects; Pro is US$20/month, or US$17/month billed annually (claude.com/pricing, checked Oct 23, 2026). Free also runs code and creates files, useful for explaining or testing logic. Visit official website ↗
- Microsoft Copilot: Microsoft 365 Personal includes Copilot in the Word, Excel, PowerPoint and Outlook desktop apps (microsoft.com, checked Oct 23, 2026). Microsoft notes that Copilot in Excel needs AutoSave on, so the file must be saved to OneDrive. Visit official website ↗
- Google Gemini: Gemini in Gmail, Docs and more is listed from Google AI Plus up; the free Gemini app can still write Apps Script you paste in (gemini.google/subscriptions, checked Oct 23, 2026). Visit official website ↗
Reasons come from the capabilities, free-plan details and prices we've checked on official pages (separate from any dated trial above). Sources: chatgpt.com/pricing · claude.com/pricing · www.microsoft.com/en-us/microsoft-copilot/for-individuals · gemini.google/subscriptions/
Common mistakes
- Running a new macro on your only copy of the file.
- Enabling macros in files from unknown senders.
- Pasting real client or payroll data into a chat to 'show' the layout; use a few fake rows.
Privacy tip
Describe your sheet with column names and a few made-up rows instead of pasting real customer, payroll or financial data.
ChatGPT (incl. Work & Codex) setting · Claude (incl. Claude Code) setting · Microsoft Copilot setting · Google Gemini setting · Sources: help.openai.com/en/articles/7730893-data-controls-in-chatgpt · privacy.claude.com/en/articles/12109829-how-do-i-change-my-model-improvement-privacy-settings · support.microsoft.com/en-us/privacy/microsoft-copilot/privacy-controls · support.google.com/gemini/answer/13594961