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,.
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..
Posts Related:
- Cara Halus Menolak Masuk Grup WA dan Cara Sembunyikan Nomor Kontak
- 4 Steps Gear Infographics **Free Download** How to convert picture to shape. Powerpoint Presentation Office
- Sistema de Painéis de EstatÃsticas Financeiras Gráfico de mapa dinâmico do Excel Tutorial 2 Office
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,.
