Excel Automation Made Easy with Python and ChatGPT (don’t miss out…)

Introduction

I’ve been experimenting with ChatGPT over the past few weeks, and in this post, I’ll demonstrate how to utilize ChatGPT and Python to automate Excel and make your life easier. I will cover some practical Excel automation ideas and compare the solutions generated by ChatGPT to the ones I developed previously.

1. Example: Find & replace values

Let’s start with the first example. I have several Excel files in my input folder, and I want to replace all instances of “Small Business” with “Small Market” and “Midmarket” with “Midsize Market” in all worksheets of the files. I asked ChatGPT to iterate through all Excel files in the input directory and perform these replacements, saving the modified workbook in the output folder.

To my surprise, ChatGPT was able to write the entire script for me, complete with comments explaining each step. After running the code, I checked the output folder and confirmed that “Small Business” was replaced with “Small Market” and “Midmarket” with “Midsize Market” in all worksheets. This was impressive and showcased the effectiveness of ChatGPT.

2. Example: Store values in a new wb

In the next example, I wanted ChatGPT to write a script to save each worksheet in a spreadsheet as a new workbook, retaining the original format. I instructed it to place the new workbooks in a folder called “Output,” creating it if it didn’t exist.

The code ran without errors, and I found the output directory containing the new Excel files. However, I noticed that the provided script only copied the values and did not maintain the original formatting. When I asked ChatGPT for a solution to retain formatting, it modified the code, but I encountered error messages upon execution. After further adjustments, I realized it was challenging to preserve formatting using the openpyxl package. I had previously used xlwings for this purpose, which I recommended for future tasks.

3. Example: Filter Excel data

Next, I had a workbook with financial information for different countries. I asked ChatGPT to filter the “Financial_Data.xlsx” workbook for the year 2021 by unique values in column B (“Country”), creating a new Excel file for each country and saving it in an attachments folder.

ChatGPT provided a solution using Pandas, which included helpful explanations. After running the code, I confirmed that the new workbooks were created correctly, containing only the financial data for each country in 2021. This example highlighted the importance of specifying the desired Python packages in the prompt.

4. Example: Split ws into wb

I then explored saving each worksheet as a separate workbook again, using xlwings for my previous solution. I asked ChatGPT to add comments to my existing code, resulting in a version with explanations for each line. While it might have been excessive to comment on every line, it demonstrated the flexibility of ChatGPT in assisting with code documentation.

Additionally, I asked ChatGPT to convert my code into a function, which it did successfully, complete with type hints and a docstring explaining the arguments. This capability to enhance and restructure code showcases the versatility of ChatGPT.

5. Example: Automate PowerPoint

In my final example, I asked ChatGPT to create a Python script that generates a PowerPoint presentation with three slides, each containing titles and bullet points, as well as images obtained from an API.

The code provided by ChatGPT included functions for creating the presentation, along with a summary of its functionality. Although the generated presentation didn’t fully match my expectations in layout, it served as a solid starting point.

Conclusion

If you have office tasks that you want to automate, ChatGPT is definitely worth trying. The key is crafting concise and clear prompts, especially for complex tasks. Breaking tasks into smaller subtasks can help in achieving more manageable automation. Keep in mind that the code generated may require adjustments, but overall, ChatGPT can significantly simplify your office life.