10X Your Excel Skills with ChatGPT 🚀

Aug 14, 2023

10X Your Excel Skills with ChatGPT 🚀

In this step-by-step tutorial, learn how you can exponentially improve your Excel skills using OpenAI’s ChatGPT artificial intelligence. Write nested functions with ease, calculate the number of unique text values in a list, write a basic invoicing macro, and more, all with just a few clicks and a few basic refinements.

📚 RESOURCES
- OpenAI ChatGPT: https://chat.openai.com/
- Excel workbook to follow along: https://1drv.ms/x/s!AmxrofZZlZ-whYJ-s…
- Article that walks through ChatGPT in Excel: https://kevinstratvert.com/2023/01/18…

⌚ TIMESTAMPS
00:00 Introduction
00:50 Get ChatGPT
01:04 Simple sum example
02:44 Profit example
03:30 Lookup functions
05:16 Left and find nested function
06:26 Unique count nested function
07:43 Write macros
10:51 Wrap up

📺 RELATED VIDEOS
- 5 Mind-blowing Artificial Intelligence Tools:    • 5 Mind-blowing Artificial Intelligenc…
- How to create simple macros in Excel:    • How to Create Macros in Excel Tutorial
- Full tutorial on Excel Macros and VBA:    • 🤖 Excel Macros \u0026 VBA - Tutorial for B…

📩 NEWSLETTER
- Get the latest high-quality tutorial and tips and tricks videos emailed to your inbox each week: https://kevinstratvert.com/newsletter/

🔽 CONNECT WITH ME
- Official web site: http://www.kevinstratvert.com
- LinkedIn: https://www.linkedin.com/in/kevinstra…
- Discord: https://bit.ly/KevinStratvertDiscord
- Twitter: https://twitter.com/kevstrat
- Facebook: https://www.facebook.com/Kevin-Stratv…
- TikTok: https://www.tiktok.com/@kevinstratvert
- Instagram: https://www.instagram.com/kevinstratv…

🎒 MY COURSES
- Go from Excel novice to data analysis ninja in just 2 hours: https://kevinstratvert.thinkific.com/

🙏 REQUEST VIDEOS
https://forms.gle/BDrTNUoxheEoMLGt5

🔔 SUBSCRIBE ON YOUTUBE
https://www.youtube.com/user/kevlers?…

🙌 SUPPORT THE CHANNEL
- Hit the THANKS button in any video!
- Amazon affiliate link: https://amzn.to/3kCP2yz (Purchasing through this link gives me a small commission to support videos on this channel — the price to you is the same)

#stratvert #chatgpt #excel

Content

0 -> Hi everyone,

0.8 -> Kevin here. Today,

2.76 -> we are going to look at how you can 10X your Excel skills by using ChatGPT.

10.52 -> If you've never heard of ChatGPT before it's a state of the art natural language

16.32 -> processing AI made by OpenAI, and you can do some truly incredible things. In a

23.32 -> previous video, we looked at how you can use ChatGPT to write a song about the

28.32 -> Kevin Cookie Company. Naturally,

31.12 -> I also had to turn it into a music video.

35.92 -> Now this is all fun and games, but you can also use ChatGPT to help you get work

41.44 -> done in Microsoft Excel.

43.48 -> And what I found is that it will forever change the way you use Excel.

49.76 -> Let's check out how. Before we get started, you'll need access to ChatGPT, and you

55.2 -> can get there by clicking on the link right up above, or also down below in the

58.68 -> description. You'll need to set up an account, but once you do that, you'll land

62.88 -> on the following page.

64.36 -> Here I am in Excel, and we're going to start out with a very simple example, and

69.72 -> then we'll get a little bit more complex as we go along. Here, we have all the

74.48 -> different markets of the Kevin Cookie Company, and I have all of the associated

78.08 -> revenue and I want to calculate the total revenue, but I'm not quite sure what

83.64 -> function to use. In Excel,

86.2 -> there's a button right up here that I can click on to insert a function,

89.72 -> and here it says to type in a brief description of what I want to do.

93.64 -> Well, I want to add up all the values in cell B2 through B10.

98.12 -> Let's click on go,

99.36 -> and here I don't get anything back.

101.96 -> Let's jump over to ChatGPT to see if that does any better. Here on ChatGPT, down

107.6 -> below,

107.88 -> I can type in my question. Here, write

110.32 -> an Excel formula to add up values in cells B2 through B10.

114.52 -> Then let's click on go.

116.04 -> And here it tells me to use the sum function.

119.12 -> Let me copy this and bring this back into Excel. Here, back in

122.16 -> Excel, I'll paste in the function.

123.92 -> And just like that, I get the sum of all of the revenue.

127.84 -> That's the exact function that I wanted.

130.08 -> Microsoft, maybe you should look at incorporating some ChatGPT functionality

133.96 -> into your insert function helper. Back now within ChatGPT,

137.76 -> one of the neat things about using this is you can have a conversation with the AI.

142.36 -> So here I see the sum function, but maybe I want to understand,

145.32 -> well, how does this work?

146.52 -> What is the colon here?

147.84 -> What is all of this. Here,

149.36 -> I'll type in, can you explain how this function works?

152.2 -> And then I'll hit enter.

153.76 -> And just like that, I get a very thorough explanation of how the sum function works.

159.12 -> And here, I even see an example where I could sum up two separate ranges.

163.44 -> This is really helpful.

164.64 -> Back in my workbook, here I want to calculate the profit and the

168.88 -> profit's the revenue minus the cost.

171.24 -> But let's say I didn't know that.

173.16 -> Let's see if ChatGPT can maybe help me with that. Back in ChatGPT, down

178.36 -> below, I'll type in my question, write an Excel formula to calculate the profit.

183.08 -> The revenue is in cell A2 and the cost is in cell B2 and here I'll click on go.

189.12 -> And here it looks like it tells me the formula is A2 minus B2. Here,

193.92 -> I can copy that and I can look through and read the rest of the response. Here,

197.36 -> it also shows me that I could use sum. Back in Excel, here

201 -> I'll paste in the formula and check that out.

203.44 -> I now have the profit for the Kevin Cookie Company.

206.12 -> Like I've always said, the cookie business is a very good business to be in.

210.12 -> So far, we've just looked at some very simple examples, but you might be

213.84 -> wondering, well, how can AI handle some more complex scenarios? For that,

218.44 -> let's jump into the next worksheet titled Vlookup and index match. On this

222.72 -> next sheet, I want to know the price of sugar cookies.

226.16 -> That's one of my favorite cookies. Over here,

228.8 -> I have a table with all of the cookie types and the associated price.

232.68 -> So here, if I look for sugar cookie, here I find it,

235.2 -> and the price is three. To look this up,

237.92 -> we would typically use lookup functions.

240.04 -> You could use VLOOKUP, you could use XLOOKUP or even a combination

244.04 -> of functions called INDEX MATCH.

246.52 -> Let's see if ChatGPT can figure this one out. Back on the website at the

250.72 -> bottom, I'll type in my question. Write in Excel formula to find sugar

255.08 -> cookie in a table and return the price.

258.24 -> Let's see how it does.

259.6 -> And right here, I get a response back that tells me how the VLOOKUP function works.

264.4 -> I can use this vertical lookup function to get the price back for sugar cookie.

269.08 -> Here

269.32 -> It gives me all this information about how it works and here it even provides

273.24 -> me the specific function that I need to enter into my Excel sheet. Here,

277.8 -> I'll copy this and let's bring this back to Excel.

279.92 -> Back in Excel,

281.16 -> I'll type in the function and here it told me Bn, n being

285.08 -> the number of rows in my table.

287.04 -> So here I have four, so I will replace the n with a four and here

290.76 -> it properly selects the entire table.

293.08 -> Here I'll hit enter and this returns me the price of three.

296.56 -> This worked exactly how I wanted it to. Back on the website,

299.84 -> let me ask if there are any other functions that will do this same thing.

303.36 -> And right here, it tells me that I can also use a combination of functions.

307.24 -> Index together with match, and here it even shows me using my

311.36 -> exact example, how that would work.

314.52 -> This is really helpful. Back in Excel,

316.76 -> let's now try another example. Here,

319.4 -> I want to extract the first name from this email address.

323.92 -> Now I could use something called flash fill. Here

326.24 -> I could type in the first name, hit enter, and here I'll

329.2 -> go back and select this cell.

331.6 -> You can go up to the data tab and then right over here, there's

334.6 -> an option called flash fill.

336.2 -> You could also press the shortcut key control E.

338.72 -> When I click on that, that'll copy this pattern all the way down, but I might

343.32 -> need to know what is the formula or the function that I can use to

346.88 -> accomplish this same exact thing.

349.4 -> Let's jump back to the website.

350.92 -> Back here on the website, I'll type in my question, write an Excel

354 -> formula to extract all the text before the @ character in cell A2.

358.88 -> And look at that, here I get these set of functions.

361.4 -> I can use the left function together with the find function.

364.72 -> I'll copy this and let's bring this back into Excel.

367.36 -> Back in Excel, here I'll highlight cells B2 through B5 and right up here,

372.64 -> I'll paste in the function that ChatGPT recommended.

376.2 -> I can now press control together with enter, and that'll paste this same

380.92 -> function into all of these different cells.

383.12 -> I'll press that and I get the same exact result.

385.92 -> You're probably starting to realize that this AI is pretty smart and powerful,

389.84 -> but we're only getting started.

391.28 -> Let's click into the next sheet called unique count, and this has always been

395.48 -> something that's been a little bit of a challenge in Excel. Here,

398.56 -> I want to get a unique count of all of the cookie types that

401.88 -> we sell here at the cookie company.

404.52 -> Now you'll see that there are some duplicate entries. Here,

406.96 -> I have chocolate chip, chocolate chip again, and here's another chocolate chip.

410.16 -> I just want to know how many unique cookies do we sell?

413.48 -> So for that, let's jump back to the website. Here,

416.2 -> I typed in my question, write an Excel formula to count the number of

419.88 -> unique values in a list, here I get an explanation along with a sample

424.92 -> function of how I can make this work.

427.04 -> I'll copy this and let's go back to Excel. Back in Excel, here

431.4 -> I'll paste in the functions and I have to make some minor tweaks. Here,

435.88 -> it's looking at this entire range, but I don't want to include the header.

439.28 -> So here I'll make a minor tweak.

441.2 -> I'll change this from a one to a two and I'll make the same

444.52 -> change here and then hit enter.

446.08 -> And here, it tells me that there are seven unique values.

449.56 -> So we have chocolate chip, oatmeal raisin, this is a duplicate,

452.96 -> then here, these are unique values and here's another duplicate.

456.64 -> We do in fact have seven different unique values.

459.76 -> So that worked exactly how I expected it to.

462.36 -> OK.

462.88 -> OK.

463.4 -> So ChatGPT can help with entering in different functions, but what about

468.88 -> the really complicated stuff like writing a macro from scratch?

474.32 -> For that, let's click on the sheet titled macro. Here on the macro sheet,

478.8 -> I want to write a simple invoicing app. Here,

482.36 -> I have customer email addresses, and I also have how much they owe

486.52 -> the Kevin Cookie Company, and I want to send all of them an email with the amount due.

491.84 -> First, we need to enable macros.

494.8 -> Let's go up to the ribbon up on top and you can right click here and

497.96 -> then select customize the ribbon. Over on the right-hand side,

502.16 -> let's make sure to check the developer checkbox, then click on OK.

506.88 -> And right here, there's a new tab for developer.

509.76 -> Let's click on that. Over on the far left-hand side,

513.04 -> let's click into Visual Basic.

515.48 -> Then let's go up to insert and here I'll insert a new module. Here on ChatGPT,

520.56 -> I'll type in a command, here write an Excel macro to send emails, use the

525.12 -> following subject, Kevin Cookie Company invoice, use the following text.

530.24 -> You owe the Kevin Cookie Company X, take the value X from column A.

534.96 -> If I jump back into the Excel sheet, here we see the amount do is in column A.

538.96 -> I'll go back to ChatGPT, send to the email address listed in column B. Here,

544.6 -> back in Excel, we have the email address in B. Let's now see

548.36 -> what ChatGPT pulls together. Here,

550.56 -> I'll click on send, and look at this,

553.08 -> it generated an Excel macro that will send out emails using my subject and

558.04 -> all of the body information. This will use both Excel and Outlook. Right here,

562.76 -> I can click on copy code. Down below, here

565.96 -> I can see an explanation of how this macro works.

569.08 -> And there's also a note telling me that I need to enable the Outlook object library.

573.56 -> So I'll make sure to do that before I run this macro.

576.52 -> Let's go back into Excel. Back within Excel's Visual Basic editor,

580.84 -> first let's click on tools and then references.

583.72 -> And here I can confirm that the Microsoft Outlook object library is enabled. Here,

588.68 -> I'll click on OK.

590.44 -> Next I'll paste in all of the code from ChatGPT.

594.84 -> Now I looked through all of the code and I found one issue. Here,

598.68 -> it looks in column A for the email address, but my email address is in column B.

603.4 -> So I need to update this reference.

606.12 -> If you're interested in learning about how to understand how macro works and

610.28 -> even how to write your own macros, be sure to check out the video right up above,

614.44 -> where I walk through a full tutorial of how you can create your very own

618.84 -> macros in Excel. All of this looks good here,

621.8 -> so I'll click on save up above, and then let's click on the X to close this out.

625.88 -> Back now in Excel, on the developer tab over on the left-hand side, let's click

630.92 -> on macros and here we see the new macro send emails and I'm all ready to run it.

637 -> Here I'll click on a run and that is now successfully sent out the emails.

641.8 -> Let's go to Outlook to confirm. And look at this!

645 -> Here's the email message with my custom subject and also the custom body.

649.64 -> That is so phenomenal.

652.76 -> I don't know about you, but Excel doesn't really seem that intimidating anymore.

657.56 -> If you have to write nested functions, or if you have to write a macro, it's no

661 -> problem.

661.48 -> You could have AI get started and maybe you just have to go in and make a few

664.76 -> refinements. To watch more videos like this one,

668.44 -> please consider subscribing, and I'll see you in the next video, or maybe you'll

673.8 -> see a Kevin AI in the next video, given the pace of which AI technology is

678.52 -> advancing.

Source: https://www.youtube.com/watch?v=JYtZ2zsdE_s