How ChatGPT Can Help With Your Complex Excel Spreadsheets

Aug 14, 2023

How ChatGPT Can Help With Your Complex Excel Spreadsheets

🤔 Can you skyrocket your productivity with ChatGPT? See what happens when I copy \u0026 paste YOUR complex Excel questions from YouTube into ChatGPT. Do you think it will give you the right answer? Let’s find out what Chat GPT is capable of. Can it properly solve complex Microsoft Excel spreadsheet problems?
What is chatgpt? Chat GPT, the new, viral AI chatbot by Openai is a type of artificial intelligence that can understand and generate natural language text. You’ll learn how to use it for Excel formulas and how to even make it write VBA code for your Excel macros.
I’ll also show you how to use ChatGPT and how you can modify your questions to get better answers.

Join 300,000+ professionals in our courses: https://www.xelplus.com/courses/

00:00 How to Use ChatGPT with Excel
00:52 Write Excel Formula with ChatGPT
05:06 Data Validation
08:42 Nested IF Formulas with ChatGPT
12:42 Writing VBA Code
17:14 Wrap Up

🌍 My Online Courses ► https://www.xelplus.com/courses/

👕☕ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/

🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/

📚 Excel RESOURCES I Recommend: https://www.xelplus.com/resources/

🎥 GEAR I use: https://www.xelplus.com/resources/#gear

More resources on my Amazon page: https://www.amazon.com/shop/leilagharani

🚩Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani

👉 This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#chatgpt #excel #openai

Content

2.94 -> There have been some really cool developments in AI recently, especially with opening up ChatGPT

7.98 -> to the public. This made me think: How could we use ChatGPT when we're stuck writing an

13.14 -> Excel formula? Let me show you some really cool examples. Okay, so what I'm going to do is take

18.24 -> some of the questions you've asked me and see if ChatGPT can give you the correct answer. In case

23.82 -> you're not familiar with ChatGPT is, it's basically an AI chatbot. You can ask it questions and it

30 -> answers. It's currently free. All you need to do is go to OpenAI.com. You can scroll down here,

35.7 -> go to ChatGPT, and then try ChatGPT or just go to this URL directly. As you can see, there is

42.78 -> currently high demand for this because it's such an impressive tool and it's super easy to use.

47.94 -> All you need to do is type in your question here and it's going to answer it for you. Let's take a

53.58 -> look at our first question. Here's a comment from Steel Graves underneath this video and he asked,

59.34 -> "I have a question and hoping you have a way to do this easily. Is there a formula for calculating

64.26 -> the number of words in an Excel sheet? I see VBA but looking for something simpler. Hoping for your

70.86 -> wiz brain." Well, it's not going to be my brain, it's going to be ChatGPT's, so let's see what

75.36 -> ChatGPT has to say to this. I'm just going to copy this part of the question, Ctrl + C, let's go to

82.68 -> ChatGPT and see what it has to say. Okay, I'm just going to paste it in and see what we get. Okay,

89.7 -> since "there is no built-in formula in Excel that you can use to calculate the number of words in

94.8 -> a sheet, however you can use the LEN() function to calculate the number of characters in a cell and

99.54 -> then divide the number by an average number per word to estimate the number of words." That's not

105.78 -> really what I want to do. Now, it gets interesting. You can also use SUBSTITUTE to replace spaces

111.48 -> with nothing. Then use the LEN function to count the number of letters and then add 1 to get the

118.26 -> number of words. Okay, so assuming that you want to check the number of words in cell A1. Okay,

125.88 -> so now, it went back to a single cell. I don't want just a single cell. Okay, so alternatively

131.28 -> you can use VBA or macros to do the same. Now you also have to keep in mind that every time you ask

136.08 -> it a question, it can be the exact same question, it can give you different answers. Look, it's

141.72 -> going ahead and writing a VBA code even though we actually said we didn't want that. We just wanted

146.7 -> something simpler. You have defined the function, you can call it by entering the formula. Oh,

152.16 -> it's written a function in VBA, that's also great. But yeah, I didn't want the VBA version, I want

158.52 -> the formula version. So let's see if I can tell it exactly what I want. I'd like to go with the

164.22 -> formula version, update it to apply to range A1 to C100 and exclude blank cells. Those ones shouldn't

177.06 -> be counted. Okay, you can use the following formula to estimate the number of words in a

181.2 -> range of cells while excluding blank cells. Okay, this formula uses the SUMPRODUCT function...[reading]

188.76 -> ...and divide by 5 to estimate the number of words. You don't want any estimation, we actually want

195.24 -> to get the number of words. This formula uses the same idea of replacing spaces with nothing

200.4 -> and counting the number of letters and adding one to get the number of words, but this time it also

206.04 -> makes sure that only non-black cells are counted by using this. Okay. This seems good, let's just

213.36 -> test it out. I'm going to copy it, let's go to Excel. I'm going to paste it in here, okay, so

219.42 -> A1 to C100. Right now I get 0, there's nothing in there. So let's... Five words. That looks great.

229.5 -> That seems to work. And this formula is valid for older Excel versions as well, and you can update

234.84 -> it as you need to depending on your range. Now I try to get ChatGPT to rewrite this using the new

241.14 -> Office 365 function like the TEXTSPLIT function, and it tried. We had a few back and forth so it

247.14 -> even recognizes that TEXTSPLIT is a newer function that was introduced in Excel 365. So if you're

252.3 -> using an earlier version, it's not going to work. But the problem was that this function wasn't

258 -> working, so we had a bunch of back and forth and none of them worked. You just have to keep

262.74 -> in mind though that ChatGPT doesn't have access to the internet and it only has limited information

268.26 -> after 2021. But if it knows the TEXTSPLIT function, it must know the TEXTJOIN function because that got

275.52 -> introduced before. But it wasn't able, at least in this iteration that we were going through, to

280.68 -> combine these functions to give me the result that I wanted, so I ended up writing my own version.

285.6 -> In case you're interested, it's this one here. We can test it out, if I just add other texts, it is both

291.84 -> versions give us the same results. It's just much easier and simpler to write the second version, of

297.78 -> course, only if you have Office 365 or Excel 2021. But the great thing is that the first version it

304.86 -> gave me did work. Now let's take a look at another question. This comment is from Cary, and she

311.7 -> asks "I've been trying to use data validation and cells that have formulas in them, but it seems

316.68 -> like it doesn't work even in the latest Excel versions. For example, cell A2 contains formula

322.5 -> B2 plus C2. If I set the validation on cell A2 saying that the sum shouldn't be greater than 10,

329.04 -> any number could still be put into cells B2 and C2, and A2's error message won't be triggered

336.12 -> even if their sum is greater than 10." And yes, that's true. Data validation doesn't work on

344.46 -> cells that are driven by formulas. It works on input cells. Now let's see what ChatGPT has to

350.88 -> say about this. Okay, so I'm just going to paste in the comment and I'm going to remove this part,

356.28 -> "just wondering if you already have a video for this," and let's see what ChatGPT has to say:

361.32 -> "Excel's data validation feature is intended to validate the data that a user enters into a cell,

366.84 -> rather than the results of a formula." It's smart; it knows that. "Because the result of formula depends on the

371.82 -> values in other cells, the data validation rule is applied to the formulas input cells." Okay.

377.1 -> "To validate the results of a formula, you can use a conditional formatting rule that checks

381.84 -> the result of the formula and highlights the cell if it does not meet the criteria. For example,

386.64 -> you could create conditional formatting that checks if the value in cell A2 is greater than

392.52 -> 10 and applies a red fill color to the cell if it is. This way, when the values in cells B2 and C2 cause the

400.14 -> sum to exceed 10, the cell will be highlighted in red indicating an error." That's great.

406.98 -> It says, "You can also use custom VBA script for this. Alternatively, if you have advanced formulas

412.5 -> in complexity, you might consider using Excel add-ons." Okay, so we have different suggestions,

418.02 -> but one suggestion would also be to add an "IF" statement right to check if A2 is greater than 10,

425.16 -> then it should return error, otherwise valid. Now let's say we are interested in the "Conditional

431.4 -> Formatting" method, but we don't know how to go about this. So let's see if ChatGPT can tell us.

437.94 -> Okay, "To set up conditional formatting for a range of cells in Excel, use the "New Rule"

442.8 -> option. Here's an example of how to set up a rule that applies a red fill color if the cell value

450 -> is greater than 10." Okay, so first we need to select it, go to "Conditional Formatting",

456.6 -> select "New Rule", use the formula, and use this function, and then format it to have a

465.3 -> red fill, press OK to close. "You can also apply a more complex formula, for example

471.3 -> you can compare the value in cell A2 with the sum of values in cells B2 and C2, or use

478.74 -> and/or operators for multiple conditions." Okay, we're just going to go with the simple one.

484.86 -> We're going to go with this one and see if we can get it to work. So let's go to Excel here,

490.8 -> then we're going to highlight these cells. It said go to Home > Conditional Formatting > New Rule.

498.273 -> Use a formula, we're going to paste this in, and format the cell to have a red fill, click on

506.88 -> OK and OK. Now let's check this out. We're going to go above 10 here, looks great. Let's copy these down,

516.6 -> and I'm just going to add some random numbers to these, that's amazing; that works. Next question is

523.38 -> about nested "IF" functions that we all struggle with. So this comment is from Free Pilot which

528.3 -> was under the "IF" formula video and they asked "'VFR' if ceiling is greater than 3000,"

534.48 -> I think these should be zeros, "and visibility less than 5 miles, but 'MVFR' if ceiling is between

540 -> this, and/or visibility is this, but this." So just reading this can make us a bit dizzy. Now, to be

546 -> able to solve this, we will have to break it down into simple steps. Let's see if ChatGPT is able to

552.48 -> break it down faster than we can to be able to generate the nested "IF" function. So I'm just

558 -> going to copy this, let's bring up the browser and paste this in ChatGPT and see what we get. Okay,

563.88 -> so it looks like you're trying to build a system for classifying weather conditions based on the

568.44 -> ceiling cloud cover and visibility. I had no idea that's what it was about. And now, it's generating

573.24 -> us the code in Python. Yeah, we never told it which language or which application we need

578.7 -> the code for. So let's see if we can specify that, and let's see if it can turn this into

584.52 -> Excel. I'm just going to put in make this into an Excel formula. Here is how you could create an

590.28 -> Excel formula to classify weather conditions based on cloud cover and visibility. Okay,

596.16 -> so it's going on and giving us quite a detailed explanation of what it's doing there, but for now,

602.7 -> I'm just going to ignore that. I just want to copy this formula and see if it works. Okay,

608.1 -> so let's copy code and bring up Excel. I already have some sample data added here. We're going to

613.68 -> test the function on the C column. Now, because the formula that GPT wrote us was referencing A1

619.92 -> and B1, I'm going to paste it in C1 so that we can drag this down. And now, let's check if we get the

626.28 -> right result. So, "VFR" if ceiling is greater than 3000, it is, it's 3100, and visibility is less than 5

634.08 -> miles, it's 2, we get 'VFR". "MVFR," so we have "MVFR" here. If ceiling is between 1000 - 3000,

641.4 -> it's 1200, that's true, and/or visibility is three to five miles,

646.98 -> so we have four. But here's the thing, the condition that's being asked is and/or. In Excel,

653.4 -> we either have an AND condition or an OR condition, and in this case, I think it makes

658.62 -> sense to have an AND condition. So, both of these conditions have to be true for us to get MVFR,

664.98 -> and that's what ChatGPT has designed as well. It's written in an AND condition for it. Now, for these

672 -> last two, I just get "IFR". I'm not seeing "LIFR". So, what's the condition for that? Well, "LIFR" if

679.38 -> ceiling is less than 500, it is, it's 400 here. And/or, so let's assume AND, visibility is less than one,

686.16 -> it is, but I'm not getting "LIFR", I'm getting "IFR", why? Well, take a close look at the formula here.

694.5 -> That last condition never gets executed. Why? Because, whenever we have a value less than 500,

703.86 -> it's also less than 1000, and whenever we have a value less than one, it's also less than three.

709.5 -> So, the formula reaches this part, the condition is met, and it leaves the formula. It never comes

717.6 -> here. So, what's the solution? Well, we just have to put this condition before the other one. So,

724.14 -> I'm just going to cut this one, go carefully before this condition, and paste the other one in.

731.16 -> And now, let's see if we get the correct answer. We get "LIFR" here. Now, I'm not 100% sure if that's

738.24 -> what Free Pilot wanted to see, but with a little bit of tweaking, we can get this to work. This

744.66 -> last one is a common question from our students. We want to create a VBA code that creates a PDF

750.96 -> report for the tab that we select here. So, if Productivity is selected in this cell,

757.02 -> I want the macro to go to the tab Productivity, generate a PDF file, and save it in the same place

763.92 -> that this Excel workbook is saved in. And if I switch this to Game and run the macro,

770.22 -> I want it to go to the Game tab and generate a PDF and save it to the same location as this file. How

776.58 -> do we do that? Let's see what ChatGPT thinks. We just have to make sure that we give ChatGPT proper

782.52 -> instructions. So, I've typed in here: "Write a VBA Excel macro that looks at the value in cell C2 and

788.82 -> finds the worksheet with the exact name. Then create a PDF and save it to the same location

793.62 -> as this Excel file is saved in." So, let's see what we get. Okay, so here's an example of a VBA

800.1 -> macro that does what you described and it's gone ahead and created the code for us. And it's even

806.94 -> adding a message box at the end to inform us that the PDF was actually saved. So, it also tells us

813.54 -> that we can run the macro by opening the Visual Basic Editor and pasting the code in there. Now,

818.64 -> if you don't know how to do this, you can ask it for proper instructions. In this case,

822.96 -> I'm just going to go ahead and copy the code and let's go to Excel and see what we get. Let's first

828.96 -> bring up the Visual Basic editor. So, you can use the shortcut key ALT + F11 or I'm just going to

834 -> right-mouse-click on a tab and select View Code. This is going to bring up the Visual Basic Editor.

839.7 -> So, let's insert a new module. Just going to go to this drop-down, insert a module. Now, let's just

845.16 -> paste in the code that we got. The syntax looks great. We could either directly run it from here

850.98 -> or we can attach it to a button or a shape. I want to attach this to a shape. Notice also, the moment

857.4 -> I added my Visual Basic code, it tells me that these projects must be saved in macro-enabled

863.16 -> workbooks. To resume, autosave, save, save to a macro-enabled file type. So, we have to save

868.08 -> this file as an ".xlsm" file. Now, I want to add a shape to attach my macro to. Let's go to Insert,

875.28 -> Illustrations, Shapes. I'm just going to go with the square. Add this in. Let's assign the macro,

882.12 -> Create PDF. Click on OK and let's just quickly give it a name. Okay, so now let's see if it

889.32 -> works. I have game selected. So, this tab should be saved as a PDF document in the same directory

896.46 -> as this Excel file is saved in. Let's run it. PDF saved as Game.pdf in the same location as

903.84 -> the Excel file. Click on OK. Is that true? Game is right here. Let's open it up and it's there.

912.42 -> Now, let's switch this to productivity. Create PDF, Productivity.pdf in the same location and

921.06 -> Productivity.pdf is right here. Isn't that amazing that we got ChatGPT to write a VBA Macro for us?

929.88 -> Okay, so to wrap up, GPT did a great job in helping us out with our Excel questions. But,

935.04 -> you have to be cautious. I've noticed that there are one or two things that can be a bit off with

940.8 -> the answers that we get. So, for example here for our VBA code, it tells us make sure that you have

946.02 -> the add-in for Microsoft Office installed in your machine for this to work. But, what does it mean

951.42 -> by add-in for Microsoft Office? We don't need that. So, sometimes you get these fishy stuff

956.64 -> in the middle of the answers, and sometimes you can get completely wrong answers. For example

962.04 -> here, this is another comment from YouTube. Someone asked, "I have a question about the

966.96 -> #SPILL error. I'm taking error when I'm using index or unique or filter formulas in the table. Where

973.02 -> do I fail? Can't we use these formulas in the table." And ChatGPT says that the #SPILL error

978 -> can occur when using certain formulas in a table if the formula is trying to return a result that

982.8 -> is larger than the number of rows in a table. So, in a way it is correct that it can occur in the

988.62 -> table and it does. But, this part is wrong. It returns the #SPILL error if the range is bigger

994.62 -> than a single cell, not bigger than the number of rows in the table. Okay, so you just have to

1000.98 -> use it with care. Now, am I worried whether chat gbt is going to replace me? Honestly,

1006.14 -> I'm super excited about these developments. I don't think AI is going away. We just have

1010.7 -> to find a way to make it work for us. I'm a teacher, so I'm happy that ChatGPT can help

1015.68 -> people getting unstuck. So, if you're stuck on a formula, it can help you figure out the problem.

1020.84 -> But it can't create, at least not yet, a Power BI hands-on course from scratch. Right? So, professions

1027.14 -> are constantly changing with technology, we just have to adjust the way that we bring value into

1033.32 -> this world. Let me know in the comments what you think about this and how you think it might affect

1038.24 -> your job. Thank you for being here, thank you for watching, and I'm going to see you in the next video.

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