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

Last checked

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.

Best pick

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

  1. Write the chore step by step: which sheet, which columns, what should happen, and an example of before and after.
  2. Say whether you use Excel desktop (VBA), Excel on the web (Office Scripts) or Google Sheets (Apps Script).
  3. Ask for the code with comments and a list of what it changes or deletes.
  4. Make a copy of the workbook and run the macro on the copy first.
  5. Check the result against your before/after example; ask the AI to fix any error message you get.
  6. 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

Macro
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.
Apps Script
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.
Fix an error
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 a macro
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

Which tool to pick, and why

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

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

Related tasks

Report an error on this page