How To Pass Advanced Excel Test For Job Interview

in this video I'll show you an example of advanced Microsoft Excel past I'll show you how to import transactional data into Excel I'll also show you how to cleanse the data using text to column feature of Excel and also power query we'll also look how to use pivot tables and a lot more in this advanced Excel test for job interview we will complete all the steps listed on the screen typical employer provides you with the background in this case client managers ebook stores from website and client provides you an export PDF file with the list of transactions as a PDF file imported from financial system to request you to analyze data now in the step 1 we'll import data from the PDF file which is provided as a separate file this is the PDF file that was provided which was opened in Adobe PDF Reader typically PDF files are not editable and there is no direct way to import them into Microsoft Excel to do that we would need to use Microsoft Word as an intermediary to bring this data into Excel I'm gonna launch Microsoft Word as a separate application create a blank document and then open this file because word allows us to opening the PDF files by browsing to that look and click on the filename word asks me word will now convert PDF to an editable word format this may take a while the resulting word document will be optimized to allow you edit the text so it might not look exactly like your original PDF especially if your original file contains lots of graphic we'll click OK on this message and word will bring all this data from PDF file into Microsoft Excel Word mimics the table format of PDF file so we should be able to just select the table to do that you just highlight all the columns and then select the copy once we go back to excel we'll create a new tab in Excel and we'll paste the data now we can mark step 1 as complete in the next step who would need to professionally format imported data to make it presentable let's go back to our sheet where we have data imported and you can see that the first column is an extra column here in Excel because date has no other dates imported and you can try it for yourself because you can download all the files listed in this video just make sure to look in the description so what we'll do to format it professional we'll do couple things will cut the title from the column header and you see there are other other headers here as well we'll do a cut we will paste it here in the next column B and then we'll delete the entire column a one of the best ways to professionally format the data is to uniquely identify column headers you can then highlight them make them bold and change the background to make sure you emphasize them in your final document another tip is to use professional fund one of the font is Calibri or Tahoma that comes already with excel now let's expand this columns to make sure they feed the size wise so we only have one line per role and this does not expand into multiple line this way it looks professionally as well so we'll pick the right size now let's select the entire table in Excel and to do that we'll use shortcut ctrl shift end to get us to the last cell of this range and it looks like column F is extra column as well so we're gonna need to delete this column and you see once we delete it it changed the formatting looks like it had some extra information some unnecessary information so we'll expend a little bit column e by looking at the data and also a row 17 had a little bit longer address so this helped as well let's do another selection ctrl shift end it will just make the selection smaller holding ctrl shift will use the arrow to the left to reduce the selection size and we need to do that so we can apply the borders make old borders and make the single border and we also need to highlight the header to make it look professional and we use the feel of some light color this is whatever your preference is and I think it looks much more professional now this way we can mark step two as complete we will do it by applying the same format painter and reapplying it to the ro8 in the next step we need to describe the data we have imported into Excel so let's go back to our table I'm gonna make it a little bit larger in there couple techniques you can use to describe the data number one is obviously look at the data and understand what's in the columns so we have bathed ebook name price purchaser name and purchaser address so it's a list of transactions from the e-book sales and you can also look at the background and in the background it looks like it's the same data a similar type of data described just to confirm that your hypothesis is correct one of the best ways to describe the data is to understand what data represents also you can look for the column headers a lot of times they contain a lot of valuable information look common sense and then come up with the description that you can write and provide to the interviewer sometimes employer might ask you to put the written description here because there is no way for you to communicate this on the test so you might want to type it in and put it into the column where they require to put you the answer I typed in a description or now you can use wrap text which will make this column very large so I might try to create another column here and I created column called written answer so now I can cut this value and paste it in here format it so it aligned to the top align to the left adjust the column little bit and this is our answer imported data contains a list of evil transactions and includes date of transaction name of ebook price it was sold for and named an address of the person who purchased it and now we can mark step 3 as complete to do that we'll copy the value using format painter from the cell c8 and then we will paste it into the cell c9 in the next step we would need to change tab title to sales data and also assign data types to imported columns in Excel selecting correct data type is very important especially when we convert the data from the imported file into Excel table later as part of this exercise to do that first let's change the tab title to do that we will click on the tab and you see it became editable and we'll just type sales data and in the next step we will need to assign data types here to do that you need to select the entire column and this is the date data type so and you see right now it's general so we would need to change it from general to short date that's the most appropriate data type this is the text because this is the name of the e-book so we'll change it to text column C we'll change the currency that's the most appropriate type for this column column D will will change to text and column E will change to text as well and now we can mark step 4 as complete a lot of questions in this video are covered as part of my ebook if you're getting ready for Excel interview or assessment test make sure to check out my ebook store to make sure you get prepared faster in the next step we need to create excel table from the data to do that let's navigate to the sales data tab we need to highlight the entire table so ctrl shift and allows us to highlight it and then we can either use ctrl T or in the data tab we can just use insert and then click table and you see the shortcut for this is ctrl T and then we click table and then it prompts us to confirm the range for this table we double check the range and it looks nice and it also asks you to check my table has headers which our table does the row one contains header so click okay and now we can mark this step as complete in the next step we need to split name column into first and last name using the text to feed text to column feature of Excel to do that let's analyze the column and it looks like we have both first and last name in the column D and it has purchased her name to do that let's select column E right click and click insert it added the new column e next to the column D so which would help us to put this extra value that will be created right into column E now let's use text to column feature it's part of the data tab so I selected column D and then click on the text to color it prompts me if I'd like to use text to multiple columns and what kind of delimiter we have we have a space as the delimiter between first and last name so we select delimited then we'll click space make sure to select that then we click Next now it split them accurately we want to change the general data type into text data type because both of those are text and then we click finish and it shows that there is already column E in place and shows us the message there is already data here do you want to replace it if we wouldn't have created column E it would have overwritten the column F so that's the reason we created column E and we'll click OK on this message and you see now it added the values so instead of purchaser column D we will call first name M column E we'll just call last name and we can mark this step as complete a lot of times my student asked me what are the features of Microsoft Excel I can demonstrate to the interviewer to impress that the most even those hard to determine which particular feature would be valued by hiring manager typically I recommend you focus on the features that's important to the company based on the company business and company's profile for example if this is a financial company you might impress the manager with your knowledge of Excel stocks data or if this is manufacturing company you might add value to the organization through your knowledge of pivot tables and power query in the next step we would need to split address to separate address and zip using power query to accomplish this let's use power query feature and let's analyze purchaser address looks like either has a dress without city and only contains street name and zip separate it was the slash now to use power query we need to select this column navigate to the data tab and then here click get and transform data and we'll be transforming it from the table slash range so let's click on this button it launches the power query and inside power query let's find this column which is our purchasers address now we need to highlight the column here do a right mouse-click and here we would use split column feature and who will be splitting columns by delimiter let's let's selected by a delimiter and Paul query is very smart so it offered us slash as the delimiter but as you can see much better split would be space slash space so let's change that and we'll use space slash space as you can see it splitted both values now we need to change the name so this would be Street this would be zip and we can save and loaded back into Excel Excel created a separate tab for this this is our original data sales data and this is the sheet to which we'll call sales data to and now we can mark this step as complete I need your help what are the questions that you see as part of Excel interview or assessment test can you please post them in the comments of this video this will help me answer those questions and together we'll be able to help others to get prepared for the interview faster in the next step we would need to determine the city based on the zip code and add it as a new column keep in mind that as part of this question you are being tested for the latest features of Microsoft so geography data type was just recently introduced in the application to do that let's analyze the data now we have sales to data so we have a zip as a separate value and right now zip is the text value to do that we need to use new feature of Excel called geographical data to use this feature we need to navigate to the data tab select our zip column and click on geography data it will basically go to Bing and we'll try to convert and determine if this is a valid zip code which had dead with some exceptions for some zip codes it wasn't able to do that so we need to manually resolve it I'll show you how to do it for one zip code we just click on the question mark and it shows we need help with this text and here in the data selector it shows us the zip code it's only four digits in u.s. zip code is five digits so we need to add a leading zero and then we will click enter and it says that this is 0 7 6 31 is Bergen County in New Jersey which is correct we click select and this result this ideally you would want to go and resolve all of this for the purpose of this video I'm not going to do it because our step is to really add a city and to do that we need to keep this column G highlighted and then click the plus sign and that's the coolest new feature or excel ultimately what happened it went to being queried each of this values determined that this is a valid zip code and now it knows what the city is and we click Add column and we know that it can add city which is one of the values and you see for the ones where it was correctly I recognized the value of the city was determined accurately we can obviously resolve all this errors manually but there's another way to add leading zeros in Excel so let's roll back by using undo for each of the steps and before converting it into geography data type I am going to add leading zeros to the zip column to do that I'm going to select the column I'm gonna select control one which opens up format cells' box and then I'm gonna click custom in the tied box I'm going to type five zeros which would represent the format that it's all numeric and it should maintain five digits I'm gonna click OK and you see that it added leading zeros for the values that were missing and now we can convert zip into geography datatype and hopefully it would resolve all the errors and you see it did resolve all the errors and now we can add a city and will expand city column in the only place it didn't resolve it is for the this value so we would need to look into this but looks like for the remaining zip codes it was able to successfully resolved and find this city now we can go back and mark the step as complete if you are getting ready for the interview where you will be asked Microsoft Excel questions make sure to check out my website to learn more about additional resources available to get you ready for the interview in the next step we need to determine state based on a zip code and add it as a new column to do that let's navigate to the sales data - tab we will select the zip code and we'll click a plus button and one of the values is the state and will click the state and as you can see Excel determined by querying the Bing search engine states for all of the zip codes and we can mark this step as complete in the next step we would need to create a pivot table from the data in the sales data to tab to do that let's navigate to the tab sales data - we can click on any cell in the table and we need to click insert and then pivot table and it prompts us which table would you like to create pivot table from which is table range which is correct we'll create it in a new worksheet I will click OK and this is the pivot table that was created by default nothing is selected now but all the fields from the table are listed here and we will be using those in the next steps of the exercise but for now we can mark this step as a complete but before that let's rename that this would be our pivot table tab and thou can navigate back and mark the step as complete my students often ask me what is the best way to learn features of Microsoft Excel faster I typically recommend experimenting the best way to experiment is to download simple set of data or maybe use copy of production data using your data samples you cannot try to solve real business problems and then sir specific business questions just remember you can always go back by pressing ctrl Z this is then to function in Microsoft Excel in the next step we would need to determine total sales by state using pivot table to do that let's navigate to the pivot table and it's very simple using pivot we just need to select price because this is the total sales and then state itself and that shows us the sum of price we can highlight it and mark this column as a dollar sign but this is the total sum times employer asks you to take a screen print of this to do that you just need to highlight the area and use snoopin sketch tool and windows 10 other tools on the Mac somehow take this screen print or you can just copy the data and you can copy a picture as well you click OK in here if you're required to paste and demonstrate the values you just paste it here and that would be the values from this exercise and we can mark this step as complete in the next step we need to calculate sales by month using power pivot in Excel so we navigate to the tab where we created the power pivot and here what we need to do we need to select our pivot table and we just need to select price and then we need to select month and it shows us total sales by months you see the total here matches the totals for salesforce state that we calculated before so we might want to highlight it and select dollar value sum of price copy the data and then copy that as a bitmap copy as picture or you can copy as values doesn't matter copy s picture and then paste it right here on the screen and type that this is sales and we can mark this step as complete in the next step we would need to build the graph that shows sales by month in the previous steps we've already built the pivot table that shows sales by month now the only remaining task is to create a graph to do that we select all the areas with the months and in prices in sum of totals for the particular month and then we just go to insert tab and then we will pick any recommended charts because the instructions do not specify which chart they want to use and we can just start with recommend the chart and column chart represents sales the best so we will just use the first clustered column and this represents sales by month will organize them a little bit so we will move their sales by months a little bit to the right and we will mark this step as complete if you liked this video make sure to click the like button and subscribe to my channel for more tips and tricks on how to get prepared for Excel interview and assessment tests in the next step we would need to create a map which shows sales by each state in the United States we will start here by looking back at the sales data and created a new pivot table from the sales data to do that we'll click on the insert tab and click pivot table we'll put our pivot table in the new worksheet and create it from the table in the pivot table we'll select state and price and it built a table to show sales by state if we try to directly insert map chart will not be able to do that we'll get this error message so what we need to do we need to copy the and paste it as values so to do that we'll select the PivotTable crepe copy and paste as values right next to it we'll rename this row as state and this would be sales and now we will click insert map it built a map which shows sales by state in the United States we'll rename the chart we'll call it sales by state it shows state by state in the United States and we can mark this assignment as complete in the last assignment we need to calculate which county in California has largest sales volley this is a tricky question as a lot of last questions are in Excel interview or assessment test there isn't it's tricky because the data we're trying to massage doesn't exist yet in the table we would need to calculate it to answer this question we would need to look up the county values from the zip code and then use County data that was calculated if we look at the sales data we don't have a county here so the the trickiness of this question relates to the fact that we need to calculate the county it's easy to do you just click the plus button and add admin division to and calculate the county based on the zip code so now once we've calculated the county we need to build another pivot table which shows and calculates sales in California by county let's go and do it step by step to do it let's create a new pivot table we'll click on insert tab a select pivot table will create pivot table in the new worksheet and here by default it doesn't show the new calculated value so what do we need to do we need to click on the pivot table and select refresh so now you see that we have stayed and the new value that we've added admin division for County to calculate which county in California had the largest sales let's select price let's select state let's select the county itself and you see that Excel helped us break down the data and California is the second state listed which shows all California counties the highest sales volume was for Los Angeles County 38-point $85 and now let's go ahead and mark this final assignment as complete if this video was helpful make sure to click the like button and subscribe to my channel for next mentioned make sure to check the description of this video to learn more on the topic make sure to check my ebooks and online training courses all the best on you interview and Excel assessment ass make sure to check out my other relevant videos and subscribe to my youtube channel we have a lot of great stuff planned in the pipeline and I don't want you to miss any of it and if you'd like to get notified about all the new stuff that are coming out make sure to subscribe to my email list as well all links are here in the screen make sure to click to stay in touch thanks again for watching

Channel: Online Training for Everyone

Views: 463,128

Keywords: excel test for job interview, advanced excel test, excel interveiw questions, Basic Excel Test for Job Interview, excel interview test, excel job interview test, excel tutorial, excel interview questions, excel interview, interview questions, excel formulas and functions, excel basics for beginners, indeed excel assessment test, indeed excel test, ikm excel assessment intermediate, ikm excel 2016 assessment answers, skillcheck excel test, skillcheck excel 2016

Id: 9_5gQe6sKC0

Length: 24min 57sec (1497 seconds)

Published: Mon Dec 16 2019