AI can make you unbelievably more
productive, but that productivity could be improved if you didn't have to
leave your app to go to another page. And you've all seen videos where ChatGPT solves
Excel problems, writes code, or explains code. And to do that, we have to go to the OpenAI platform,
ask it what we want, then copy and paste it into our application. So this video is not that type
of video. This is where we're going to bring AI inside our application, right? So that's what
I did here. I brought it inside my Excel file so that I can ask a question in a cell and get
the reply in the next cell. If I happen to ask it a question like "Give me the names of The
Sopranos cast," it puts it all in a single box, and that bothered me. I want to put it in multiple
separate rows, right, because it's easier to take that information and then work with it in Excel.
Of course, we don't want to have to do this manually, so I decided to build on this. Whenever
ChatGPT's answer is more than a single line, we're going to get a multiple-row version of
it as well. You have no idea how excited I was when I got this code to work. I'm going to
show you how I set it up, and the purpose of this video is to inspire you and show you the
possibilities that are out there. Let's go. Okay, so over here on Excel for Desktop, I've
created this simple layout, and in this box, I can ask my question. So let's say I'm talking
with my colleagues, what the top 10 hip-hop songs of the 90s were. Okay, so then we click on
"Ask," and this is where my script is running. The moment I click on this, notice on the side
pane here, we get Script Run Status. This is based on Office Scripts. I'm going to show
that to you in a second, how it works. Once we get the answer from the AI back, we put it in
this cell. Oh yeah, these were my party songs. Let's do another one. Let's say we're discussing
what the names of the Spice Girls members were. Do you remember the Spice Girls? Do you know
what they were? Let's see if the AI knows. Yeah, that's them. Let's include their
nicknames as well because they did have some weird, strange nicknames like
Scary, Bossy, Baby, oh, no Bossy. Now, let's say I finally decide to get some
work done, but I'm stuck. I can't figure out this formula that I need to put in here. So
here's what I want to do. I want to grab the total revenue from either the Health sheet
or the Productivity sheet or the Game sheet. I have different sheets here. They have a
similar structure. My revenue is in column B. What I wanted to do is write a SUM function
here that looks into the correct sheet. So, if this is Game, you should go to the Game sheet
and grab the B column. If it's Productivity, you should go to the Productivity sheet. I
don't want to write an IF function because I am going to have a lot of different
sheets. I can't figure out the formula, but ask our AI. Okay, I'm just going to pin this
in place and start with "I need an Excel formula. I need to get the sum of column B from different
sheets. Now, the sheet name is in cell A2." Okay, so this is very specific to what I need
and I want it to give me the formula. So, let's ask our AI and see what we get. Okay, so
we get: "Assuming the sheet name is in cell A2, you can use the following formula." Well,
this looks good. Let me just copy this, go to my sheet, and paste this in. Okay, it looks
like everything is in order and I get a number. Does it really work? Let's sum up Game revenue.
What's our total? 210,616. That's what we get, 210,616. So, if I change this to Productivity,
162,643. Let's just make sure that it works. Yes, it works. It gave me the right formula. This saved me
a lot of time of having to go through different websites, different forums, and blog posts. I
got the answer that I wanted by just asking. Now, let's say my boss asked me to analyze
the balance sheet for BMW. So, I can find their balance sheet on the web here and I've done
my task, I've analyzed it all on my own, but I want to get a second opinion from
the AI and see what it has to say. So, I'm just going to copy everything, go back
here, paste it in, and let's just go all the way to the top and tell it what we want
it to do. We want to analyze the balance sheet and tell us what it thinks the risks are.
Okay, so let's ask. Okay, so we get a response: "The balance sheet includes the following:
a high level of intangible assets, which could be a risk if the company's business model
changes. The company has a large amount of debt and the company has a large number of receivables,
which could be a risk if the customers don't pay." Now, you can run this multiple times and you
are going to get different responses. You can use these to cross-check with your own response,
but of course, don't take what the AI says as the truth. You are responsible to cross-check these
and come up with your own informed decision. We can also use the AI to quickly give us
some data we can work with. For example, we want to get the top 10 countries by area. Let's
ask and see what we get. Okay, so we get our list here. Now, everything is inside the same cell.
That's where this part comes into play. So, I've updated the script so that whenever the result
is more than one line, we get it into separate rows and we get it on this sheet here. This makes
it easier for me to work with the data in Excel. If you're for example, giving some training, and
you want some fake data, you can ask it as well. So, for example, we want to get sales data and
budget data from January to December for a fake company. Format should be month, actual sales,
and budget sales. Let's ask and see what we get. That's our data right here. Now that we get it
into separate cells, it's easier to work with. Now, you can also update the logic to account
for the delimiter. It's just that you can get different delimiters back from the AI, but if you
have it in this format, it's really easy to split it into separate columns. You just have to go to
the "Data" tab, here, "Text to Columns", select the limited, go next, and then select the type of
the delimiter you have. So, here is a comma and a space, Finish, and we have our data in separate
columns. Now, I can use this as fake data for my training. If you're a teacher, you could go with
a prompt like this: "Give me names of the Breaking Bad characters, add a fake school subject and fake
grade, and grades should be in percentage." Click on "Ask", and that's what we get. Tuco Salamanca
is not bad in math. Notice here, the delimiter is different here, but we get them into some
separate rows, and then again, we can use Text to Columns or Power Query or TEXTSPLIT function
if you'd like to split this into separate columns. So, I hope these examples give you some ideas
on how you could use AI to speed up your work. Now, let me show you how I've set this up. So,
this setup consists of two main ingredients: one is Office Scripts. Now, Office Scripts
was first introduced for Excel on the web, but recently, we got this Automate
tab in Excel for desktop as well. So, this is the Office 365 version. The advantage
is that, whenever you automate your tasks using Office Scripts, that automation
is going to work for Excel Desktop, it's going to work for Excel on the web or
if you use Excel in Teams, it's going to work in all places. The second ingredient is
an API for GPT from OpenAI, and to get that, you have to go to the OpenAI website, then click
on API, set up an account if you don't have one, and then you can get your API key. So, if I
go to my account here, go to your account, and then you can generate your secret key. This is
currently free for up to three months, depending on your usage. After that, you're going to need
to upgrade. The API key you get is for GPT-3. So, ChatGPT is a version of GPT-3. It was fine-tuned
for conversation and dialogue. GPT-3 is broader. Now, once you have that, you can go ahead
and check out the documentation. There are guides on how you can use this API, how you can
make requests, and so on. Right, so have a read through this, then you get some ideas on how
you can integrate this into your own models. Now, once you have this, you can go
over to Excel and create a new script, and you can also do this for Excel on the
web if you don't have the "Automate" tab yet in Excel for Desktop. The script you write
is called "Office Script". Office Script is written in TypeScript and it's a superset of
JavaScript. The script is saved on my OneDrive, you can also save it on SharePoint. So, let me
quickly walk you through this, just so you get an idea on how it's built. The starting point
of any Office Script is the "main" function, and here I'm using an asynchronous function. This
allows the code to run independently of other code. We're also using "ExcelScript.Workbook", so
that we can communicate with the Excel objects. We need to provide the API keys. I've defined a
constant for this. This is where you would be pasting it in. I've pasted it inside a cell in a
sheet that I've hidden. Next up, I've defined some more constants. So, here we have "mytext," this is
cell B2. So, this is the "Ask," what we're sending to the AI. And down here is the communication
with the model. We're sending the text to the AI model and then here, we're getting the output
back. The output is what we paste back in cell B4, right here. You can use the documentation on
OpenAI to help you set this up. And in my case, I got stuck and I asked ChatGPT for help
and it was able to solve the problem. So, this part was a collaboration with ChatGPT. We
got it to work. Then, I went on to add this part that allows me to check whether I have more than
one line, and if I do, it's going to split it and paste it into separate cells. So, this is where
that happens. We are using the "Split" function. This basically splits the string into a list of
smaller strings and puts it inside an array. Then, we loop through that array. For each instance, I'm
checking whether the array length is greater than zero, and the reason I'm doing that is because
sometimes the AI returns empty lines in between, and if I don't account for this, I end up
having empty rows here, and I don't want that. I just want my rows with text to be below
one another. OK, so that's basically the script, and you can build upon this as you need. I so wish
I had this when I was starting to learn Excel. There were times I could spend hours just getting
my formula to work. Let me know what you think. I personally enjoyed this experiment just to see
the potential we could have in Excel. Who knows, maybe in the future, we can ask it to
import the data from Bob, summarize it, and then send it to the boss. Right? Something
to look forward to. But let me know what you think. Comment below. I hope you enjoyed
this video, and I'll catch you next time.
