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 TutorialWatch on YouTube & Subscribe to our Channel

Download the Example File Automate Excel with ChatGPT.zipDownloadODA Prompting Framework – Excel Campus.pdfDownload

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 ChatGPTIn 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 Code1. Providing the Prompt – The initial request asked ChatGPT to generate a VBA macro to export selected sheets as CSV files.

  1. 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).

  2. Testing the Macro – Before running the code, I recommend two precautions:

    1. Reading through the code/comments to understand the logic.

  3. Saving a backup copy of the file in case the macro causes unexpected issues.

Troubleshooting ErrorsWhen 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 FrameworkTo 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.

  1. The macro should prompt the user on where to save the CSV files.
  2. Loopthrough the selected sheets.
  3. Select an individual sheet.
  4. Copy that sheet to a new workbook.
  5. 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.

ConclusionAI 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!

Link to post: Automating Excel with ChatGPT: Can AI Really Help?

See All 107 Episodes of "Excel Campus"