Excel Test for Job Interview 2021

now stop me if you've heard this one a finance department has an opening there are 50 applicants question number three on the application goes somewhere along the lines do you know excel and you get 50 answers saying of course i do doesn't everyone my name is guys for commenting i'm a microsoft mvp for excel and this is my version of an excel skills test for a job interview [Music] the questions are designed to have multiple layers or multiple answers it's important to note none of the questions are set up in a way where they have only one correct answer i think that would be totally counterproductive and no excel tests should look like that because in a way you're not only trying to test if somebody you know has done it before and therefore knows the answer right out of the box maybe even more so you're trying to test if someone can connect the dots on the spot and give you a correct answer and you know that beautiful mind kind of moment okay so i'm going to give you a rundown of the five questions letting you know why these five questions are important also what purpose they serve and also i'll give you ideas of a build up so if somebody hits a home run in the first try and gives you the answer in five seconds so that you can say okay now what can you do with that can you do this [Music] okay let's start with question number one or rather task number one now i love this one the the task is very simple it just says can you please input today's date in a range of cells from a3 to a100 000. so as far as the instructions go it's very simple and and i think it's very important that the first task is not something like create a data model where you simulate a many-to-many relationship and everyone just goes like you know it's important that the first task is easy to understand and this one is it only includes two concepts today's date and a range of cells in excel now if somebody doesn't know those concepts then they don't know excel period but if they do it's very easy to understand what is required of them and yet it's not that easy to solve so let me show you how this is done if you know one concept and that concept to me is very important in excel and that is control enter if you know control enter in excel if you don't know control enter check out the video above somewhere it's basic excel it's basic excel and yet many people don't know it but why i would want someone working for me well i would want to know if they know control enter is because i know that they save up about 20 minutes a day next to the guy that doesn't know control engine that's why it's important and now let me show you how this can be done with control enter and then as i said it can be done multiple ways each of these tasks have multiple answers or multiple solutions and then i'll show you what the other solutions could be and then i'll even show you how to upgrade on the answer if it is exactly what it should be okay let's go okay here's the solution i would be most glad to see it would simply be okay i'll select the name box i'll go a free column a 100 000 enter now i have my selection and from here on i'll just do this i'll go control in semicolon and control enter and that is it that is today's date and that range of cells i would also be very glad if the candidate would have asked should the date always be today's date or would it have to be dynamic so that it would change every day right should i use its day function or should i use this regardless if somebody did this i would be extremely happy but not many people would and then then people need to get creative and to be creative in excel the problem here is not today's date as much as is in all these cells that's the problem and if i would have had a way to copy somewhere exactly to a 100 000 then that would kind of be exactly where i you know would want to be even if i don't if i have no concept of control enter of the name box and all that if i could just get that range somehow and a clever way of doing it would be i'll put three in here and i will not format itself the way it should but i really don't care i'll just put three in there i'll put it in general like this and then you go home fill series and you say in this column i want you to create a linear series step is one and stop at one hundred thousand go okay and all of a sudden you have exactly what you need you have those ninety nine thousand hundred cells full now all you need to do is you need to somehow input today's date into the first two and then just select both cells and double click and what you got is you got your 99 997 today's date right and it just it kind of takes the basic concepts of excel building on them connecting a few and creating a very creative solution to this problem now there could be a lot more ways of doing this but apart from you know the two that work on filling multiple cells at the same time so one could easily go like this just inputting the first date and then doing this right but um i'm guessing we're all in agreement this could never lead to eternal happiness or let's put it another way this is not what we're looking for right because before this gets to 100 000 the day is over and and the test is over and this is what we're looking for we're looking for people who you know they can try like this and then in the end even if they got to 100 000 it's still a catastrophe because they get the serious and then they need to copy sales and all that so this very simple very simple task is a perfect start because it's easy to understand and yet to solve it you need to have some complex or not so complex concepts by themselves but brought together in a complex way and i'm going to give you another tip here which is the build up right so if someone was to do exactly as i showed you so i'll just go a free column a 100 000 enter control in semicolon control enter i have the date now what right two seconds solution is here then you go okay now could you make those days dynamic that is a good build-up because you're trying to see if they know their functions if they know today and then you go oh you can make it dynamic okay could you make it so that we have the not just the current date but also the current time so if they know the now function and and you can really build on that right and one of my perfect ways if somebody does this brilliantly because it's such a short task if you know control enter as cool now could you go to the column c and starting with the same date could you give me a list of all weekdays so no no weekends no saturdays no sundays just all the dates occurring on weekdays down to the end of 2021 so what we would be looking for is starting with this phil sirius i want to create a date serious in a column i only want the weekdays so no saturdays no sundays and i want to stop at december 31st 2021 right and this is such a good build up because it actually comes full circle to the other solution right in the other solution if they don't know control enter they actually needed to show us that they know fill serious command whereas here we say oh you know control enter now show me that you know the fill series command and even that not just in a sense of create a series from one to one hundred thousand but create a much more complex date series right so that is a perfect build up of this task now let's move on to task number two okay task number two now this one is set up in a way where it kind of checks if there's a scientist on the other side right if there is someone that is trying to understand the data and also how well they can read excel formulas and functions now this data set is very stripped down just so i can quickly show you the basis of the example but i want you to imagine this the same one column but having about ten thousand or twenty thousand rows right so these there would be twenty thousand of these values but two things would be the same there would be a function in the end the average function and it would return an error which means somewhere in those 20 000 cells there is an error and there would be only one but it would be so well hidden because you know it's among other values and among 20 000 values and another thing to note here is if i would do a count i would see that there are 11 values right now but then if i go count numbers i would see that there are only eight numbers within the range now one of them right the the non-number is an error but that still leaves two values in here that are not numbers and it's this eye to detail that i would be looking at so if somebody would figure out okay there are things here that are you know not as they appear that would be what i would have in mind when giving someone this task and a solution would be fairly simple now basically looking at this function at the end already tells you okay i have an error somewhere now you have two options how to deal with that you can either go to the filter and simply find the error and say this is what i'm looking for and it's going to show you the error it's going to show you which row the error is in and you can correct that a perfect question would be do you want me to fix the function at the end so that if there's an error it doesn't return anything or would you want me to just fix the error itself now the perfect question of the applicant would be do you want me to take the subtotal function and replace it with the aggregate function why well because the aggregate function does something very well it says i'll calculate an average and then in the options tab you can say ignore errors which the subtotal function obviously does not and the sum function the average functions they do not but the aggregate function does so if somebody asked me that given this assignment i would be very very happy with that and another way someone could get creative here is by using the go to special command by saying okay so this is a range of values and at this point it doesn't even matter that there's if there's 20 000 of them or just the 11 that you see here the procedure would be just the same so go to special and then you go so give me those things that are returning errors now those can be formulas or they can be constants you can search for both i'll go with formulas i'll say okay and there it is i just found it right if there were 20 000 of them i've just found it and very similarly i would go about finding my text so i would go go to special i need those constants that are text oh it's these two right and it's because they were written in a way where you started with an apostrophe and that was the input so this is text to excel and a build up of this would be a very cool question of now how would you go about changing all of those text values to numbers but still doing that in the same cells so not trading a new column with value function or anything like that and if that's the only way you know how then do that but what i would be going for is copying a blank cell selecting these and simply go and paste paste special i want you to do an operation i want you to add this cell to these now this cell is a blank cell so it holds a value of zero and all i'm doing here is i'm saying plus zero to each one of these a plus 0 does nothing in a sense of making the number larger or smaller but it tells excel a critical thing it tells well i want to calculate with these items and that's why those two just became numbers and i can show you that very easily by saying count it's still 11 and count numbers is now 10. why well because of this error that one is still not a number but those two text values just became a number and this is a perfect build up that i would use to see if somebody can you know think on a spot and use excel in a very creative way okay moving on to number three okay question number three or task number three is very similar to task number one in a sense that it's very easy to understand or explain what i'm asking so there are 40 values here just give me a sum of top 10 values that's it i need the sum of top 10 values from this range now whereas it's easy to understand the way you're gonna solve it is designed to show me if you can go kind of beyond just the regular seven functions that everyone knows in excel right beyond the sum average min maxing count and let's just throw in sumif and vlookup you know beyond that basket that everyone knows so there's a whole plethora of functions beyond that and this one would actually require you to [Music] know a function called large now there's a function called large in excel where you say this is the range and i want you to give me the seventh largest value from that range right a simple function basically a very simple function and on the opposite side you got the small function but it needs to be used here the the correct and and i i know i said there is no correct only one correct answer but the most correct answer to this would be somewhere along the lines of this so if somebody would have done this would have gone give me one to ten over here give me equals large of this array and i want this value and i'm gonna make that absolute this so this is the top 10 values and then all i have to do down here is just say give me the sum of those values so this is the sum of top 10 values from this range right not that difficult and this so what i've done here is actually probably solution number three in a sense of how quick this can be because this would be much easier if i just used row 1 to 10 or even better if i would have used a function called sequence give me 10 that's it now this one says it can't spill because there are values in here but if they're not you see it does the same thing and then this could be really really cool because i could just say do it in a sense of a dynamic array there you go and now i have the solution but even this could be simplified by just writing one simple function so going equals sum of a large function this is the array and i want you to use the sequence function on this array there it is so like this and like this and that is it that is the sum of top 10 values in that range now if somebody would have done this you know kudos to you but then again you know that was deep into the formula and on the other hand someone could have simply gone okay so top 10 values i could just highlight the top 10 values like this and now all i need to do is select the top 10 values one way or the other and here it is so the answer is 256 291 and since i didn't say give me the sum of top 10 values in this cell that is also a correct answer right remember when i said there is no just one correct way to do it just get to the answer right so there are other ways that this could be achieved but of course i would much prefer to see someone using the large function over here if somebody would have done that then the build up of this would have been do you know the small function and maybe i would even go as far as to say what would the sum of top 10 values be if i would have rounded up all those numbers to the closest multiple of 100 right so checking if somebody has heard of ceiling floor mround really digging deep into the function basket of excel and and seeing you know how much of that is in the applicant's head okay that's question number three now let's move on to number four number four is very much in keeping with this day and age the abundance of data the need to analyze that data the need to grab that data and get it into excel it's all kind of combined into task number four now basically what i would be hoping for here and what i would be checking for here is power query power query is to me hands down one of the greatest things if not the greatest thing that happened in excel in the last 10 years having said that it's almost been 10 years right it's 2021 and power query came out in 2013. so checking for this also tells me how much a person sitting opposite to me is keeping up you know and not just keeping up um how much they're brushing on the latest and greatest and how much they're incorporating that into their workflow and i find that extremely important in in an applicant just the the sheer willingness to learn i think that's extremely important and this checks for that and it checks in a way that i give them four txt files now these txt files they have sort of multiple layers to them they look like this so the layers within this are there are days that are not us format so people need to deal with that and i specifically designed these in a way where the the number of blank rows above the desired data set varies from file to file so this is the next one and you can see this one only has three blank rows and this is one more this one only has one whereas the original one the first one had multiple blank rows above the data set combining these requires effort now there's a trick here and and the way this is spelled out it says join for txt files but expect that more are on the way right so kind of copy pasting the txt files content into excel and then manually deleting the the top rows and pasting them below just doesn't cut it okay so what i would want to see here is i would want someone to take initiative and say okay so we're gonna get all the files from this folder and then if we get extra files we'll just add them to the folder and we'll set up master table that comes from all these in a way that it just includes the new files on the fly right and we do that with power query so the way we do that is say data get data from file and in this case from folder so we're going to say from folder now i'll recopy the folder path because otherwise it would be very very tedious to get to and what i'm going to say is these are the files now i wanted to combine them now if i just combined them i would not be a good choice given that there are rows at the top that i don't need and there are certain columns that i should take a look at and maybe somehow tell power query how to deal with them so what i'm going to say is combine and transform so combine and transform data and what this will give me is it will give me which is my sample file now usually what you would go for is you would go for the last one right because the last file is the closest to what you need in the end but i wouldn't you know judge if an applicant picked just the first file the way it was given i wouldn't judge by this what i would judge by is if they're even opening power query if they're even aware of power query's existence and then in power query if i would see things like okay so now we need a step let's call it base and then let's deal with the variable of blank rows at the top and how we're going to deal with that is we're going to add a column we're going to call this column the index column from 1 and and what i'll do is i'll bring this one to the start just because it's easier so to beginning and then i'm going to filter on the first column all the way to the id why well because that's where my data starts that is the header of my table and this is what i need the id of this is exactly what i need it might as well be called index and now i'll go create a bank step go back to source and remove top rows how many well i'll just ballpark it here i'll say eight why because this hardcore number does me no good what i need is whatever i've saved up here in the index step that's how many rows i need you to pull down and with that i actually remove the header row which is not a good idea so i'll go minus one and once i have this now i'll say use first row is headers right because i know i'm over there i'm going to ignore the change type step and over here with the dates you can see that the dates are written in a well i'm gonna say normal way day month year but not the way the us does them so what i need to do here is i need to say change the type using locale and the locale that i'm gonna use will be this is a date but the way and i'm just gonna go with slovenia because that's the way we write dates and you could have gone with any other country that does them like that but so this is now because it turned them into the us dates so this is now january 1st 2016 but they work they are dates they're legit dates and once i have this i could have gone through the other ones but i will not do that um i now look at the end query which i'm going to rename into master table and all i need to do is remove the last step and this is it so these are all four files combined so this is the data from all four files treated in the same way that i thought power query here and that that is something i would expect for an applicant to do maybe not so much expect as hope for and if somebody did it like this and says close and load over here and just kind of load it okay so now we have it loaded on a new sheet let's load it exactly where it should be so i'm gonna delete the sheet that it created for me i'm gonna go on question four i'm gonna say load two a table existing worksheet start here and okay and this is exactly what is required off of this question would i expect or let me put it differently the percentage of applicants that i would expect to do exactly what i showed you now is fairly small fairly small i would expect someone to shoot up vba i would expect someone to use the legacy connectors to txt files which wouldn't help any because they would still need to connect to all four and then manually remove the rows and all that and then put them together but that connector actually has the the date format you can specify the date format within so that would also be a legitimate way of of doing this but i would be going for this and again not just in a sense of can he do exactly this but does he keep up with or she of course um does the applicant keep up with the latest and greatest that that's that's the goal i have in mind with this question and the the buildup potential of this is endless simply endless you can build this up in in so many ways you can do a calculation of an extra column you can do amazing stuff with this you can go for a pivot table out of this where you would say i want you to add up time and you would see if they actually know the trick of formatting a cell so it shows you time greater than 24 hours and stuff like that you can you can build on this endlessly okay question number five i hope you're all having a good laugh over the title i had one too but i i think that's so important and this is actually a build up of the last question right the last one was going for do they know power query well this one goes a bit further than that because you can solve this with three simple formulas three simple formulas that's all you need to solve all this uh let me tell you what is required here so from the table that we got as a result of combining those txt files what i want here is give me a list of unique planes so give me a list of all unique items in column plane and then give me a data validation drop down list that lets me choose any of these planes and from that when i choose a plane what i want over here is a subset of my original data filtered to exactly this plane that i chose over there now the reason i love this is because it's one of those that has multiple solutions multiple ways of doing it and i'm sort of gonna go through the you know let's call it time go through time in in excel and and tell you how i would solve this so a list of unique planes well i could do that with a pivot table you know i would create a pivot table off of this so let's just do that let's create a pivot table like this and if i put in plane what do i get well this is the list of all unique planes right simple but this solution is so you know 2003 and the worst thing about it is if i go about it like this there's no way to get only that data in here or at least no easy way there are ways but no not good ways and then i'm also going to have issues if this is a pivot table i'm going to have issues with creating a data validation that is dynamic well reason number one pivot tables are not dynamic so you have to refresh them but having said that if you refresh a pivot table how do you make this dynamic and if you go off a pivot table you would need to create named ranges with offset you know it's just it was the way we did it in 2003 but we have progressed since so that would be a solution i would sort of upload but not really now the second solution that one would be kind of better is simply doing it with power query because you can do all these things well not the data validation data validation will always be data validation but you can do these two with simple power query what you would say is start with this table so data start from this table and once i have the table in power query what i would say is i only need the plain column remove other columns and then in this one remove all duplicates and then if i wanted to let's sort it ascending and i get this i would consider this a superior solution to the pivot table one simply for the obvious reason that this means you're 10 years ahead at least of the person using the pivot table solution but if you think about it what i said before was if i use the pivot table solution i'm still gonna need to refresh it and power query just the same if my data changes i still need to hit refresh and do it that way but now the way this should be done is by simply using the incredible new formulas from dynamic arrays so what i would say is give me a unique values from this column of this table and that's it okay and now i have my list using the dynamic arrays let me just point out this is a feature of office 365. you can't use this formulas in other office products or in excel 2013 2016 and so on you could simulate it by using the array formulas but this is so much better than that but now let me show you something really really cool i'm gonna take a picture with my camera of this and i'm gonna paste that picture right here there you go and now take a look at this this is why this is such a brilliant thing if i add a new so let's add the the giant bird so it's 380 i think did you see it works without any refreshers it just adds that item to the list why because it's a formula it's the formula engine of excel that does this and it gets refreshed every time you enter something or refresh the entire workbook and now for the data validation list if this was either a table derived from power query or a pivot table creating this list would require you to create a named range well if it was a table derived from power query you could actually if you did it correctly you could just select the the column and it would work it would be dynamic but now that i have the formulas now i can just go data data validation this should be a list and this list should be defined by this cell and everything that cell defines this is a new syntax and in excel and it means everything that this cell defines and it's all these items and you can see that right here right so now i have a list of this and it's dynamic it's dynamic because it shoots off this formula which will refresh immediately something changes in my data and if i've done this and if i now select a single plane right here now i can do this now i can go equals filter and what i want you to do is to take the entire table [Music] so the master table and the filter you should apply is where a plane column equals whatever i've chosen right here right enter and that is it and a single formula that does everything i need all right and if i now choose the 380 that's the row that i changed just before and if you haven't seen dynamic race ever this should be the greatest thing you've ever seen probably today this week this month maybe this year and i would so be searching for that in an applicant i'm again not claiming i would expect someone to do it like this but if someone did i would just say sign here and i think that's the the point of having questions that allow the wiggle room where you can still solve it the way you would in 2003 you can solve it the way you would in 2013 but then you can solve it the way you would in 2021 and if somebody does that it gives you all the information that you need about that person at least as far as excel is concerned and their knowledge of excel okay this was a long one uh if you have extra ideas for what to include leave them down in the comments below if you want the files that i use for this if you think you could use them or if you want to go through the process that i went through just leave a comment below and i'll send them to you and thanks for watching this was a long one thanks for sticking around and i'll see you on the next one [Music] you

Channel: Excel Olympics

Views: 11,798

Rating: 4.8466454 out of 5

Keywords: excel interview questions, excel test for job interview, excel interview test, excel interview, excel test questions and answers, excel test for employment, microsoft excel interview questions, excel test for job interview 2020, excel test for job interview 2021, excel interview questions for business analyst, excel interview questions and answers in english, excel assessment test for job application, interview questions to ask employer, MS Excel, Excel Olympics, Gašper Kamenšek

Id: 0ZryTtce1sE

Length: 43min 40sec (2620 seconds)

Published: Mon May 17 2021