TOP 10 Excel Formulas to Make You a PRO User

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