REAL Excel Interview Tests for Business & Finance Roles - YouTube

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