Bottom Line: ChatGPT can be a valuable assistant for automating Excel tasks, but understanding Excel's built-in tools like VBA is essential to getting the best results.
Skill Level: Intermediate
Watch the Tutorial
Watch on YouTube & Subscribe to our Channel
Download the Example File
AI in Excel: Hype or Help?
There’s a lot of debate around AI tools like ChatGPT and their ability to assist with Excel tasks. Some users love how they streamline workflows, while others find them unreliable or error-prone. This post explores how ChatGPT can help automate Excel, its limitations, and a three-part prompting framework to improve results.
Creating a VBA Macro with ChatGPT
In the video example, our goal is to export multiple Excel sheets as CSV files automatically. Instead of writing a VBA macro from scratch, we’ll use ChatGPT to generate the code.
Generating the VBA Code
- Providing the Prompt – The initial request asked ChatGPT to generate a VBA macro to export selected sheets as CSV files.
- Copying the Code – We copied the AI-generated VBA code into the Visual Basic Editor (Alt + F11) and placed it in a new module (Alt + IM).
- Testing the Macro – Before running the code, I recommend two precautions:
- Reading through the code/comments to understand the logic.
- Saving a backup copy of the file in case the macro causes unexpected issues.
Troubleshooting Errors
When running the macro, an error appeared:
Runtime Error 1004 – “You cannot copy or move a group of sheets that contain a table.”
By debugging the macro, it became clear that Excel tables were preventing the sheets from being copied. After multiple attempts to get ChatGPT to resolve the issue, the final fix required a manual code adjustment: selecting each sheet before copying.
So this is a good example of how AI can often save a ton of work, but human knowledge and experience is still needed to cross the finish line.
The ODA Prompting Framework
To improve ChatGPT’s performance with Excel automation, use the ODA Framework:
- O – Overview: Provide a brief explanation of the task.
- D – Details: Give step-by-step instructions to minimize assumptions.
- A – Ask Questions: Request clarification before ChatGPT generates a response.
Example of a Better Prompt
“I would like to get your help with writing a VBA macro for Excel.
The goal of the macro is to export or save each selected sheet to a CSV file. Each sheet contains an Excel Table, so the macro will need to run the following steps to work around Excel limitations when selecting multiple sheets with Tables.
- The macro should prompt the user on where to save the CSV files.
- Loop through the selected sheets.
- Select an individual sheet.
- Copy that sheet to a new workbook.
- Save the file as a CSV file.
“Do you have any questions on this macro?”
This new prompt follows my ODA framework.
By following this framework, ChatGPT generated more accurate responses and even included filename validation—something not initially considered.
Final Thoughts: Is ChatGPT Useful for Excel Automation?
ChatGPT can be a powerful assistant for automating Excel tasks, but it's important to understand both its strengths and its limitations.
Where ChatGPT Excels:
- Generating Starter Code: It can quickly produce VBA macros and scripts, saving time on repetitive coding tasks.
- Explaining Concepts: AI-generated responses can help clarify coding principles, making it easier to learn new techniques.
- Speeding Up Problem-Solving: Instead of manually searching for answers online, you can get quick solutions and troubleshooting tips.
Potential Challenges:
- Debugging Required: AI-generated code isn't always flawless, so having some VBA knowledge is helpful for troubleshooting.
- Manual Review Needed: For critical tasks, reviewing and testing the code is essential to ensure it functions correctly.
- Handling Complex Business Logic: AI may not fully grasp specific workflows or nuanced business rules, requiring human oversight.
Microsoft calls its AI “Copilot,” and that’s exactly how it should be used—as an assistant, not a replacement for Excel expertise.
Need Help with VBA?
If this tutorial has you thinking that you could use some help upping your VBA game, we've got you covered. Register for my free Excel Blueprint Webinar to get started.
Conclusion
AI tools like ChatGPT can save time in Excel, but they still require a human touch to ensure accuracy. Have you tried using AI to automate Excel? Leave a comment below and share your experience!
Hi Jon:
Thanks very much for this article.
Very interesting & informative as always.
Looking forward to trying Copilot and other AI engines with Excel.
I have very limited experience with Copilot and agree that the User is truly the Pilot, hence, the User needs to think ahead at what the possibilities are of Copilot’s deliverables to ensure you get what you ask for, so to speak. I have tried to structure prompts differently (on purpose) and was surprised at the variations of the results. I’ve learned that by tweaking wording to remove extra words helps Copilot to “focus” more on the task.
Just curious whether you or others in the community have asked chatGPT or Copilot, other AI engines, to review VBA coding that you / others have created to see where AI could correct any issues (potential coding issues) or provide suggestions for coding best practices, suggestions for better VBA coding in general, different approach to solve a coding issue / approach, etc.?
Thanks again.
Not seeing where the ODA Prompting Framework as a pdf link. Seems like you can just save a webp image.
Hello Richard, we have added it now under “Download the Example File”. Thank you and have a great day! 🙂
Hey Jon,
Where do I find the prompt PDF you mentioned in the video?
Hello Daniel, we have added it now under “Download the Example File”. Thank you and have a great day! 🙂
Thanks for covering this issue. My guess (i.e, “guess”) is that Excel users expect too much from AI and/or don’t know VBA well enough to understand the “finer details” of writing it correctly.
Thanks too for Alt+IM. I’d never seen it — it’s a big help.
Fantastic documentation of the process to use AI to assist with Excel. Great explanation of the benefits and the challenges. There are not enough people on social media that are correctly defining AI use within Excel (or any other work application)
nice topic
thank you