10X Your Excel Skills with ChatGPT 🚀

Hi everyone, Kevin here. Today, we are going to look at how you can 10X your Excel skills by using ChatGPT. If you've never heard of ChatGPT before it's a state of the art natural language processing AI made by OpenAI, and you can do some truly incredible things.

In a previous video, we looked at how you can use ChatGPT to write a song about the Kevin Cookie Company. Naturally, I also had to turn it into a music video. Now this is all fun and games, but you can also use ChatGPT to help you get work done in Microsoft Excel.

And what I found is that it will forever change the way you use Excel. Let's check out how. Before we get started, you'll need access to ChatGPT, and you can get there by clicking on the link right up above, or also down below in the description.

You'll need to set up an account, but once you do that, you'll land on the following page. Here I am in Excel, and we're going to start out with a very simple example, and then we'll get a little bit more complex as we go along. Here, we have all the different markets of the Kevin Cookie Company, and I have all of the associated revenue and I want to calculate the total revenue, but I'm not quite sure what function to use.

In Excel, there's a button right up here that I can click on to insert a function, and here it says to type in a brief description of what I want to do. Well, I want to add up all the values in cell B2 through B10. Let's click on go, and here I don't get anything back.

Let's jump over to ChatGPT to see if that does any better. Here on ChatGPT, down below, I can type in my question. Here, write an Excel formula to add up values in cells B2 through B10.

Then let's click on go. And here it tells me to use the sum function. Let me copy this and bring this back into Excel.

Here, back in Excel, I'll paste in the function. And just like that, I get the sum of all of the revenue. That's the exact function that I wanted.

Microsoft, maybe you should look at incorporating some ChatGPT functionality into your insert function helper. Back now within ChatGPT, one of the neat things about using this is you can have a conversation with the AI. So here I see the sum function, but maybe I want to understand, well, how does this work?

What is the colon here? What is all of this. Here, I'll type in, can you explain how this function works?

And then I'll hit enter. And just like that, I get a very thorough explanation of how the sum function works. And here, I even see an example where I could sum up two separate ranges.

This is really helpful. Back in my workbook, here I want to calculate the profit and the profit's the revenue minus the cost. But let's say I didn't know that.

Let's see if ChatGPT can maybe help me with that. Back in ChatGPT, down below, I'll type in my question, write an Excel formula to calculate the profit. The revenue is in cell A2 and the cost is in cell B2 and here I'll click on go.

And here it looks like it tells me the formula is A2 minus B2. Here, I can copy that and I can look through and read the rest of the response. Here, it also shows me that I could use sum.

Back in Excel, here I'll paste in the formula and check that out. I now have the profit for the Kevin Cookie Company. Like I've always said, the cookie business is a very good business to be in.

So far, we've just looked at some very simple examples, but you might be wondering, well, how can AI handle some more complex scenarios? For that, let's jump into the next worksheet titled Vlookup and index match. On this next sheet, I want to know the price of sugar cookies.

That's one of my favorite cookies. Over here, I have a table with all of the cookie types and the associated price. So here, if I look for sugar cookie, here I find it, and the price is three.

To look this up, we would typically use lookup functions. You could use VLOOKUP, you could use XLOOKUP or even a combination of functions called INDEX MATCH. Let's see if ChatGPT can figure this one out.

Back on the website at the bottom, I'll type in my question. Write in Excel formula to find sugar cookie in a table and return the price. Let's see how it does.

And right here, I get a response back that tells me how the VLOOKUP function works. I can use this vertical lookup function to get the price back for sugar cookie. Here It gives me all this information about how it works and here it even provides me the specific function that I need to enter into my Excel sheet.

Here, I'll copy this and let's bring this back to Excel. Back in Excel, I'll type in the function and here it told me Bn, n being the number of rows in my table. So here I have four, so I will replace the n with a four and here it properly selects the entire table.

Here I'll hit enter and this returns me the price of three. This worked exactly how I wanted it to. Back on the website, let me ask if there are any other functions that will do this same thing.

And right here, it tells me that I can also use a combination of functions. Index together with match, and here it even shows me using my exact example, how that would work. This is really helpful.

Back in Excel, let's now try another example. Here, I want to extract the first name from this email address. Now I could use something called flash fill.

Here I could type in the first name, hit enter, and here I'll go back and select this cell. You can go up to the data tab and then right over here, there's an option called flash fill. You could also press the shortcut key control E.

When I click on that, that'll copy this pattern all the way down, but I might need to know what is the formula or the function that I can use to accomplish this same exact thing. Let's jump back to the website. Back here on the website, I'll type in my question, write an Excel formula to extract all the text before the @ character in cell A2.

And look at that, here I get these set of functions. I can use the left function together with the find function. I'll copy this and let's bring this back into Excel.

Back in Excel, here I'll highlight cells B2 through B5 and right up here, I'll paste in the function that ChatGPT recommended. I can now press control together with enter, and that'll paste this same function into all of these different cells. I'll press that and I get the same exact result.

You're probably starting to realize that this AI is pretty smart and powerful, but we're only getting started. Let's click into the next sheet called unique count, and this has always been something that's been a little bit of a challenge in Excel. Here, I want to get a unique count of all of the cookie types that we sell here at the cookie company.

Now you'll see that there are some duplicate entries. Here, I have chocolate chip, chocolate chip again, and here's another chocolate chip. I just want to know how many unique cookies do we sell?

So for that, let's jump back to the website. Here, I typed in my question, write an Excel formula to count the number of unique values in a list, here I get an explanation along with a sample function of how I can make this work. I'll copy this and let's go back to Excel.

Back in Excel, here I'll paste in the functions and I have to make some minor tweaks. Here, it's looking at this entire range, but I don't want to include the header. So here I'll make a minor tweak.

I'll change this from a one to a two and I'll make the same change here and then hit enter. And here, it tells me that there are seven unique values. So we have chocolate chip, oatmeal raisin, this is a duplicate, then here, these are unique values and here's another duplicate.

We do in fact have seven different unique values. So that worked exactly how I expected it to. OK.

OK. So ChatGPT can help with entering in different functions, but what about the really complicated stuff like writing a macro from scratch? For that, let's click on the sheet titled macro.

Here on the macro sheet, I want to write a simple invoicing app. Here, I have customer email addresses, and I also have how much they owe the Kevin Cookie Company, and I want to send all of them an email with the amount due. First, we need to enable macros.

Let's go up to the ribbon up on top and you can right click here and then select customize the ribbon. Over on the right-hand side, let's make sure to check the developer checkbox, then click on OK. And right here, there's a new tab for developer.

Let's click on that. Over on the far left-hand side, let's click into Visual Basic. Then let's go up to insert and here I'll insert a new module.

Here on ChatGPT, I'll type in a command, here write an Excel macro to send emails, use the following subject, Kevin Cookie Company invoice, use the following text. You owe the Kevin Cookie Company X, take the value X from column A. If I jump back into the Excel sheet, here we see the amount do is in column A.

I'll go back to ChatGPT, send to the email address listed in column B. Here, back in Excel, we have the email address in B. Let's now see what ChatGPT pulls together.

Here, I'll click on send, and look at this, it generated an Excel macro that will send out emails using my subject and all of the body information. This will use both Excel and Outlook. Right here, I can click on copy code.

Down below, here I can see an explanation of how this macro works. And there's also a note telling me that I need to enable the Outlook object library. So I'll make sure to do that before I run this macro.

Let's go back into Excel. Back within Excel's Visual Basic editor, first let's click on tools and then references. And here I can confirm that the Microsoft Outlook object library is enabled.

Here, I'll click on OK. Next I'll paste in all of the code from ChatGPT. Now I looked through all of the code and I found one issue.

Here, it looks in column A for the email address, but my email address is in column B. So I need to update this reference. If you're interested in learning about how to understand how macro works and even how to write your own macros, be sure to check out the video right up above, where I walk through a full tutorial of how you can create your very own macros in Excel.

All of this looks good here, so I'll click on save up above, and then let's click on the X to close this out. Back now in Excel, on the developer tab over on the left-hand side, let's click on macros and here we see the new macro send emails and I'm all ready to run it. Here I'll click on a run and that is now successfully sent out the emails.

Let's go to Outlook to confirm. And look at this! Here's the email message with my custom subject and also the custom body.

That is so phenomenal. I don't know about you, but Excel doesn't really seem that intimidating anymore. If you have to write nested functions, or if you have to write a macro, it's no problem.

You could have AI get started and maybe you just have to go in and make a few refinements. To watch more videos like this one, please consider subscribing, and I'll see you in the next video, or maybe you'll see a Kevin AI in the next video, given the pace of which AI technology is advancing.