How ChatGPT Can Help With Your Complex Excel Spreadsheets

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
There have been some really cool developments in  AI recently, especially with opening up ChatGPT   to the public. This made me think: How could  we use ChatGPT when we're stuck writing an   Excel formula? Let me show you some really cool  examples. Okay, so what I'm going to do is take   some of the questions you've asked me and see if  ChatGPT can give you the correct answer. In case   you're not familiar with ChatGPT is, it's basically  an AI chatbot. You can ask it questions and it   answers. It's currently free. All you need to do  is go to OpenAI.com. You can scroll down here,   go to ChatGPT, and then try ChatGPT or just go  to this URL directly. As you can see, there is   currently high demand for this because it's such  an impressive tool and it's super easy to use.   All you need to do is type in your question here  and it's going to answer it for you. Let's take a   look at our first question. Here's a comment from  Steel Graves underneath this video and he asked,   "I have a question and hoping you have a way to  do this easily. Is there a formula for calculating   the number of words in an Excel sheet? I see VBA  but looking for something simpler. Hoping for your   wiz brain." Well, it's not going to be my brain,  it's going to be ChatGPT's, so let's see what   ChatGPT has to say to this. I'm just going to copy  this part of the question, Ctrl + C, let's go to   ChatGPT and see what it has to say. Okay, I'm just  going to paste it in and see what we get. Okay,   since "there is no built-in formula in Excel that  you can use to calculate the number of words in   a sheet, however you can use the LEN() function to  calculate the number of characters in a cell and   then divide the number by an average number per  word to estimate the number of words." That's not   really what I want to do. Now, it gets interesting.  You can also use SUBSTITUTE to replace spaces   with nothing. Then use the LEN function to count  the number of letters and then add 1 to get the   number of words. Okay, so assuming that you want  to check the number of words in cell A1. Okay,   so now, it went back to a single cell. I don't  want just a single cell. Okay, so alternatively   you can use VBA or macros to do the same. Now you  also have to keep in mind that every time you ask   it a question, it can be the exact same question,  it can give you different answers. Look, it's   going ahead and writing a VBA code even though we  actually said we didn't want that. We just wanted   something simpler. You have defined the function,  you can call it by entering the formula. Oh,   it's written a function in VBA, that's also great.  But yeah, I didn't want the VBA version, I want   the formula version. So let's see if I can tell  it exactly what I want. I'd like to go with the   formula version, update it to apply to range A1 to  C100 and exclude blank cells. Those ones shouldn't   be counted. Okay, you can use the following  formula to estimate the number of words in a   range of cells while excluding blank cells. Okay,  this formula uses the SUMPRODUCT function...[reading] ...and divide by 5 to estimate the number of words.  You don't want any estimation, we actually want   to get the number of words. This formula uses  the same idea of replacing spaces with nothing   and counting the number of letters and adding one  to get the number of words, but this time it also   makes sure that only non-black cells are counted  by using this. Okay. This seems good, let's just   test it out. I'm going to copy it, let's go to  Excel. I'm going to paste it in here, okay, so   A1 to C100. Right now I get 0, there's nothing  in there. So let's... Five words. That looks great.   That seems to work. And this formula is valid for  older Excel versions as well, and you can update   it as you need to depending on your range. Now I  try to get ChatGPT to rewrite this using the new   Office 365 function like the TEXTSPLIT function,  and it tried. We had a few back and forth so it   even recognizes that TEXTSPLIT is a newer function  that was introduced in Excel 365. So if you're   using an earlier version, it's not going to work.  But the problem was that this function wasn't   working, so we had a bunch of back and forth  and none of them worked. You just have to keep   in mind though that ChatGPT doesn't have access to  the internet and it only has limited information   after 2021. But if it knows the TEXTSPLIT function, it  must know the TEXTJOIN function because that got   introduced before. But it wasn't able, at least  in this iteration that we were going through, to   combine these functions to give me the result that  I wanted, so I ended up writing my own version.   In case you're interested, it's this one here. We  can test it out, if I just add other texts, it is both   versions give us the same results. It's just much  easier and simpler to write the second version, of   course, only if you have Office 365 or Excel 2021.  But the great thing is that the first version it   gave me did work. Now let's take a look at another  question. This comment is from Cary, and she   asks "I've been trying to use data validation and  cells that have formulas in them, but it seems   like it doesn't work even in the latest Excel  versions. For example, cell A2 contains formula   B2 plus C2. If I set the validation on cell A2  saying that the sum shouldn't be greater than 10,   any number could still be put into cells B2 and  C2, and A2's error message won't be triggered   even if their sum is greater than 10." And yes,  that's true. Data validation doesn't work on   cells that are driven by formulas. It works on  input cells. Now let's see what ChatGPT has to   say about this. Okay, so I'm just going to paste  in the comment and I'm going to remove this part,   "just wondering if you already have a video for  this," and let's see what ChatGPT has to say:   "Excel's data validation feature is intended to  validate the data that a user enters into a cell,   rather than the results of a formula." It's smart; it knows that. "Because the result of formula depends on the   values in other cells, the data validation rule  is applied to the formulas input cells." Okay.   "To validate the results of a formula, you can  use a conditional formatting rule that checks   the result of the formula and highlights the cell  if it does not meet the criteria. For example,   you could create conditional formatting that  checks if the value in cell A2 is greater than   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   sum to exceed 10, the cell will be highlighted  in red indicating an error." That's great.   It says, "You can also use custom VBA script for  this. Alternatively, if you have advanced formulas   in complexity, you might consider using Excel  add-ons." Okay, so we have different suggestions,   but one suggestion would also be to add an "IF"  statement right to check if A2 is greater than 10,   then it should return error, otherwise valid. Now  let's say we are interested in the "Conditional   Formatting" method, but we don't know how to go  about this. So let's see if ChatGPT can tell us.   Okay, "To set up conditional formatting for  a range of cells in Excel, use the "New Rule"   option. Here's an example of how to set up a rule  that applies a red fill color if the cell value   is greater than 10." Okay, so first we need  to select it, go to "Conditional Formatting",   select "New Rule", use the formula, and use  this function, and then format it to have a   red fill, press OK to close. "You can also  apply a more complex formula, for example   you can compare the value in cell A2 with  the sum of values in cells B2 and C2, or use   and/or operators for multiple conditions."  Okay, we're just going to go with the simple one.   We're going to go with this one and see if we  can get it to work. So let's go to Excel here,   then we're going to highlight these cells. It said  go to Home > Conditional Formatting > New Rule. Use a formula, we're going to paste this in, and  format the cell to have a red fill, click on   OK and OK. Now let's check this out. We're going  to go above 10 here, looks great. Let's copy these down,   and I'm just going to add some random numbers to  these, that's amazing; that works. Next question is   about nested "IF" functions that we all struggle  with. So this comment is from Free Pilot which   was under the "IF" formula video and they asked  "'VFR' if ceiling is greater than 3000,"   I think these should be zeros, "and visibility less  than 5 miles, but 'MVFR' if ceiling is between   this, and/or visibility is this, but this." So just  reading this can make us a bit dizzy. Now, to be   able to solve this, we will have to break it down  into simple steps. Let's see if ChatGPT is able to   break it down faster than we can to be able to  generate the nested "IF" function. So I'm just   going to copy this, let's bring up the browser and  paste this in ChatGPT and see what we get. Okay,   so it looks like you're trying to build a system  for classifying weather conditions based on the   ceiling cloud cover and visibility. I had no idea  that's what it was about. And now, it's generating   us the code in Python. Yeah, we never told it  which language or which application we need   the code for. So let's see if we can specify  that, and let's see if it can turn this into   Excel. I'm just going to put in make this into  an Excel formula. Here is how you could create an   Excel formula to classify weather conditions  based on cloud cover and visibility. Okay,   so it's going on and giving us quite a detailed  explanation of what it's doing there, but for now,   I'm just going to ignore that. I just want to  copy this formula and see if it works. Okay,   so let's copy code and bring up Excel. I already  have some sample data added here. We're going to   test the function on the C column. Now, because  the formula that GPT wrote us was referencing A1   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   right result. So, "VFR" if ceiling is greater than  3000, it is, it's 3100, and visibility is less than 5   miles, it's 2, we get 'VFR". "MVFR," so we have "MVFR"  here. If ceiling is between 1000 - 3000,   it's 1200, that's true,  and/or visibility is three to five miles,   so we have four. But here's the thing, the  condition that's being asked is and/or. In Excel,   we either have an AND condition or an OR condition, and in this case, I think it makes   sense to have an AND condition. So, both of these  conditions have to be true for us to get MVFR,   and that's what ChatGPT has designed as well. It's  written in an AND condition for it. Now, for these   last two, I just get "IFR". I'm not seeing "LIFR".  So, what's the condition for that? Well, "LIFR" if   ceiling is less than 500, it is, it's 400 here. And/or,  so let's assume AND, visibility is less than one,   it is, but I'm not getting "LIFR", I'm getting "IFR",  why? Well, take a close look at the formula here.   That last condition never gets executed. Why?  Because, whenever we have a value less than 500,   it's also less than 1000, and whenever we have a  value less than one, it's also less than three.   So, the formula reaches this part, the condition  is met, and it leaves the formula. It never comes   here. So, what's the solution? Well, we just have  to put this condition before the other one. So,   I'm just going to cut this one, go carefully  before this condition, and paste the other one in.   And now, let's see if we get the correct answer.  We get "LIFR" here. Now, I'm not 100% sure if that's   what Free Pilot wanted to see, but with a little  bit of tweaking, we can get this to work. This   last one is a common question from our students.  We want to create a VBA code that creates a PDF   report for the tab that we select here. So,  if Productivity is selected in this cell,   I want the macro to go to the tab Productivity,  generate a PDF file, and save it in the same place   that this Excel workbook is saved in. And  if I switch this to Game and run the macro,   I want it to go to the Game tab and generate a PDF  and save it to the same location as this file. How   do we do that? Let's see what ChatGPT thinks. We  just have to make sure that we give ChatGPT proper   instructions. So, I've typed in here: "Write a VBA  Excel macro that looks at the value in cell C2 and   finds the worksheet with the exact name. Then  create a PDF and save it to the same location   as this Excel file is saved in." So, let's see  what we get. Okay, so here's an example of a VBA   macro that does what you described and it's gone  ahead and created the code for us. And it's even   adding a message box at the end to inform us that  the PDF was actually saved. So, it also tells us   that we can run the macro by opening the Visual  Basic Editor and pasting the code in there. Now,   if you don't know how to do this, you can  ask it for proper instructions. In this case,   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   bring up the Visual Basic editor. So, you can use  the shortcut key ALT + F11 or I'm just going to   right-mouse-click on a tab and select View Code.  This is going to bring up the Visual Basic Editor.   So, let's insert a new module. Just going to go to  this drop-down, insert a module. Now, let's just   paste in the code that we got. The syntax looks  great. We could either directly run it from here   or we can attach it to a button or a shape. I want  to attach this to a shape. Notice also, the moment   I added my Visual Basic code, it tells me that  these projects must be saved in macro-enabled   workbooks. To resume, autosave, save, save to  a macro-enabled file type. So, we have to save   this file as an ".xlsm" file. Now, I want to add  a shape to attach my macro to. Let's go to Insert,   Illustrations, Shapes. I'm just going to go with  the square. Add this in. Let's assign the macro,   Create PDF. Click on OK and let's just quickly  give it a name. Okay, so now let's see if it   works. I have game selected. So, this tab should  be saved as a PDF document in the same directory   as this Excel file is saved in. Let's run it.  PDF saved as Game.pdf in the same location as   the Excel file. Click on OK. Is that true? Game  is right here. Let's open it up and it's there.   Now, let's switch this to productivity. Create  PDF, Productivity.pdf in the same location and   Productivity.pdf is right here. Isn't that amazing  that we got ChatGPT to write a VBA Macro for us?   Okay, so to wrap up, GPT did a great job in  helping us out with our Excel questions. But,   you have to be cautious. I've noticed that there  are one or two things that can be a bit off with   the answers that we get. So, for example here for  our VBA code, it tells us make sure that you have   the add-in for Microsoft Office installed in your  machine for this to work. But, what does it mean   by add-in for Microsoft Office? We don't need  that. So, sometimes you get these fishy stuff   in the middle of the answers, and sometimes you  can get completely wrong answers. For example  here, this is another comment from YouTube.  Someone asked, "I have a question about the   #SPILL error. I'm taking error when I'm using index  or unique or filter formulas in the table. Where   do I fail? Can't we use these formulas in the  table." And ChatGPT says that the #SPILL error   can occur when using certain formulas in a table  if the formula is trying to return a result that   is larger than the number of rows in a table. So,  in a way it is correct that it can occur in the   table and it does. But, this part is wrong. It  returns the #SPILL error if the range is bigger   than a single cell, not bigger than the number  of rows in the table. Okay, so you just have to   use it with care. Now, am I worried whether  chat gbt is going to replace me? Honestly,   I'm super excited about these developments.  I don't think AI is going away. We just have   to find a way to make it work for us. I'm  a teacher, so I'm happy that ChatGPT can help   people getting unstuck. So, if you're stuck on a  formula, it can help you figure out the problem.  But it can't create, at least not yet, a Power BI  hands-on course from scratch. Right? So, professions   are constantly changing with technology, we just  have to adjust the way that we bring value into   this world. Let me know in the comments what you  think about this and how you think it might affect   your job. Thank you for being here, thank you for  watching, and I'm going to see you in the next video.
Info
Channel: Leila Gharani
Views: 565,093
Rating: undefined out of 5
Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, chatgpt, chat gpt excel, A.i., AI, chat gpt, solve excel problem, excel formula not working, excel returns error, get answers, how to use chatgpt, what is chatgpt, openai
Id: 21bXQDXSxYs
Channel Id: undefined
Length: 17min 34sec (1054 seconds)
Published: Thu Jan 12 2023
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.