Excel Interview Questions For Data Analyst

data analysts help retrieve gather data and organize it to help end-users make business decisions a lot of times during the interview companies are evaluating skills of data analysts using Microsoft Excel over the years Excel became very powerful supporting a lot of data import tools a lot of different ways of storing the data and a lot of ways of reporting the data through reports and dashboards and can easily be used to understand how skillful data analyst is in this video I will show with you simple Excel assessment test that could be used to gauge skills of data analysts I will also share with you some insights related to data analyst job responsibilities which you might be asked for as part of the interview 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's 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 file name word asks me word will now convert PDF to an editable Word format this may take a while the resulting more 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 contained lots of traffic will 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 chyli it all the columns and then select 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 one of the questions almost guaranteed in any data analyst interview is what is the data analysis itself make sure to watch the entire video as I will provide you with the comprehensive answer later in this video 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 will 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 and 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 and it will just make the selection smaller holding ctrl shift we'll use the arrow to the left to reduce the selection size and we need to do that so we can apply the borders make the old borders and make the single border and we also need to highlight the header to make it look professional and we use the fill 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 row eight when thinking about the data is data analyst it's important to think about input storage and output typically you get data in a different formats and you may need to import it into Excel directly or by performing some transformations the way you store data in Excel is also very important best way to store data in excel by the way is to use tables data output in excel could be a summary pivot table could be a chart could be a report or could be a dashboard the output is very important because the effectiveness of the way you present the data to the end users determine how effectively can they make decisions based on your data in the next step we would 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 bate 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 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 a column called written answer so now I can cut this value and paste it in here format it so it aligned to the top aligned to the left adjust the column little bit and this is our answer imported data contains a list of ebook 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 we are 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 datatypes here to do that you need to select the entire column and this is the date datatype 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 four 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 would 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 we'll click OK and now we can mark this step as complete in addition to excel questions during the interview sometimes you might be asked questions specific to data analysis one of these questions might be how do you assess quality of the data model even though the answer might be different I'm gonna list you some specific questions you might ask to assess quality of the data model for example can model be easily consumed by end-users your customers to help facilitate decision-making process can model easily support generation of output format reports or dashboards combination of these questions will help you to assess the quality of your data for future sustainability and also for the best way to present data to the end-users in the next step we would 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 the column e right click and click insert if 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 companies profile for example if this is a financial company you might impress the manager with your knowledge of Excel stocks data type 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 Cooley to accomplish this let's use power query feature and now let's analyze purchaser address looks like it has a dress without city and only contains street name and zip separate it with 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 it's not selected by a delimiter and pal query is very smart so it offered us slash as the delimiter but as you can see much better split would be space / space so let's change that and we'll use space / space as you can see it's played at 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 2 which will call sales data - 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 will 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 as a new column keep in mind that as part of this question you are being tested for the latest features of Microsoft Excel 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 will 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 US 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 this step and before converting it into geography datatype 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 type 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 data type and hopefully it would resolve all the errors and you see 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 dis 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 the zip code and add it as a new column to do that let's navigate to the sales data to 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 - or 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 sheep will click okay and this is the pivot table that was created by default nothing is selected no fields 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 now 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 we use snip and 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 at as picture as well you click OK and 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 one of the tricky questions you might be asked as part of your data analyst interview is what are the biggest challenges that you might run into when doing data analysis even though it's situation is different I'll list you some criterias that you might present to the interviewer for example one of the challenges might be importing the data into so for example importing the data might be a challenge depending in which format you will get the data this also gets exaggerated and more complicated for the bigger data volumes especially if you're getting the data from the big data data stores you might also run into data cleansing challenges for example you might have a lot of duplicate values so decision what you do is those duplicate values spelling mistakes is another challenge how do you deal with those do you just leave them untouched or do you fix the data that's the question that you would have to answer as data analyst another thing is working with multiple data sources how do you ensure referential integrity if you bring data from multiple data sources do they necessarily have unique keys that will help you join the queries when you arrange the data in excel or is that a challenge or is this something that you need to add as an additional step in the process to ensure this referential integrity quality of the data we've talked about spelling mistakes and unique keys what about incomplete data where you get blanks something that hasn't been populated maybe by an user if this was a form data that was collected what do you do with those blanks how do you make sure that they get analyzed as well as part of your reports and last but not least is what are the best ways to present the data Excel provides multiple ways to present the data for example you can generate pivot tables you can do summary reports you can do charts you can do dashboards and maybe come up with something unique of what you would like to do based on the data types how would you pick the best format for the output to help decision makers your end users to answer business questions 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 us picture and then paste it right here on the screen and type that this is sales month 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 a 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 do we want to use and we can just start with recommended chart and column chart represents sales the best so we will just use the first clustered column and this represents sales by month we'll 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 test 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 pivot table create 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 and now back to the original questions I asked earlier in this video what is the process of data analysis data analysis is the process of collecting Cleansing interpreting and transforming and model and data to gather insights and generate reports to help make business decisions typically data analysis can be broken down into multiple steps first step is identification of the sources as part of this step you look at different data sources available in organizations and try to understand the ways of extracting and importing the data from those data sources data import is where you extract the data from those sources identified in the first step and then bring it into Excel to get it cleansed and prepared for analysis in the step of data import you also need to make sure that you understand referential integrity how you gonna join the data and make sure you understand the ways of dealing with the blanks and understanding what should be removed maybe from the data that you bring it into the analysis the step of data analysis is where you look at the data once it's ready once it has been important trying to find patterns and answer business decisions in this step you might also decide to build the model depending upon the data complexity a lot of times you doing data analysis in the iterative way for example in the first wave of iteration you might just bring in the data and look at it initially and then you realize you're missing something else to make a decision then you go to iteration 2 where you bring additional pieces of information to help you make better decisions a lot of times also you can run it in the agile way where are you doing ongoing because business situation is changing and you need to improve your data analysis and using iterative way is the best way to ensure comprehensiveness of the data model as part of analysis stage and last but not least step in the data analysis is report generation in this step you look at the best ways to present the data to the end-users to make sure you facilitate quality decision making process 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 and the highest sales volume was for Los Angeles County thirty-eight point eighty-five dollars 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 Lex mentioned make sure to check the description of this video to learn more in the topic make sure to check my ebooks and online training courses all the best when you interview an excel assessment task 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: 64,782

Rating: 4.9520845 out of 5

Keywords: data analyst interview questions, data analyst interview questions and answers, data science interview preparation, how to prepare for data analyst job interview, data science interview, data science interview prep, data science interview questions, data science interview tips, the career force, prepare for analytics questions, data analyst interview, data analytics, data analyst, data science, data scientist, data scientist interview questions, big data

Id: b9XKNjPEJsE

Length: 31min 20sec (1880 seconds)

Published: Sun Jan 05 2020