Hey guys,
I've been experimenting with ChatGPT over the past few weeks, and in this video, I'll
demonstrate how you can utilize ChatGPT and Python to automate Excel and make your life
easier. Specifically, I'll go over some practical
Excel automation ideas. For all examples in this video, I've also
created a tutorial in the past, so we'll also be comparing some of the solutions from ChatGPT
to the ones I came up with. Ok, so let's start with the first example. I have a bunch of 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. So, here is what I asked ChatGPT to do: "Using
Python, iterate through all Excel files in the input directory and replace instances
of "Small Business" with "Small Market" and "Midmarket" with "MidSize Market" in each
sheet of each file. Save the modified workbook in the output folder,
create the folder if it does not exist. ". To my surprise, ChatGPT was able to write
the entire script for me and even included comments explaining each step. I then copied and pasted the code into an
empty Python file and ran it. Now, if we look in the output folder, we can
see our modified spreadsheets. Let me open one up to show you the changes. As you can see, "Small Business" has been
replaced with "Small Market" and "Midmarket" with "Midsize Market" in all the worksheets. This is already pretty impressive, and I like
that ChatGPT included the comments to explain the code in more detail. In a past video, I created a solution to this
problem using a dictionary for the replacement pairs in case I wanted to add more values. Nevertheless, the output is the same, and
ChatGPT accomplished my task. However, in the next example, I ran into some
issues. I wanted ChatGPT to write a script to save
each worksheet in this spreadsheet as a new workbook, with only the values, and the workbook
should have the same name as the sheet while maintaining the same worksheet format. The new workbooks should be placed in a folder
called "Output". If that folder doesn't exist, it should be
created. So, I asked ChatGPT for a solution, and it
gave me a script, including the description. As before, I took the code, pasted it into
my text editor and executed it. The code ran without errors, and also, in
my folder, I now have my output directory with the different Excel files. Yet, when I checked the result, I noticed
that the provided script didn't keep the original format - it just copied the values. While it was a good start, it wasn't perfect. However, one of the great things about ChatGPT
is that you can ask follow-up questions. So, I asked how I could prevent the formatting
from being lost in the new workbook. ChatGPT modified then the code and added a
couple of lines to copy the formatting. I ran the code again. Yet, I got the following error message. I then asked ChatGPT to fix this error, and
it provided me with a modified version of the code, but also that version did not work. So, it was leading to nowhere. As I said, also for this use case, I have
created a dedicated video on my channel. In that video, I used the Python package xlwings
to keep the formatting because I knew it would be tough to do with openpyxl. So, if you already know which package you
want to use, you can tell ChatGPT to do so. This is what I did in the next example. In this Excel workbook, I have two worksheets. The data worksheet contains financial information
for different countries. The task I gave to Chatp GPT is as follows:
Using Python and Pandas, filter the "Financial_Data.xlsx" workbook for the year 2021 by the unique values
in column B ("Country"). Load only the "data" sheet and extract the
financial data from column A:P for each unique country value. Create a new Excel file for each country,
containing only the financial data for that country in 2021. Save each Excel file in the attachments folder,
and name each file after the corresponding country. If the attachments folder does not exist,
create it before saving the files. As you can see, this time, I specified that
chatGPT should use Pandas in its code generation. The result was a Pandas solution with some
helpful explanations at the end. Just like before, I copied the code into my
text editor and ran it. And just like that, we have the new workbooks
in our attachment folder. Let me open up the Canada file to ensure everything
is working as expected. If I apply a filter to the workbook, we can
see that it only contains data for Canada in 2021. So, when you know which Python packages you
want to use, make sure that you specify those in your prompts. Another useful feature of chatGPT is its ability
to not only generate new code but also to improve existing code. As an example, let's consider taking the same
spreadsheet and saving each worksheet as a separate workbook. To do this, I created a solution using xlwings. Also, for this task, you can find a video
on my channel. I'll go ahead and run the code to test it
out. After it's done, we'll have a new output folder
with two new workbooks. The data spreadsheet will now only contain
the data worksheet. So far, so good. I then asked chatGPT to look at my Python
code and add some comments to it. The result was code with comments explaining
what each line does. It might be a bit overkill to have a comment
for every single line, but I could have also asked chatGPT only to include comments for
the most important parts. Another awesome thing is that you can keep
talking to it as you go. So, while you're writing documentation or
checking out some new code online, you can ask chatGPT to clarify what certain code does,
which will help you understand what's going on. I also asked chatGPT to convert my code into
a function, and it was able to do so, complete with type hints and a doc string with explanations
of the arguments. As before, I ran this code, and it worked
perfectly. All of these capabilities are really impressive. Of course, chatGPT isn't just limited to automating
Excel tasks. Essentially, you can use it to try and automate
any tedious office tasks you might have. In my final example, I asked chatGPT to create
a Python script that generates a PowerPoint presentation with three slides. The first slide should have the title "Introduction"
and include three bullet points on the left and an image of a dog on the right, obtained
from an API. The second slide should have the title "Methods"
and include three bullet points on the left and an image of a dog on the right. The third slide should have the title "Results"
and also include three bullet points on the left and an image of a dog on the right. chatGPT should use functions and best practices
to make the code pythonic and reusable. The PowerPoint should be saved with the name
"my-awesome-ppt.pptx". In response to this prompt, I received the
following code. We can see that there are different functions
for creating the presentation. As before, chatGPT provided a summary of what
the code does and mentioned that a specific Python library is required to use the code. All I had to do was copy and paste the code,
which I did. After running this code, Python created the
following PowerPoint presentation. While the presentation isn't exactly visually
appealing, and the layout is different from what I had in mind (the bullet points should
have been on the left and the image on the right), it's still a solid starting point. So, what is my conclusion? If you have office tasks that you want to
automate, chatGPT is definitely worth a try. However, the most difficult part for me was
writing a concise and clear prompt that accurately conveyed what I wanted to do, especially for
more complex tasks. It's a good idea to break these tasks down
into smaller subtasks and ask chatGPT to automate them separately before piecing them together. Additionally, it's important to keep in mind
that the code provided by chatGPT may not always work as expected, so you may need to
troubleshoot and make adjustments as necessary. Despite these considerations, I think that
chatGPT is a powerful tool that can help to make your office life easier. I would highly recommend giving it a try. Also, let me know your opinion about ChatGPT
in the comments below. Thanks for watching, and see you in the next
video.
