welcome to the one video that will take you from excel formulas newbie to excel formulas wizard
and for this we'll go over 10 of the most useful formulas and rank them in terms of difficulty all
the way from 1 stars to 5 stars so let's get into it starting with level 1 first up we have
the sequence formula which as the name kind of suggests allows you to generate a list of
sequential numbers for instance in this excel file which you can download in the description below
you can see that we have a list of 100 new employees over here and so we want to put
a sequence that's going to go from 1 to 100 so we'll go equals sequence is the formula for that
press the tab key there and the rows we're just going to type 100 close those brackets and hit
enter if you go to control down arrow all the way to the bottom you'll see that you created a list
of 100 right there now you might think that this formula is quite simple and there's not much to it
but there is a slightly more advanced version of it for instance suppose over here under id number
ashley here is employee number 500 so for this we can go to equals
sequence again press the top key and now for the rows we still want a hundred but this time around
under columns we'll just put one press the comma again but we want to start at number 500
because there's already 499 employees in the company say then you can close those brackets and
hit enter and now that's where you start next up in level 1 we have string functions like the right
the mid or the left and you're probably wondering what i'm talking about so let me show you on excel
here's the scenario under the second tab you can see that we have a small table with the store id
followed by the products that are going to that store alongside their contact information over
here and so we want to find out the calling code which essentially is a plus 34 area alongside the
country code which for madrid would be the es here so first we'll go left function press the tab key
the text is going to be this area over here that we want and we want the first three
characters close those brackets and hit enter there from here you'll go all the way back to
the first one and press the shift down arrow all the way to the bottom here and go ctrl d
that's gonna drag it all the way down for us same concept with a country code and you probably
guessed it it's the right hand side now press the top key there we'll go location as madrid press
the comma and the number of characters going from the right side we want two characters close those
brackets and hit enter and again shift down arrow all the way to the bottom here and press ctrl d
all right moving on to level two and here we're going to be looking at date functions so functions
like today weekday and week number and here's the scenario so we've got all of these products over
here that are coming on a specific date now we want to make sure that this date isn't a
sunday because we don't work on sundays in the warehouses and so to make sure of that you can
either go one by one and try to see whether that's going to be a sunday look it up on google maybe
but that's obviously quite a tedious process and instead what we can use is the weekday function
so we'll go equals weekly press the tab key there and so the date that we're interested in
is this one comma and here we're going to be able to specify hey do you want the sunday as the first
day or do you want the sunday as say day seven in our case let's say we go for the conventional
monday through sunday so you'll press the two layer close those brackets and hit enter from
here you can either press the ctrl d like before or go over here to the side and double click on it
now you can start to see that hey this is a sunday so we should probably reschedule it same kind of
concept goes for the week number suppose we don't work in a particular week in the year
we'll go equals weeknum press the tab key same serial number comma and the return return type
let's go for two again so we stay consistent and hit enter and so now it's the same concept there
and lastly it'd be useful to know the time to arrival so essentially which of these
we have to prioritize because we see there's quite a lot that are going to come on a sunday
but we don't want that and so we need to find out how to prioritize them
for this we can we can actually use the today function and see how far it is from today
that they're gonna be arriving so we'll go equals we'll select the arrival date first minus today
close those brackets and hit enter and so we know now that this is gonna arrive 157 days from today
if you drag this all the way down you're gonna start to see that hey maybe we should prioritize
this very last one that's actually coming in 14 days so we should probably contact them first
next up in level two we have the if statement which basically says that if a certain condition
is true then you should do one thing and if it's false you should do something else so for example
if you like this video you should like it and if you don't like it you should comment down below
why you don't like it jokes aside let's look at a real life scenario here so we've got a
company's balance sheet and in case you don't know anything about accounting all you should
know is that the total assets so this yellow line over here should equal the total liabilities and
shareholders equity line down over here now to make sure of that we can use an if statement
so we'll go equals if press the tab key there the logical test for us is that this figure over here
equals this up figure up here press the comma key there and the value is true so what should
happen if that is indeed the case we'll put in brackets and okay sign close those brackets
and then put the comma sign there the quotations i meant there sorry and then the value if false
so if that's not the case then we should put something in quotations like error
then close those quotations close the brackets and then hit enter there so the first one says
it's okay we'll just drag that across like so and suppose i change this figure to say i5 just
to see what happens you can see that that's gonna change to an error sign there continuing on with
if statements in level three but this time they're gonna be slightly more complex because they're
actually gonna be two functions in one a sum which is obviously an addition and an if put together
for this we have this data set over here where we have the iphone as the product and we have
the respective quantities for it as well as the year it was sold and say so we have the
2022 iphone sales that we want to find out here but you'll notice that we have 22 2022 over here
we have a bit more of that over here somewhere over here and so we want to find a formula that's
going to be able to sum all of them together so we'll go equals some if press the top key there
and the range here is going to be basically all of this years over here press the ctrl
shift down arrow to get to bottom comma the criteria is that we want this range to equal
to the 2022 number press the comma key and the sum range is the area that we want it to sum
for us that's going to be the quantity sold ctrl shift down arrow to get to the bottom close those
brackets and hit enter there and now you can see that we should have the whole sum over here
and if you're watching these formulas and wondering how you can apply them to the real world
i recommend checking out financial edge which provides certified online finance courses
and is also sponsoring this video they're the instructors that teach the new hires
at the top four investment banks across the globe so you can get the exact same training online
at your own pace their best-selling investment banker course goes over all the relevant skills
you need to succeed in an analyst role these include financial accounting financial modelling
valuation and more and if you're interested in other areas of finance they've also got courses
on asset management private equity trading etc so if you're interested in checking them out
go to a link in the description below and use code kenji25 to get 25 of their courses
all right back to excel a slightly more complex version of a sumif is a sum ifs so with multiple
if conditions for this go to control page down and here you can see that we have
all our budget where we have our income alongside our expenses with the description and the amount
for it and suppose we're looking specifically at the month of april and we want to find out the
base salary alongside all of the other expenses as well for this a normal sum if it's not quite gonna
work because we have both a condition where we need to fulfill the month so we need it to be the
month of april and we need to fulfill the category as we want it to be the category over here
that's why we're going to use a sumifs for this we'll go equals sum ifs press the tab key the sum
range for us is going to be all of the amounts so go to control shift down arrow all the way to the
bottom there and now we're gonna do something a bit special which is pressing the f4 key
the reason we want that you can see there's some dollar signs here it's because it's
gonna lock this range meaning that when we copy this formula down all the way here
the amount column here that we have selected is not going to be moving down as that wouldn't be
quite accurate so then we're going to press the comma key the criteria range is that we
need it to be in the month in this area so all of these months is going to be the range for us
press the f4 key there comma then the criteria number one is that it's in the month of april
we'll press the f4 again so it doesn't go down comma the criteria range number two is all the
categories so we'll select these areas here ctrl shift down press the f4 key comma and the criteria
2 is going to be that it equals to the base salary this one we don't have to press the f4 key because
it's fine for us to be dragging it down because we need it to move down as well close those brackets
and hit enter from here we'll copy and drag it down over here and same thing down below ctrl v
moving on to level four and make sure you stick around until the end for two bonus tips as well
here we're going to start with a vlookup which allows you to look up data within a vertical table
so here's the scenario we're working with we're looking at a data set of coca-cola products here
alongside their warehouses and how much quantity there is for each and we'd like to know how much
minute maid we have left and how much of all these other brands we have left as well so for
this we'll go equals vlookup press the top key there the lookup value for us is the minute made
press the comma key the table array is gonna be this whole range over here so we'll press ctrl
shift down ctrl shift right and then we'll press the f4 key to lock that one press the comma key
the column index number and this is a part that gets people confused it's basically vlookup starts
from the left hand side and then goes towards the right so we need to count the number of columns
we need to find in order to get to the quantity so for us it's going to be product as number one
number two number three the quantity which is the one we want as the output is gonna be the
fourth one so we'll put a four there press the comma and then for here we're just gonna go false
because we want exact match close those brackets and hit enter and just to make sure here we see
that minute maid has a quantity of ten thousand five hundred and fifty if you go down uh right
over here under table you can see that that's the exact same amount so we know that's right
from here we'll drag this down just double click there but you'll notice here that the very last
one powerade is giving us this error sign we're not entirely sure why if you press the f2 key
it seems like the formula is working fine and so you'll notice that if you go under the products
the powerade actually isn't there and so that's why it's giving us this error sign and that brings
us to the second part of level four where we'll be looking at the if error function which essentially
allows you to replace an arrow key with something else so that it's easy for people to understand
and they don't think that the model is broken or something so we'll go inside this poweraid cell
by pressing the f2 key go to the very front of it and we'll go if error press the tab key and
the value is essentially what's going to be right so if there's no error what should we
put so we'll put all of this area over here we'll just skip towards the very end press the comma key
and the value if error meaning if there is a mistake let's put in quotations we'll assume that
it's out of stock and so that's why it's not on this list so we'll just put out of stock close
the quotations and hit enter here it says that i don't close the bracket so i'll just press yes
and now you can see that it says out of stock because there's no power right there and i change
this to say coca-cola then all of a sudden it would change back to the actual quantity here
moving on to level five and these are the most complex and useful formulas out there and first up
we have the x lookup which is very similar to the vlookup other than it can do more things as well
so let's take a look over here you can see that we have a similar data set to the one before and here
we have the product arrival date and essentially we want to find out what brand arrived on which
day now the reason we can't do a vlookup here is because we're actually have to go more to the left
hand side so this is our starting point where we have all the dates and we need to go more to
the left if you recall the vlookup actually went from left to right it can't go from right to left
and so that's the limiting factor there that's where the x lookup comes handy so we'll go equals
x lookup press the tab key lookup value is going to be that date for us press a comma the okapari
is going to be this whole range over here all of these dates ctrl shift down arrow we'll press the
f4 key comma then the return array so what we want as the output is the products here go to
ctrl shift down we'll press the f4 key close those brackets and hit enter then we're just gonna drag
this all the way down to the bottom here and again you'll notice that the very last one has an error
sign here as well if you press the f2 key there just to make sure the formula is working fine
it seems to be the case so it's more most likely that this date actually nothing arrived on that
date and so we can go ahead and change that press the f2 key but instead of doing the f error again
x lookup does have a feature like that inside it so press the comma key you'll find that it says
that if not found which is basically someone like an if error and here we're just going to put in
quotations that nothing arrived on this date close the quotations there and hit enter and so now you
have a similar feature to the if error except that it's all in one formula and finally the hardest
formula we'll look at which is the index match and it's actually a combination of two formulas one's
the index and the other one the match so let's take a look on excel go to the very last tab and
you can see we have this data set with the state revenue by year and so we want to find out the
revenue specifically in the state of california in 2019 and you might consider maybe doing a vlookup
or an x lookup but the problem here is that we have two different criteria one being the year
and the other one being the state and so an x lookup only allows for one lookup value so that's
not quite gonna work there instead we're gonna use the index match equals index press the top key and
for the array we want to select the whole area so we'll go all the way up over here ctrl shift down
ctrl shift right press the comma key as the row number that's where we want to put the first match
so essentially we're going to match these by row and match them by column as well so we'll go match
press the tab key the lookup value for us is going to be the california so the state here
press the comma then the lookup array is going to be this whole range over here
make sure you select it all the way from the top to the bottom press the comma key and we want an
exact match so we'll put a 0 there comma again and now we want to go for the column side let's
go back up here we're going to do a second match this time around for the column where the lookup
value is gonna be that 2019 comma the lockup array is gonna be all of the years that we have up here
come again and we want an exact match close those brackets and we've closed the brackets for the
match now we gotta close them for the index and hit enter there so it says that in california
in 2019 we had 75k let's take a look at that california's third one in 2019 we have that 75k
now for the two bonus tips and sometimes in a large excel file like this one over here with a
lot of numbers you don't really know which area is hardcoded meaning that you wrote it yourself
versus an area that's in a formula and so for this you can just go under the formulas tab
and go to show formulas up over here and now you can see exactly that this whole
area seems to be in a formula same thing down below if you ever want to deactivate it just
click on the show formulas again that's gonna do it for you similarly with complex formulas
sometimes it's useful to find out where they're coming from so what is this linking to exactly
for this you can go to trace precedence click on there once now you can see that for the sum there
it's actually tracing from all of these areas over here if you were to use more complex formulas
it obviously becomes more useful go to remove arrows to remove that let me know in the comments
if you knew these bonus tricks and how many formulas you knew out of 10 for more on excel
tricks check out this link over here or this other link over here to learn more about excel
hit that like and that subscribe button if you liked it and i'll catch you in the next one