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 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 video 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 video
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 videos ranging from just
the idea phase all the way to uploading them and if we look at this current video 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 video 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 finance course over here you can check out this video on
excel interview test or this other video 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