Excel Formulas YOU NEED for Business & Finance Roles #Finance

what’s up everyone it’s kenji here and today i thought i’d share some of the most useful excel formulas that i’ve had to use based on my experience working in business or in text-decoration: none;">Finance roles and you can download the excel file i’ll be working with in the description so let’s get into it and thank you to meistertask for sponsoring this CashNews.co let’s suppose that we’re working at apple as analysts and

we’re given this data set over here where we’ve got the region the store id the product and the quantity the first thing you’ll notice here is that under a product it seems to have a typo where you’ve got two eyes instead of one as it should be and you can either go one by

one and try delete them like so or the other way that we can do it is by removing the duplicates so essentially just ctrl shift down arrow to select the whole column and from there press the ctrl ctrl h key and here it says to find what and we want to find the two eyes so iphone like that and then

we want to replace it with just the regular iphone and then you’re gonna go to replace all because we want all of them removed click on that and it says well done we made 19 replacements press ok and then you can close it and if you go back up you’ll notice that there’s no longer

the typo of having two eyes and let’s say that our manager wants us to create a table with the quantity per region as well as per product so for that let’s go over to the column g over here and you’ll notice that we can either go one by one and copy and paste this but we might

miss on some so instead what we’re gonna do is go equals unique press the tab key and it says to re returns the unique values from a range so press the tab key there what we’re going to do is select everything so ctrl shift down arrow and then press enter what this does is it’s

going to remove all of the duplicates so you have iphone here a bunch of different times so instead it just has it once and so now we have all of the different categories of phones similar to that for the region here what we can do is either go one by one and type americas and so on and so forth

press the escape key the other thing that we can do is type equals transpose press the tab key and then we’ll select the three main regions which are these three that we have up top press enter and what this does is essentially a copy and paste something that’s vertical into horizontal

or vice versa now let’s do a bit of formatting on the table so firstly ctrl shift right arrow here and then go to alt h b o that’s going to give you the bottom border and for here ctrl shift down arrow and then alt h b r standing for the right side and that’s looking a bit better

then let’s bolt in this so ctrl b same with this side ctrl b all right so now that we have this we needed to essentially do a formula that’s going to say that hey if this is in the americas region and if this is an iphone 11 then we want to get the quantity of all of those and

that’s gonna trickle down all the way to the bottom so for that we’re going to use a sumifs so we’ll go equals sum ifs press the tab key and the idea is that the sum range that we want is the quantity that’s the output that we’re looking for so we’ll go ahead and

select that ctrl shift down arrow press the f2 key which is going to lock it you see those dollar signs over there that basically means that whenever we copy this across from the americas to the emir region it’s not going to move this column over here because if it does it’s obviously

going to be wrong so press the comma key the criteria range is basically what do we want as the first criteria it’s the america’s region so we’ll go ahead and select that american region over here ctrl shift down arrow press the f2 f4 key sorry comma the criteria number one is the

americas now for this one we’re going to have to lock it again press the f4 key but instead we don’t want both the the column and the row to be locked instead we just want the row to be locked so we’re just going to move that so press the f4 key again now you can see that the

dollar sign is only on the number two so this means that when we copy this down this is still going to remain on this first row over here then press the comma key again and then for the criteria range number two the second criteria is the products over here ctrl shift down arrow press the f4 key

comma and then we want it to be the iphone 11 for this first one and again in this case we need to lock it again press the f4 not once not twice but three times such that it’s only the dollar sign is only on g press enter and then we can just drag that down so we can do ctrl c and just copy

and paste it across ctrl v and there we go one check that we can do here to make sure that everything is looking good is to press the f2 key on the very last one here now you can see that it’s actually moved from the americas to the emea region and it’s moved down to the iphone 6 as it

should have and you’ll notice that there’s a bunch of different zeros especially for the say the older versions of iphones which makes sense because after all they’re the ones that they’re probably not producing anymore right one final thing that we can do here is to ctrl

shift down arrow ctrl shift right arrow then we’ll go under conditional formatting and what what this does is it’s gonna after you set a certain parameter it’s gonna allow you to highlight some regions the reason you might want to do that is say if we have too much in stock then

we probably don’t want say the the factory to send us any more stock to that particular store right so for that we can do something like a highlight cell rules if it’s greater than you can see that there’s a bunch of different options you can do color scales you can do even icons

and things like that but for now we’ll do highlight cell rules whenever they’re greater than the 500 we’re gonna put them in red as that’s implying that hey in the americas region for iphone 11s we really don’t want to be shipping any more stock over there because we

already have a lot speaking of working with your manager i want to talk about the sponsor of this CashNews.co mysterytask they’re an intuitive task management tool to boost productivity in the office for you and your team using their flexible kanban boards you can track anything from the

growth marketing of a product the hiring steps to a group for employees or simply keeping track of your blogs in my case for instance i can use it to keep track of my youtube CashNews.cos ranging from just the idea phase all the way to uploading them and if we look at this current CashNews.co

you’re watching you can see that i have tags for it being sponsored i also have a few checklists as well as the due date for this once it’s uploaded i can move it across very easily to the next section and mark it as complete so as you can see it’s very well designed and easy to

use in fact they even provide you with customizable reports on how you or your team are performing over 2 million teams worldwide are already using meister task to get more done so if you’re interested in checking it out you can go to the link in the description below where you can get 15

percent off using code kenji15 both for their business and their pro versions alright back to the CashNews.co next up in sheet 2 we’ve got the date and the respective Revenue the cost and the net Income for it and our manager would like us to create a small

summary table where we have a drop down where we can choose a date and with it we can pick the different Revenue the cost and the net Income so for that for the date part firstly we’re gonna have to do a data validation so go under the data tab and from there

we’re gonna click on this thing over here and then we want to make a list and the source is going to be all of the dates that we have available on the data set so all of these ctrl shift down arrow then just press this thing again and press ok now if you go back up ctrl ctrl up arrow you can

see that we’ve got a whole drop down of the different dates one more thing here you’ll notice that even though we pick a date it’s actually in this strange number so this is actually because of a formatting error because we don’t have it formatted as a date to do so you can

just press the ctrl 1 and we’re gonna go under date and this kind of format is fine press enter now you can see it’s changed to the date format another thing we could do here is use the today formula what this is gonna do is that whenever somebody opens up the model it’s going to

refresh to today’s date so they don’t have to go ahead and choose it so we’ll go equals today press the top key and then press enter so today is march 1st and so that’s why it’s giving me this date but whenever somebody were to open the model it would refresh to

today’s current day and over here down below we need to link the Revenue for the data it chooses up here so for that we can use something like an x lookup so we’ll go equals x lookup press the tab key the lookup value here is we want to look for this date press the f4

key comma the lookup array is basically what range is that gonna take going to be in which is this whole range over here ctrl shift down arrow and press the f4 again comma the return array is what what is it gonna return so we’re returning the Revenue that’s what

we’re interested in right now so we’ll go ahead and select that over here ctrl shift down arrow press the f4 key and now you’ll notice that there’s a few other arguments to the formula like if not found much mode etc but you can see that these are actually in in quotation in

brackets sorry so basically means that they’re not necessary for the formula to work and we don’t need them so just press enter and there you go and now we can copy that and paste it down here for the close side press the f4 key here sorry f2 key to take it to take a look at it and

you’ll notice that now it’s still on the Revenue key obviously so we need to move that across to the cost column and press enter and then for the net Income we could do an x lookup again or we can just do equals this one minus this one which should be

the same thing and let’s reformat this a bit so ctrl shift down arrow press the ctrl 1 key and we’re going to go under the number tab and over here we want to do a separator and we don’t really need the decimals we’ll put them at zero press okay that’s looking a bit

better for the last one for net Income ctrl shift right arrow ctrl b and from there alt h b d that’s going to give us some top and bottom borders so it looks a bit more clear and now say if an executive came in to see the model he or she can just pick the different date they

want and they can get the all of the summary data as opposed to having to see all of this model so for example this this over here could be under the cover page or something like that too lastly on sheet 3 we’ve got a raw data set over here and our manager is asking for us to split this split

into the first name the last name the product and the price now for that we could go one by one so go theresa and then you’ll go may here etc which is quite a bit of a pain to do so press the ctrl z and ctrl z again to go back and instead we’re gonna select this whole region here ctrl

shift down arrow we’re gonna use the text columns feature so go to alt a e for that which is basically this area over here and then go to next and what we’re going to do is we’re going to put a delimiter so every time that there’s this dash line we want to essentially split

it into a new column so we’ll go under other and we’ll put the dash over here now what this does is you see over here down below it’s going to split it all across so we’ll go next the destination is where we want this to to come out which say we put it over here and then

we’re gonna go to finish and now that’s looking a lot more like you where you’ve got the first the last name the product and then you’ve got the price and they’re all split accordingly but let’s say that our manager is being a bit annoying now he says actually i

wanted the first name and the last name to be to be together for that we can use the concatenate function so we’ll go equals concatenate press the tab key and so the text one is this one press the comma and the text two is this other one over here and press enter now it’s all together

but you’ll notice that it actually has a space it doesn’t have any spaces in between them which obviously doesn’t look that good for that we can go ahead and change it so we’ll go to the f2 key here and we’ll delete this comma sign what we’re going to do is put

the amper sign and then we’re going to put in quotations just the space and then close the quotations put an ampere sign again and then press enter and now all of a sudden we do have that space the way we want it to press the f2 again and overall with this quotations you can really put

whatever you want in it say i put just something random and you’ll notice that now it’s going to be within that range press the ctrl z to go back so it’s quite a flexible tool and then we’ll just drag that across all the way down to the to the last one and now for these

other ones we can go ahead and just link them for the ipad and the price and we’ll just track those down let’s also add the titles here so name product and price for more on excel you can check out our excel for business and

style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance course over here you can check out this CashNews.co on excel interview test or this other CashNews.co on financial modeling that’s all for this one hit the like hit that subscribe if you liked it and

i’ll catch you in the next one