5 MUST-KNOW Excel Interview Questions

if you're applying to any of these roles odds  are you'll have an excel interview test of sorts   now to help you pass that here is five of the  most common excel interview questions let's go   the first type of question has to do with search  and this is simply about searching for a figure   within a table so let's take a look over here  you can see that the instructions say to use two   different formulas to find the location of conor  west which is an employee over here and here's all   of their office locations and so right by where  it has the yellow cell is where we wanna try the   different formulas so the first one that comes to  mind is a vertical lookup where it's gonna look   for it for a corner west vertically here so we're  just gonna go equals vlookup press the tab key the   lookup value so we're actually looking for corner  west comma the table array is gonna be all of the   range that we're interested in in our case it's  all of this ctrl shift down ctrl shift right   comma key the column index number so how the  vlookup works is it goes from left to right   so for us it's not we're not interested in the  output for the employee but rather for the office   location so that's number two for us comma and we  want an exact match we'll close those brackets and   hit enter and that should give us chicago which it  seems to be the right one if you want to test if   the formula makes sense you can always change  the name say evan heinz and see that goes to   london and that seems to be the case so that looks  correct press ctrl z there to go back and for the   second formula we could go with something like an  x lookup which is very very similar to a vlookup   but for this scenario let's go for something like  an index match which is somewhat different now the   index match is going to be a combination of the  index formula and the match formula so we'll go   equals start with the index first press the tab  key the array for us is whatever we're interested   in getting as the output so in our case it's  all of the different office locations comma   then for the row number here's where we're going  to put the match function press the tab key there   the lookup value well we're looking for conor west  comma the lookup array is where are we looking for   him so it's going to be in this region over here  ctrl shift down comma we want an exact match we'll   hit the tab key there close those brackets and  now we need to see do we want a column number   in this case we don't really need one so we're  just going to close the brackets again this   time for the index and hit enter and now we have  conor west in chicago which seems to make sense   moving on to the second question and here we've  got a calculation and this is often along the   lines of how much do we need to sell in order  to reach x in profit in this particular case   it's find the number of bottles we need to sell  to reach ten thousand dollars in profit so we've   got a set of assumptions here like the price per  bottle some tax that will be incurring and the   cost per bottle so over here we can see all the  calculations that are going into this so if you   press the f2 key you can see that the the sales  is simply the quantity sold times the price and   so on and so forth so up here if i put a 1c then  all of this area is going to auto populate and we   want this very last cell to reach ten thousand  dollars now to do so let me just just randomly   try say five thousand see what that looks like  it goes a bit too high there maybe i'll go for   two thousand that's way too low there and instead  of going about this manually which is probably   not gonna impress the interviewer too much you  can use something called the what if analysis   this is basically a built-in tool on excel  that's gonna allow you to calculate given a   set of variables so in our case we're just gonna  go to the data tab go to what-if analysis and   we're going to look for goal seek so we want to  set the this value which is the profit for us   we want to set that to exactly 10 000 so we'll  just type the 10 000 in here and the cell that   we want to change in our case the variable is the  quant the bottle sold so the quantity sold we'll   hit okay there and then excel is just going to  quickly calculate that for us hit okay now you can   see the exact amount that's going to reach 10 000  profit it's actually 2 900 and something bottles   next up we've got data cleaning and this is simply  about how efficiently you can clean a raw set of   data so in this scenario it just says to clean the  raw data import and identify sales less than 300   dollars so over here in yellow is where we want  to put all of this data and as you can see the   problem right now is that it's all within one cell  so it's not actually separated by cells and that's   basically what we want to try to get to over here  now you can obviously do this manually just put   squid in here as a first name or does the next one  and so on and so forth but well it's going to take   a long time and it's not very impressive either so  instead we're going to use a special trick called   text columns for it you're just going to go ctrl  shift down to select this whole area over here   then we'll go to data go to text to columns and  over here i know this guy this is going to look   a bit daunting for now just leave it at delimited  go to next and over here this is basically where   you how do you want the separation to be qualified  so in our case you'll notice that we have a space   after every every word so that's basically where  we want the separation to be so we're just gonna   put the space as our separator and down below  in the preview you're gonna see more or less   what that's gonna look like we'll hit next there  and the destination for us we want it to start   right over here then we're gonna hit finish  hit okay there and just like that we're gonna   have all of that raw data separated over here into  different areas so it's exactly the way we want it   one final thing though is the sale amount it says  to identify sales less than three hundred dollars   now to do so a quick and easy way is  going to be using a conditional formatting   so let's hit ctrl shift down to select the whole  area and we're going to create a new rule we'll   just go to i'm home over here go to conditional  formatting and we're going to create a rule that   basically highlights anything that's less than  300 dollars so it's very easy for us to identify   so we'll go to highlight cell rules less than and  the rule here for us is when it's less than 300   then we want it with live red that that's fine  for us hit ok there now we can see exactly which   are going to be less than 300 and if you're  looking for more extensive excel prep material   you can consider taking 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  and what makes this course different is that it's   applied to the real world while we still cover the  theoretical lessons like formatting formulas and   charts we also offer case studies that simulate  the type of work you might be assigned in your day   to day ranging from financial modeling to cleaning  a data set and presenting some visual insights and   if you get stuck along the way you can easily ask  us questions in the discussion forum so if you're   interested in checking it out go to the link in  the description below alright back to the video   next up in question four we've got visualizing  data and this is primarily in the form of charts   so let's take a look over here and it says to  create a chart showing product and service revenue   and gross margin percentage so over here you can  see that we've got an income statement where we   have the revenue split into products into services  and the last part the gross margin percentage is   this percentage that we have down over here so  i basically want to make a chart that's going to   show all of these somewhat nicely say so let's go  ahead and select them first we're going to press   the shift down and then ctrl shift right then  keep pressing the ctrl key so you can drag the   gross margin percentage down over here from there  we're gonna go to the insert tab to add the chart   click on insert what we're gonna insert is a combo  chart which is gonna allow us to show all of this   better i think so we'll just click just click  on that one once but you'll notice here that the   gross margin percentage is actually all the way at  the bottom and the reason for this is simply that   it's in a percentage so it's actually less than  one which is very low compared to these figures   so let's go ahead and select it first we're just  gonna put it on a different axis to the side so   we'll go right click format data series and we're  just gonna put it to the secondary axis and just   like that we can see it a lot better now another  thing that doesn't quite look right here are all   all of the different years so as you can see it  says one two three while it should probably say   all of the years up over here so let's go ahead  and select that part right click go to select data   and from here for the horizontal category labels  that's basically the one we want to change   we'll hit edit there and we're just going  to select all of these years that we've got   up over here hit okay now you can see that that's  looking a lot better for the colors here actually   first let's get rid of these grid lines which  are a bit distracting too let me fast forward   how i change the colors without simply just going  to the format and changing the colors from here great now i think that looks a bit better than  with the default colors and finally we've got   question five which is about analyzing data so  let's take a look over here and it says to show   by quarter the quantity sold the total sales  and the average sale price it's going to be   using this data set that we've been provided down  over here the tougher part is actually to show by   quarter because finding a quantity sold or a total  sales is probably not that hard now for this we're   probably going to use a pivot table which is one  of the better ways to analyze data so we'll go to   insert then let's go to pivot tables actually let  me go ahead and select everything here so control   a and then click on that pivot table and we want  it in an existing worksheet let's say we put it   right over here to the side so the interviewer  can also see it hit ok there and here is going to   be all of the different fields that we have so in  our scenario we said that we wanted it by quarter   so the date is probably quite an important field  so we'll put that under the rows and here you'll   notice that you actually get it by quarters  already we don't really want it by date so   we're just going to drag that out that's going  to remove it for us and so now if we open these   put the plus sign and the plus sign again you'll  see that we have everything split into quarters   which is what we want from there we want a  bunch of different data so the quantity is sold   it's probably going to be under purchase you  don't hit inside that and we don't really want   the sum because that would be the total sales  instead we want the quantity so the the count   we're just gonna go to value field  settings we're gonna hit the count   then we'll do the same thing for total sales  so we'll drag the purchase one more time and   this time for the sum that makes sense that's  the total sales for us and one final time for   the average sale price so we're just going to  select it again put it back down but we've got   to change the field instead of the sum we want  the average so we'll go value field settings   and we're just going to click on the average there  hit okay and now let's move along a bit let's also   change the labels here as they're probably they  don't make much sense as of now so we'll just   click on them go to value field settings the very  first one is just going to be the quantity zone hit okay there that second one sum of purchase  is going to be the total sales so value field   settings total sales hit okay the very last one  is gonna be just the average average sale price hit okay there and just like  that that's looking a lot better   best of luck if you're preparing for an  interview check out this video over here   for a more advanced excel interview or this  other link over here to learn more about our   course hit that like and that subscribe if you  liked it and i'll catch you in the next one

Channel: Kenji Explains

Views: 218,649

Keywords: excel test, excel interview, excel interview questions, excel question, excel test interview, excel interview test, excel charts, excel formulas, top excel questions, common excel questions, goal seek, pivot table interview, what if analysis, text to columns excel, excel test to column, goal seek analysis, create a combo chart, make a combo charts from scratch, xlookup, indexmatch, excel formula test, excel visuals test, excel questions for interviews

Id: Eb0YfiFsPok

Length: 11min 55sec (715 seconds)

Published: Sun Sep 04 2022