Automate Excel Using ChatGPT (SURPRISING results)

in this video we're going to test chat GPT from  asking it to write some basic formulas all the way   to creating full-on code to automate tasks using  macros let's get into it so here's the Excel file   that we'll be working with which you can download  for free in the video description and as you can   see we have this database here with the brands and  how much we celebrate of each and specifically we   want to find out all of the unique Brands so all  of the unique ones that we have in column e and   the instructions to give to chat GPT are going to  be the following on Excel use a formula to find   each unique value from cell E3 to e 251 which is  basically this whole column area over here so for   this we're just going to copy the instructions  so Ctrl C and then we'll go to chart GPT   and in here in case you don't know what this  is this is basically a free tool that you can   use you just need to sign up for it and using  artificial intelligence it's able to generate   answers to any questions that you might have in  text format so let's give it a try here we're   just going to Ctrl V to paste it hit enter and  just wait for it to generate here so let's take   a look at the answer it's generated for us here  and it's proposing we use the unique formula which   is probably the same one that I would have used  and more specifically it also gives you the cell   reference that you're referring to so from here  all we would need to do is actually just copy it   so just Ctrl C there and you'll notice down below  that it also offers you another alternative if   you want but we're happy with this first one as  it's a lot simpler from here we're just gonna   go over back to the Excel file and just Ctrl V  to paste it and you can see that it's able to   find all of the different brands that we had  from this data set one thing to keep in mind   here is that chat GPT doesn't always generate the  same answer even if you type in the exact exact   same things as me you might get a different  formula or a different type of text as well   you're probably thinking Kenji this is such an  easy example but don't worry we'll get into some   Hardware stuff now and over here you can see  that we have this balance sheet and you don't   necessarily need to know anything about this  just that these figures here so these ones in   row nine should be equal to these ones in row 21.  so to do that we'll check over here we'll make it   check that basically says that here you can see  the instructions if C9 matches this figure over   here then the check should just say okay and  if not you should to give us an error design   just to make sure so under the instructions  here it's copy these by hitting Ctrl C again   we'll go to the chat and again we'll paste it over  here and hit enter and wait for it to generate   let's take a look at the answer that it's written  over here as you can see it's suggesting the if   formula and this is the full syntax that I want  so if C9 equals c21 okay and if not show error   that seems to make sense it also shows another  alternative using conditional formatting here but   we're happy with just this so we'll copy it Ctrl C  and let's paste it over here so let's say we paste   it down where the check is so Ctrl V you can see  that it's showing OK meaning that these two are   matching indeed if I change this say I put a 5  here it should give me an error as that seems to   be the case from there we can also copy and just  drag it along and paste it there like so it should   all be okay if they are matching so that seems  to make sense there now what if we give it some   harder stuff that's not a formula that it can just  type up for example what if we want a drop down   list so let's take a look by going to control  page down here you can see that we have the   customer name on one side and specifically we want  create a drop down list with the values on Cell C3   to C11 so basically with all of these here we just  want to create a drop down over here so let's copy   the instructions Ctrl C and again we'll go back to  chat GPT and just paste it over here and hit enter   so here it's suggesting that we use the data  validation feature and here's all the steps   that it's asking us to do now this is probably  easier done with a video demo or some kind of a   tutorial as it's not that easy to follow these set  of steps but regardless we'll take a screenshot of   them and try to reference them as we do it over  in Excel so first we need to go where we want   the drop down to appear which in this case it's  over here under H3 then we need to go under data   under data validation which in case you don't  know where it is it's probably quite difficult   to find but it's basically this area over here so  just click on that and then in this scenario we   want to have a list so let's click on list there  and the source this is where we want the list to   be coming out from so in this case it's going to  be generated based on all of the first names that   we've got over here because that's the C3 to C11  basically then we're just going to hit OK and just   like that you should be able to have a drop down  as you can see in this scenario it's not all that   useful to just to use chat GPT that's typically  the case with anything that requires some visuals   or some complex Financial modeling and things  like that that's where learning Excel properly   is probably recommended and speaking of learning  Excel properly if that's something of interest   to you you can consider checking out our Excel  for business and finance course what we teach   everything we know about Excel and what makes this  course different is that it's all applied to the   real world while we still cover the theoretical  lessons like formatting formulas and charts we   also offer case studies that simulate the type  of work you might be assigned in your day-to-day   ranging from Financial modeling to cleaning a data  set and presenting some visual insights and if   you get stuck along the way you can easily ask us  questions in the discussions forum we also offer   several other courses including PowerPoint Finance  evaluation and more so if you're interested in   checking it out you can go to a link in the  description below alright back to the video Let's   now go for something even more ambitious on chat  GPT which is recording a macro and in case you   don't know a macro is basically recording a set  of steps so that you can automate them for future   use so over here if you go to control page Zone  you can see in example four and the first thing   we're gonna need to do is to save the file as a  macro enabled workbook for this just go to F12   that's where the save as pop-up should show up so  like I mentioned instead of saving like an Excel   workbook we're gonna click on that and go to excel  macro enabled workbook as we're gonna just record   the email Pro in here so we'll hit save there and  let's look at the data set over here we basically   have the weekly sales by country and by day here  and we want a set of different things so first   we're going to want to sum the weekly total  so it's all of these figures here we're going   to want to sum them same we're going to want to  average just below that and then for the highest   day as you can see the instructions here we want  the maximum so which one's the highest day but we   want to return the matching value meaning that we  actually want to see the the day not the actual   number and then the last one to test a bit of  formatting we're seeing to highlight all of these   three cells in Orange so it's really quite a few  set of steps so let's try and see if charge gbt   can do this for us so we'll copy all of these Ctrl  shift down Ctrl C and let's head over to chat GPT   we'll paste it down over here and hit enter and  wait for it to load let's take a closer look at   the answer over here and it says that you need to  use Visual Basic for applications program language   it doesn't really give you much of an explanation  on what VBA is but don't worry we'll get to that   later as I'll give you some context basically  this is all the code that we we're going to   want to copy and paste then towards the bottom  it's going to give you the instructions on going   ahead and opening up the VBA editor and where to  page things Etc so let's go ahead and give it a   try by copying the code here just hit copy code  then we'll go back to excel once on Excel to open   up VBA it's set to use the ALT f11 key so let's  do that and you can see that it's opened it up   here but it's not all that clear where you need  to actually paste any of this so this is where   I'm just gonna help you out a bit but basically we  need to go to insert then we're gonna go to module   and from here this is where we want to paste it  so we're just going to paste all of the code here   then once we've done that we want to save it  and again this is not really something that's   that's been explained but anyhow we're gonna  save it and then let's just close out of that   and so now when we look down over here we gotta go  to the developer tab to run the macro then under   macros we should have it saved so this is the one  that we just saved here under macro name and we're   just gonna hit on run as you can see over here we  have all this area in Orange let's go ahead and   double click here so we can see it better so the  weekly total let's see if that's the same amount   we're just gonna go ahead and run that ourselves  to test it out so it's this area here some it's   exactly the same so that seems to be working well  for the average it's the average formula here and   we'll select it that seems to be working well as  well and the highest day it does seem to be that   Wednesday so that's all good I will say that the  sales what I had in mind was to only have these   three in Orange not the ones in between I feel  like that's what I highlighted here with c16 18   and 20 but maybe it didn't quite catch some part  let me know in the comments what you think if this   was good enough or it still needs some work if  you feel like chat GPT still need some work I will   say that Excel does have a built-in AI feature  which most people don't actually know about so   let's test it out you can see that we have this  data data table and towards the side we have the   analyze data ribbon so this area this button is  the one that we're going to want to click on so   we'll hit click on that and you can see that it's  going to start to load a lot of different insights   for example a pretty useful one is a total sales  by city as you can see that's not something we   have right now but it's probably a useful thing to  know so we would just click on insert pivot chart   from here under a new sheet it's gonna  load it up for us one more thing if we   go back let's suppose that we're not quite  liking any of these suggestions and so we   want to ask it something else we can actually  do that up over here so for example let's say   we want to find out how many sales Coca-Cola  had so we'll just type that how many sales   does cocoa cola have hit enter there and you can  see that it's able to calculate all of that using   a pivot table and you just gotta insert it if  you want to have it somewhere for more on Excel   check out this video over here to learn about  macros with this link over here to check out   our chords hit that like and that subscribe if  you liked it and I'll catch you in the next one

Channel: Kenji Explains

Views: 212,161

Keywords: excel & chatGPT, chatGPT for excel, use chatGPT for excel, chatGPT excel application, chatGPT for excel formulas, chatGPT for excel macros, chatGPT for VBA code, learn excel using chatGPT, can chatGPT for for excel, testing chatGPT for excel, can chatGPT automate excel?, chatGPT for microsoft excel, automate excel with chat GPT, if statement in chatGPT, chat GPT on excel, master excel with chat GPT, excel test chat GPT, Excel AI, Excel artificial intelligence, AI for excel

Id: qB4_2_YX9uE

Length: 11min 0sec (660 seconds)

Published: Wed Jan 11 2023