Take this Excel Interview Test and Avoid Interview Embarrassment

hey applicant I'm the interviewer and in this role you're expected to be proficient in Excel so we're going to test that through an Excel interview test that's going to be five questions long from easy to hard sounds good yes ready to get started and if you also want to practice check out the free Link in the description below to download the exact same Excel file so over here we have the first question which is actually the easiest one let's take a look it says to create a condition to highlight Revenue above 50,000 in green and below and including 50,000 in Red so here's the revenue column over here and first we'll just select all of the numbers and what we want to do is create a condition using conditional formatting that's probably the easiest method so on the green side it's going to be when greater than 50,000 it's in green so we'll just type the 50,000 in here as the threshold and change the color to a green and hit on okay so that's 50% done and the other side says below and including 50,000 in Red so we'll go back to conditional formatting highlight cell rules but this time you can see that we don't have a less than or equal to we just have less than so we could put two conditions one less than and one equal to or simply customize it ourselves with more rules so we're going to say that if the cell value is less than or equal to 50,000 let's put that in here then we're going to change the formatting to something like adding a fill color which let's say is in red like this red over here and we can also change the font color down over here let's suppose I go for this orangish color so we can see it a bit and click on okay and okay again now you can see what that looks like and it's including that 50,000 as well awesome that's level one done so let's move move on to the next level with question two and over here it says employees with Revenue over 50,000 should receive a bonus of 5% of Revenue so you can see here the employee the revenue they're getting and for the bonus we have that 5% to the side now calculate the bonus amount in the yellow area so this part over here if it's not over 50,000 then leave blank now this is some kind of a conditional statement so it probably makes sense to use an if statement so under the first name we're just going to put equals if hit the top key there The Logical test is that if this Revenue figure over here is greater than 50,000 if that's the case what do we want to do so we'll put a comma and the value if true if it is the case we want to reference that same D7 which is the revenue and multiply that by the Bonus amount which is 5% we'll hit the com there and then the value if falls meaning if it's not above 50,000 well we want to leave blank as it said up over here and we do that by adding two quotations close the parenthesis and hit enter that's the first one done and you might think of just dragging it down but actually we have a problem that's that it doesn't quite work this is because we're actually moving down the cell references but we should have actually locked them with the dollar keys so if we go back to the top one first you'll notice that we have the G7 which is this one right here we should lock that under the row with the dollar sign this is going to prevent it from going down same thing with the bonus so under G10 we'll put a dollar sign on the tender so it doesn't move down now we can hit enter and double click on the side to drag this down and you can see when they're above the 50,000 Mark they're starting to get a bonus which should be 5% of their revenue great now moving to level three which is slightly harder over here the question says to separate the department and region column so this column over here into two separate columns in the yellow area so one for the apartment and another one for the region for this there's probably a few different ways to go about it so let me show you one of them which is one of excel's new formulas called the text split hit the top key there and what it does is it's basically going to split text into two right so this is the text we want to split comma and for the column delimiter we want to add in quotations the underscore that's how we want to decide to split these two columns close the quotations and close the parentheses and hit enter now we have that first one done and we simply need to drag this down all the way to the bottom there and you can see what that's looking like now if you don't have the text spit formula there is also another method first let me delete this part over here and instead what we're going to do is select the relevant area so this over here go over to data and click on text to columns this is basically going to do the same thing we want it Del limited next and here as what's our separator we want to go to other and select an underscore that's what we'll type there you can see in the preview that it's now splitting things correctly hit on next and for the destination we're going to want to switch this to this part right here and click on finish now you can see what that's looking like where we have the department in one side and the region in the other if you're finding this a bit too fast or slightly too challenging we'd recommend you check out our Excel for business and finance course with our comprehensive curriculum we cover everything you need to know from formatting best practices and shortcuts to building building awesome visual dashboards creating large Dynamic Financial models and much more this is basically the course I wish I had before I started to work at an Excel heavy corporate job if all of this sounds interesting check out the link in the description below and if you want more than just Excel we also offer courses including powerbi Finance evaluation and much more all right back to the interview awesome now moving on to the stuff in level four let's take a look at the question so it says to use a pivot table to find total revenue by product and average profit by product so over here is the table that we basically just want to put inside of a pivot table so we'll go to insert and click on pivot table we should get the whole area selected by default but let me put it in an existing worksheet over here just to the side so we can see it better going to click on okay there and so here's our pivot table let me resize that awesome so first we want to find the total revenue by product and for this we're going to select the products and put them under the rows so we can see all of them to the side and then we want to get the revenue as our values so we'll just drag and drop that you can see our sum of Revenue there by product so that's all looking good for the first part and secondly we have the average profit by product so for this it's the same thing we'll drag and drop the profit but you'll notice though that it's still the sum of the profit it's not the average so that's not quite right we actually need to go to this drop down over here and under value field settings we're going to change this from the sum to the average that should do it for us and click on okay if you really wanted to you could customize this maybe change the formatting of the numbers so you you can read them better with some commas or change the titles over here so they are exactly the same as the question now if you found this question quite easy wait till we get to the bonus question at the end of the video but for the time being let's go over level five and the question they have there it says over here to use two different formulas to find Sara's revenue and over here in yellow we have the area for one formula and down below the area for the second formula so basically we want to find Sara and then get the revenue for her now there's probably multiple formulas we could use here but an easy one that comes to mind is the X lookup so we'll go equals x lookup hit the TP key there and the lookup value well we're looking for Sara right so let's select her comma where can we find her that's a lookup array well we can find her in the list of names over here comma and then the retary is what do we want as the answer well we want to find Sarah's Revenue so we'll just select the whole Revenue area over here close the parenthesis and hit enter now it says 48,000 if we check in the table you can see that looks about right great that's the first Formula done and now let's go over formula number two and for this I'm thinking we could use a v lockup which is very similar or let's try something different with an index match so equals index hit the Tab Key there and the array is basically the result that we want in our case we're looking for the revenue figures comma then under row number we'll put the match formula hit the Tab Key there and the look up value well we're looking for Sara much like before comma and we're looking for Sara within this range over here comma and as the match type we we want an exact match we'll close the parenthesis for the match there and now we need to close it again for the index part so we'll do that and hit enter so it says 48,000 meaning it's probably correct too hey welcome back congrats you've solved all of these questions so you've definitely passed this Excel interview test that said here's one bonus question you probably won't know so let's take a look at this mysterious bonus question over here it says to remove the parenthesis and everything inside of it for each client so if we take a look at the client column over here you can see that they have these parentheses and within it they have some text so it's saying to remove everything in there as well as the parenthesis now what makes this a bit tricky is that they're not all the same character length you can see some over here seem to be shorter While others seem to be somewhat longer now to change this what I'm I'm thinking is first selecting everything and then clicking on controll H that's the same thing as going to find and select and clicking on replace now we should get this pop up and from here we want to find what exactly well we want to find things in parenthesis now how do we say exactly what we want in the parenthesis because they're all of different lengths here right that makes it a bit tricky and they're obviously not the same text so what will put is an asterisk sign inside of it that basically says to remove everything that's inside of the parenthesis now what do we want to replace it with nothing right so we'll just put on replace all and then hit on okay and close out of that you can see that now that's looking all clean so there's a cool trick for you let me know down in the comments if you got that bonus question now to continue learning Excel check out their latest feature over here or take our Excel course over here hit the like and that subscribe and I'll catch you in the next one

Channel: Kenji Explains

Views: 39,459

Keywords: excel test, excel interview, excel interview test, excel assessment, excel exam, excel interview question, excel interview questions, hard excel interview questions, common excel interview questions, excel test interview, pass this excel test, can you pass this excel test, excel questions what to expect, excel lookup questions, excel data cleaning questions, excel data analysis questions, excel formatting questions, excel conditional questions, excel pivot table questions

Id: E4t43Bx7XvY

Length: 12min 7sec (727 seconds)

Published: Sun Feb 11 2024