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