Discover how to create Excel macros effortlessly using ChatGPT. Streamline your workflow and boost productivity with this powerful combination.The arrival of artificial intelligence has been a breath of fresh air for many office tasks that could previously take us a long time. The other day we saw how we can create a PowerPoint presentation using AI , and today we are going to follow that same dynamic to create Excel macros .
All this without the need to write formulas or be an expert in Microsoft Excel. In our case we will use ChatGPT, but we can use any other AI that is capable of programming, such as Windows Copilot, Bing Chat, Google Bard or other similar text generative AIs .
How to create an Excel macro using ChatGPT
Creating Excel macros with the help of ChatGPT can be a streamlined process. Here’s a guide in tabular form to assist you:
| Step | Description |
|---|---|
| 1. Identify the Task | Clearly define what task you want the macro to perform in Excel. This could be anything from formatting cells, sorting data, or automating complex calculations. |
| 2. Open the Visual Basic Editor (VBE) | In Excel, press Alt + F11 to open the Visual Basic for Applications (VBA) editor. |
| 3. Insert a New Module | In the VBE, right-click on any of the existing projects in the Project Explorer. Choose Insert > Module to create a space where you can write your macro. |
| 4. Define the Macro | Start by typing Sub MacroName() and press Enter. Replace MacroName with a name of your choice. This begins the macro definition. |
| 5. Write the Macro Code | Inside the macro, write the VBA code that performs your task. If you’re not familiar with VBA syntax, you can describe the task to ChatGPT and ask for code suggestions. |
| 6. End the Macro | Close your macro with End Sub. This signifies the end of your macro code. |
| 7. Test the Macro | Run your macro by pressing F5 while in the editor, or assign it to a button in the Excel workbook for easier access. |
| 8. Debug if Necessary | If the macro doesn’t work as expected, return to the VBE and check the code for errors. You can ask ChatGPT for debugging help. |
| 9. Save Your Workbook | Save your Excel file as a macro-enabled workbook (.xlsm) to ensure your macro is saved with your data. |
Remember, writing macros involves programming in VBA, which is a powerful tool but can also be complex. ChatGPT can assist with code snippets, debugging, and explanations, but some knowledge of VBA is helpful for more advanced tasks.
For this example we are going to create a very simple macro with ChatGPT. The idea is to understand the concept and then be able to develop it with more complex data tables.
Suppose we have an Excel sheet in which we have a list of superheroes, each one with its corresponding name, power level and group to which it belongs.
Next, we will create a macro that tells us which is the most powerful superhero.
- Open the browser and enter the ChatGPT website.
- Ask the AI to create a macro that meets your needs. For this you can use the following template:
- Write an Excel VBA macro for [target]. The data starts on line [X] to line [Y] (counting headers) in the following order:
- [Paste your headings]
- The name of the sheet is [Name].
- Be sure to specify the sheet where the table is located. If the data is on the second sheet, clearly indicate that it is “Sheet2”.
- Once ChatGPT has generated the code, click on “ Copy code” to copy it to the clipboard.
Now that we have the macro code, we will go to the Microsoft Excel program.
- In Excel, press ALT+F11to open the Visual Basic editor.
- In the top menu, click on “Insert -> Module”.
- Paste the code you generated with ChatGPT and press the “ CONTROL + S” keys to save the macro. You will see a warning message: accept it by clicking “Yes”.
- Close the Visual Basic window and return to the Excel sheet where you have the table with the data.
- Press the ALT+F8keys , select the macro you just compiled in Visual Basic and click “ Run ”.
The application that we created with the macro will automatically run. In our case, we have asked the macro to tell us which is the most powerful superhero in the table, that is exactly what it has done, showing us the following message on the screen:
How to create charts in Excel with ChatGPT
We can also use macros to perform other types of tasks in Excel, such as creating graphs from the data in a table . The process to follow is the same: we will create a macro with the chart data, we will compile it in Visual Basic and then we will execute it in Excel.
- Ask ChatGPT to create a macro to generate a chart in Excel. You can use the following prompt as a template:
- Write an Excel VBA macro to create a chart: the X axis displays [HEADER A] and the Y axis displays [HEADER B]. The data starts on line [X] to line [Y] (counting headers) in the following order:
- [Paste your headings]
- The name of the sheet is [Name].
Following the same example as before, we’ve asked ChatGPT to create a graph breaking down the power levels of all the superheroes that appear in our data table.
Once we have the code, we go to Excel and press the ALT+F11 keys. Within Visual Basic we click on “ Insert -> Module ” and paste the code that we just generated with ChatGPT. We press the CONTROL+S keys at the same time to save the macro and close Visual Basic.
Finally, we go to the Excel sheet where the data is located and press the ALT+F8 keys , select the macro to create the graph and click “Execute”. Excel will automatically create the graph with the data that we have passed to it.
