Hey guys,
In this video, I'll show you how to take your productivity in Excel to the next level by
integrating ChatGPT. By the end of this tutorial, you'll have a
new button, ready to generate Excel formulas, VBA code or, like in my example, a travel
plan. To use it, simply select the cell with your
prompt, click the button, and ChatGPT will generate a response that will appear in a
new worksheet. I'll also be sharing some practical use cases
for this tool and guide you through the process of adding this button to your Excel files. So, get ready to boost your Excel workflows
with ChatGPT! Before we begin, a short disclaimer: This
video is heavily inspired by Leila's video. She wrote an Office Script to bring ChatGPT
into Excel, and I thought to myself, 'Hey, I can do that too... but with VBA!' So, that's the reason I created this video. If you're interested in the Office script
solution, then definitely check out her video. I'll leave the link to the video in the description
below. And with that out of the way, let's begin
with the first example. As an Excel nerd, I even use spreadsheets
to plan my vacations. With the help of ChatGPT, I can now speed
up the vacation planning process. In this example, I asked ChatGPT to create
an itinerary for a day trip to Barcelona, Spain. After clicking the button, you will see in
the status bar that your request is being processed. Once completed, a message box will appear,
and a new worksheet will be inserted. On the results sheet, you will now find your
itinerary for the day trip. Next up, I am going to generate some sample
data, which I will use in our next example. For this purpose, I want to create some fictional
employee IDs that have a particular format. Once again, when I click the button, the output
will appear on the result sheet. As I said, we will use this data in the next
example, so let me create a new sheet and paste the data here. Suppose you want to clean up the data and
extract only the numbers from the ID, but you are unsure which Excel formula to use. You can now turn to ChatGPT for help. I provided an example of the format and asked
for an Excel formula to extract the numbers. After clicking the button, the formula will
appear on the results sheet. Let me copy this cell and paste it next to
the ID. I only need to update the cell reference,
so instead of B1, the data is located in A3. Once that is done, I can simply drag the formula
down, and now I have the cleaned ID. Instead of Excel formulas, you can even create
entire VBA codes. For this example, I want to split each worksheet
in the current workbook into separate Excel files and save them on my desktop. When I click the button, I receive the VBA
code in the results sheet. All I have to do now is to run the code. So, let me copy the code, go to the Developer
tab, and open the Visual Basic editor. In a new module, I will paste the code. Just by looking at this, I can already see
that VBA won't accept the double backslashes. This could be because the API response from
OpenAI is in JSON, and I might need to implement an additional cleaning step. Nonetheless, I'll try to execute it. As I said, this code currently leads to an
error, but to fix it, all you need to do is adjust the file path. Now, when I rerun it, the worksheets will
be saved as separate workbooks on my desktop. To validate it, let me open up one of the
spreadsheets. Here we have our Employee data. Now, the cool thing is that the ChatGPT functionality
is available in all my workbooks. It doesn't need to be a macro-enabled workbook. Let me show you what I mean. You can pick any cell you want. For example, I will ask ChatGPT for a joke. Next, all I need to do is select the cell
and then click the smiley emoji up here. Once done, a new Results worksheet will appear,
and I can find the joke there. By the way, if you select an empty cell and
try to run it, you will get the following error message. Now that you've seen how it works let me show
you how to add this functionality to Excel. All you need is an OpenAI API key. Go to the following website; you will find
the link in the description below. Next, create a free account. No credit card is required. When signing up and creating an API key, OpenAI
will grant you $18 in free credit that can be used during your first three months. After that, you would need to pay to use their
service. Ok, and with that in mind, log in to your
account, and in the upper right corner, click on "Personal" and select "View API keys". On this page, click on "Create new secret
key". Now, it is important that you do not expose
this API key. Just for the sake of this video, I will show
you my API key. However, when you watch this video, this key
will no longer be valid. With that said, let me copy this API key and
put it temporarily in my browser. We will come back to this API key in just
a moment. Next, open up a new Excel workbook and then
click on "Developer". If you do not see the "Developer" tab, right-click
on your ribbon and select "Customize Ribbon". Ensure that you tick the box next to Developer
and confirm your selection by clicking OK. With that in place, you can now click on "Record
Macro". It is important that you select "Personal
Macro Workbook" in the drop-down field here. The name and description do not matter; Next,
click on OK. Right after that, you can stop the recording
by hitting this button. Now, open up the Visual Basic Editor. If everything went well, you should now see
a new section called "Personal.xlsb". Open this section and navigate to "module",
and open "Module1". In this module, we will paste the VBA code
I uploaded to GitHub. You will find the link to the GitHub repo
in the description of the video. If you click on it, you will see this site. All you need to do is click on the copy icon
up here. Then, navigate back to the Visual Basic editor,
select all existing code, and replace it with the code you just copied. On the very top of this script, you will find
a section where you need to input your API key. So, in my case, I pasted the API into my browser. Let me go back, grab the API key and paste
it here. Next, I just need to assign this macro to
the Quick Access Toolbar. Therefore, in Excel, click on the arrow key
in the top left corner and select "More Commands". In the new window, select "Macros" and add
the macro from the Personal Workbook to your Quick Access Toolbar. Optionally, you can also modify the button
icon. Once done, click "OK". When you close the workbook, you will get
a message asking you to save the changes to the Personal Workbook. It is important to click on "Save" to make
the VBA code available in all your Excel files. To test it, let me open a new spreadsheet. I can now simply enter my prompt in any cell,
select that cell, and run the VBA code from the Quick Access Toolbar. The response from ChatGPT will appear in a
new worksheet. And if you want to create a workbook similar
to the one I showed you in the beginning, you can also execute the macro by clicking
a button. To do this, draw a shape on your sheet, right-click
on the shape, select "Assign Macro," choose "Personal.xlsb" from the drop-down menu, and
select the "OpenAI Completion" macro. And for those who are interested in how this
works, let me briefly explain the VBA code. Essentially, it sends an HTTP request to the
OpenAI API. You can also adjust the API endpoint parameters,
which are detailed in the OpenAI documentation. In the code, I first check if you have inserted
your API key. If so, it checks if the current cell is not
empty. If it's not empty, the VBA code creates a
result worksheet if it doesn't already exist. To inform the user that the code is running,
the status bar is updated before the HTTP request to the OpenAI API is created. After sending the request, the code checks
the status code. A status code of 200 means the request was
successful. As I mentioned before, the response is in
JSON format, so I am cleaning up the response text and inserting each line in a separate
row. Once this is complete, the new sheet is activated,
and the user is informed via a message box. If the status code is not 200, the user is
notified via a different message box. In case of any error, the error number, description,
and line number are displayed in yet another message box. In addition to the main sub, there are helper
functions used to check if the output worksheet already exists, parse the JSON response, clean
the input prompt for the cell, and remove unwanted backslashes in the response from
ChatGPT. Okay, guys, that's pretty much all the code. I hope you found this solution helpful. If you did, please let me know by hitting
the like button. And as always, thank you for watching, and
I'll see you in the next video.
