You don’t need to know code — AI writes it for you ⓒ Unsplash
Learn to build Excel macros (VBA) with ChatGPT without writing a single line of code yourself.
Cleaning the same data every month. Reformatting the same way, every time. Merging the same files, over and over. This kind of repetitive work can be eliminated entirely with a macro (VBA) — one click, done. The reason most people never try is the assumption that “VBA means coding, and coding is hard.” That’s no longer true. Describe what you want to ChatGPT, and it writes the code for you. Here’s how.
1. What Exactly Is a Macro?
A macro “records” a repetitive task in Excel so you can replay it with a single button click.
Example: “Sort column A → apply formatting to column B → calculate the total” — record it once as a macro, and every future run is just one click.
→ VBA (Visual Basic for Applications) is the programming language behind these macros.
2. The Step-by-Step Process With ChatGPT
1️⃣ Describe the repetitive task in specific plain language
2️⃣ Copy the VBA code ChatGPT generates
3️⃣ Open Developer tab → Visual Basic in Excel
4️⃣ Paste the code and run it (F5)
5️⃣ If it’s not quite right, describe the result to ChatGPT and ask for a fix
3. Real Prompt Examples — Copy These Directly
“Write an Excel macro that finds duplicate values in column A and highlights them red, then sorts column B in ascending order. Include beginner-friendly instructions on how to run it.”
“Write a macro that combines all Excel files in the same folder into a single sheet. Only pull the first sheet of each file, and keep the header row only once.”
“Write a macro that adds borders to a selected range, makes the header row bold with a gray background, and applies thousands-separator formatting to number columns.”
Instead of “clean this up,” say exactly which column should do what: “remove duplicates based on column A, and reformat column B as a date.” Vague requests produce vague code.
4. How to Run a Macro (Step-by-Step Guide)
| Step | Menu Path |
|---|---|
| 1. Enable Developer Tab | File → Options → Customize Ribbon → check “Developer” |
| 2. Open VBA Editor | Developer tab → Visual Basic (or Alt+F11) |
| 3. Insert a Module | Insert → Module |
| 4. Paste the Code | Copy ChatGPT’s full code → paste into the module window |
| 5. Run | Press F5 or click the Run (▶) button |
5. Time Saved With Macro Automation
| Task | Manual Time | Macro Runtime |
|---|---|---|
| Remove duplicates from thousands of rows | 15–20 min | 2 sec |
| Merge multiple files into one | 10–15 min | 3 sec |
| Apply recurring monthly formatting | 10 min | 1 sec |
| Highlight cells matching a condition | 5–10 min | 1 sec |
📁 Save your file as an “Excel Macro-Enabled Workbook (.xlsm)” to keep the macro intact
🔘 Add frequently used macros to the Quick Access Toolbar for one-click execution
💾 Save macros in your Personal Macro Workbook to use them across every Excel file you open
6. Level Up Your Workflow While You’re At It
Comparing code against results while reviewing multiple data files is much easier with a second screen — one monitor for code, another for output. A dual-monitor setup pays for itself quickly if this is a regular part of your job.
Mastering IF, IFS, and nested functions to handle complex conditional logic.
Frequently Asked Questions
Q. I’m getting a “macros have been disabled” warning. What do I do?
Go to File → Options → Trust Center → Trust Center Settings → Macro Settings, and select “Disable all macros with notification.” This lets you choose to allow it when running trusted macros.
Q. The code ChatGPT gave me throws an error.
Copy the exact error message and paste it back to ChatGPT along with “I ran this code and got this error.” It will almost always fix it immediately — the error message is the debugging clue.
Q. My company has macros disabled entirely. Now what?
Some organizations restrict macros for security reasons. Ask your IT department for approval, or consider Power Query as an alternative automation tool that’s often allowed even when macros aren’t.