what's up everyone it's kenji here and today we'll go over some of the most common excel interview
tests that i've encountered both recruiting for finance roles as well as more business oriented
roles especially in big tech and here's some general guidelines about them these tests are
often time constrained at around 30 minutes so you don't really have time to be typing things
manually also they're very result oriented so they won't really ask for things like the syntax of a
formula or which tab can you find a certain tool in instead it's up to you to figure out what tools
work best to reach the desired output and these excel tests can be split into two main categories
on the one hand you have formulas where you might need to include some ifs index matches or lookups
while the other one has to do with data cleaning where you might need to use some text functions
you might need some numbers formatting text columns other things like that to clean up a
data set in this video we'll look at case studies of both so let's get into it and you can download
the file i'll be working with for free in the description so here's the document with the excel
test and it's got two different tabs one going over formulas and the other one to do with the
data cleaning side so just general guidelines it says to fill in these light blue areas over here
and the instructions are that we need to use two different formulas to find the net income in new
york for the period between january and may and it's quite common where they don't actually tell
you what formula to use instead it's up to you to try to figure that out and there's generally more
than two right answers like they ask for here so there should be plenty of room for you to
try different ones basically we want the formula that's going to say that if it's currently january
and if it's in new york then we want to get the net income for that and so we'll go equals some
ifs that's the first one we'll be using you can see the instructions on what it does over here
press the top key the sum range is essentially what we want as the output so it's the net income
for us that we're interested in we'll go ahead and select it here press the ctrl shift down arrow
and then from there you have to press the f4 key you'll notice that that puts in a few dollar
signs in there and what that's saying is basically that if we copy this across say all the way to may
this thing is going to stay fixated at net income and it's not going to move across
so that's what we want press the comma key the criteria range the first one let's say that's the
month and so we want the period to be within this range over here press the f4 key again press the
comma key and we want that to be in the january period you can't see right now but there it is
selected and for this one we're not going to press the f4 key because we want that to move across
all the way to the different months press the comma key the second criteria
has to do with the state so this is this area over here ctrl shift down arrow
press the f4 key comma and then we want the criteria itself to be the state of new york
we'll press the f4 because we don't really want to have it move when we copy it across then close
the brackets and press enter from there we can just go ahead and drag that and paste it across
that should be working for us to double check you can get into this last one press the f2 key now
you can see that it's selecting this this over here this month of may and same thing over here
and so on so that's looking good now we need to find a different way to do this as well
the second formula that comes to mind is an index match and it's actually a combination of two
different formulas one is an index and the other one is a match and essentially it's gonna let us
select something horizontally and something vertically which is essentially what we want
so it's actually quite similar to the sumifs so we'll go equals index press the tab key and for
this this is the actual output that we want which is the net income over here a bit like the sumifs
earlier press the f4 key to lock it comma now we want the row number and that's where we will
would put the first match which is going to be the horizontal one and the second one for the
vertical side so top press the match key press the tab there the local value is the january
which is what we're looking for right now press the comma key and the log up array is the whole
area over here which is a whole period press the f4 key comma and the match type we want it
to be an exact match in this case so put the zero close the brackets press the comma again and now
it's asking for the columns so for columns we want new york so we'll go match again press the tab key
the local value is going to be new york comma the lockup array is going to be the whole state area
press the f4 key to lock it and again in this one i just forgot here but we need to lock this side
over here as well so i'll press the f4 key select it all and then press the f4 and then lastly we
want it to be an exact match so we'll put a zero close the brackets and then close the brackets
again for the index match press enter and now if we've done this correctly it should give us the
same numbers as above that's a way for us to check and that is the case so it's all looking good
moving on to the second question in this sheet press the control down arrow to get to the bottom
here and here's the instructions it says to create a drop down list by month that's the first step
and secondly to link the net income in new york to the drop down list by month so what i think
this is saying is that hey if we select a certain amount we want the net income to move dynamically
with it so if we have february here we want to see the net income for february now firstly go ahead
let's go ahead and do the drop down list and for that what comes to mind is a data validation so
for that we'll go under the data tab and then in data validation which is this tick thing over here
click on it and we want to do a list essentially that's going to show us all of the months and the
source here we'll go ahead and select it which is basically all of these months that we have up here
and then we'll close that press the ok key and now if we scroll down to the bottom here we should
have a drop down list and i think that's currently the case where we can select a different month
so let's say we leave it at a march here and now the second part to be honest it isn't all
that clear on this question and that's sometimes the case where you need to interpret things a bit
yourself but i think what it's assuming is that hey if we have a certain amount we want to find
the net income so it would be good to do some sort of a lookup lookup function we'll go equals
type x lookup and here it's going to show you the instructions of what it does so basically
what it's going to do is if we select the month of march then it's going to look for that net income
in that period so we'll go ahead and press the tab key to select it the local value like we said
is the current period comma the lookup array is the range where it's going to be looking for it
and so we want to look for it up over here press a comma key and then it's asking for a return array
so that's basically what is the return what is it that you want so in our case we want the net
income right so we'll go ahead and select that it doesn't really matter if we select
it from this first first row or the second row as they're all the same numbers so we'll just
select the first one and then press enter and from there in the period of march it says the
net income is 59 000. so let's go ahead and see if that's currently the case and it is indeed
and now we can dynamically change this so if we change that to january
then it moves all the way to 79 76 6 000 sorry as that is the case here so it's looking good
moving on to the second section of the excel test and over here we've got the data cleaning exercise
so the instructions here say that we need to clean up this data set and create a table with
the first name the last name the phone number and the state so here's the raw data and from
what you can see obviously it's looking quite wrong it's not looking too great there's a lot
of uppercase layers where there probably shouldn't be there's also some spacings here some things are
involved some things are italicized as well those are things that we want to try to remove ideally
using formulas so i think the first formula that we can use here is the trim which i'll just type
over here the idea is that this is going to remove all of the spacings between the words
and it's only going to leave one space so equals trim you can see the instructions of it over here
press the top key and just select it it's that simple and then we just drag that down and there
you go now it's looking somewhat cleaner the second one that we can use is known as the proper
and what this does ctrl b to bolden it and what this does is it's gonna only put the upper
case on on the first letter of a word so for some for example it would be just the s smith only the
s as well so we'll go equals proper press the top key and we'll select this over here then
we can just drag that across now that's looking a lot better as you can see over here it's looking
much more clean one thing that i think is important to mention here is that you can
actually merge both the trim and the proper function and put them all into one if you
ever wanted to so for example i can go here under equals then i can do trim press the top
key and instead of selecting the text i'll just put the proper inside it and then we select the
text which is this one over here and then we need to close the brackets press yes and that's going
to show you the exact same thing over here press the delete key to get that out also let's space
this out a bit more so for this one here press the alt hoi that's going to open it up for us
and now it says over here up top that we need to create a table with this so the first name
the last name phone number and state so basically we need to try to categorize these we can actually
go one by one and just try to copy and paste them which is obviously quite a bit tedious right you
could select this then copy it and paste it first let's go and paste this as values so control c
and then over here you can press the alt h v which is a shortcut for that so what i'm saying
is we could go say select sum and then ctrl c and paste some say over here and then the next guy and
so on which is quite a bit tedious so press the delete key we're not going to go through that
and instead what we can use is the text to columns feature which is basically going to allow us to
categorize everything by a certain requirement so for example if it has a space then it's gonna
break it down by that so we'll go ahead and select this area ctrl shift down arrow
and then the shortcut here is the alt a e so it just took us over here to the text of columns and
delimited is fine press next here and now the delimiter here is basically what's what's the
breakdown so we want it to be split into spaces so that's what you want to take over here so you can
see the data preview here where everywhere where there's a space it's going to go into a new column
press next and then over in destination this is basically where the output is going to be
i know destination sounds a bit fancy just where it's going to be on the data set so
let's go ahead and select the range and say we want to put it over by the h here
and then finish and now you can see that it's been outputted as split column so once just the first
name last name so let's go ahead and categorize them so first last and then here we want the phone
and then the state now that's looking a bit more like it another thing that we should do here is
that the phone numbers you can see that typically they have dashes between them so every three
numbers you might have a dash so let's try it and do that ctrl shift down arrow and then we'll press
the ctrl 1 key that's going to help us reformat the numbers from there we'll go under the custom
over here which is going to let us customize it and what we want to put is this number sign
and we want to put it three times then after we want to put the dash three more times and then
three more times there so the numbers are gonna essentially look like an actual phone number press
the ok key then you can see here that you can actually see them so press the alt hoi which is
going to reformat things so they look a bit more spaced out now that's looking slightly more like
it that being said because this is all us numbers we could actually just put a plus one and then put
brackets between the first three numbers so we'll go ahead and select them again ctrl shift down
arrow ctrl one again and under custom at the very beginning here we're just gonna put a plus one
and then we'll put a space and then we'll start putting the um parenthesis at the start and the
end of the first three values press the ok key and now that's actually looking a lot more like it
lastly for the states here we could actually make them uppercase mainly because well states
are usually both letters in uppercase so we'll go equals upper press the top key and that's
just gonna put everything in uppercase we'll select it and then just drag that down over here
then we're gonna paste it as values so for that ctrl c and then alt h v v and this way we can just
drag that across so control x and then ctrl v and now that's looking a lot more like it let's
finally reformat this a bit so it looks a bit fancier so ctrl b and we can also change the fill
color so alt h h and from there we'll select say a dark blue like so and then alt h fc that's gonna
change the colors and we put the white over here so we can see it a lot better uh one final thing
maybe for the state we can actually align that to the center so we'll go alt h ac and there you go
now we clean this data set from looking like this as it was before all the way to this over here
which i think looks a little more professional hopefully that would be enough for you to pass the
excel interview and if you're liking this video you can also check out our excel for business
and finance course where we teach everything we know about excel specifically for people either
looking to break into a business or a finance role or those in it trying to level up their
excel game as well as going over the fundamentals of formulas formatting and charts we also go over
case studies that replicate the type of work you might be assigned in your day to day ranging from
financial modeling to cleaning a complex data set and more so if you're interested in checking it
out i'll leave it in the description below if you want to see more videos on excel you can
check out this one over here on forecasting or this other one over here on excel visuals
hit that like hit that subscribe down below if you liked it and i'll catch you in the next one