Advanced Excel Test for Job Interview
As part of Advanced Excel Assessment Test companies would like to test candidate's skills in the areas of Data Import, Data Conversion, Power Query, Pivot Table, Power Pivot and more. They also would like to ensure that candidate can actually perform functions in Excel, instead of just answering the questions. This is why most companies are using interactive test format: most recent test ask candidates to actually perform steps in Excel spreadsheets.
In this tutorial you will complete 15 steps test, for imaginary online eBook sales company:
Client manages eBook Sales from the web site. Client provides you an export PDF file with the list of transactions, as a PDF file exported from financial system and request for you to analyze the data
Step by Step Tutorial
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 and follow the steps below;
- I'm going to launch Microsoft Word as a separate application create a blank document
- And then open this file because word allows us to open PDF files by browsing to that look and click on the filename.
- 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.
The next step, we would need to professionally format imported data to make it presentable. In our sheet you can see that 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. So, what we'll do to format it professional, we'll do couple things which include; We will cut the title from the column header and you see there are 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 font. One of the fonts is Calibri or Tahoma that comes already with excel.
Now let's expand these columns to make sure they feed the size wise so we only have one line per row 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.
It looks like column F is extra column as well. So, we're going to need to delete this column. So, we s do another selection ctrl shift end and will just make the selection smaller holding ctrl shift we’ll use the arrow to the left to reduce the selection size
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.
In this step we need to describe the data we have imported into Excel. There’re a couple of 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 date, 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 with 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 and 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.
NOTE: Another way to format a cell is by using format painter.
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.
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.
- Then for the entire column eBook, we use Text datatype.
- Then for price column, we use datatype currency.
- For Purchaser Name’s column, we will use text datatype.
- And Purchaser Address column will be change to text datatype.
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.
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 end 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 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 okay.
We need to split name column into first and last name using the 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 purchaser’s name.
To do that let's select column E right click and click insert to add a 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.
You can use text to column feature select column D and then click on the text to column and it prompts if I'd like to use text to multiple columns and what kind of delimiter we have.
So, we select delimited then we'll click space, make sure to select that then we click Next.
Now, we’ve 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.
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 and column E we'll just call last name
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 address without city and only contains street name and zip separated with the slash. 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.
Power query is launched and inside power query, let's find this column purchaser’s address and we highlight the column. Do a right mouse-click and here we would use split column feature and who will be splitting columns by delimiter.
Power 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, we change that and use space slash space. As a result, it splits both values.
We can save and loaded back into Excel. Excel created a separate tab for this which we'll call sales data 2
NOTE: 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.
Have in mind that as part of this question, you are being tested for the latest features of Microsoft. 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 which is under the data tab. Select our zip column and click on geography. It will basically go to Bing and will try to convert and determine if this is a valid zip code.
To manually resolve issues that occur, 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.
To add city, we need to keep this column G highlighted and then click the plus sign and that's the coolest new feature of excel.
Ultimately what happens, it went to query each of this value, 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.
Resolve Zip Code Conversion Errors by adding leading zero
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 going to select control one which opens up format cells' box
And then I'm going to 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 going to 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 it would resolve all the errors.
NOTE: 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.
To do this, let's navigate to the sales data tool 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.
To do this, let's navigate to the tab sales data tool - we can click on any cell in the table and we need to click insert and then pivot table.
It prompts us which table would you like to create pivot table from which is table range. We'll create it in a new worksheet I will click OK.
NOTE: My students often ask me 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 set specific business questions. You can always go back by pressing ctrl Z this is undo function in Microsoft Excel.
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.
Sometimes employer asks you to take a screen print of this. To do that you just need to highlight the area and use snipping sketch tool on windows 10 or you can just copy the data and you can copy a picture as well.
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, we need to select our pivot table and we just need to select price and then we need to select month.
It shows us total sales by months.
Copy the data and then copy that as a bitmap copy as picture or you can copy as values doesn't matter.
We would need to build the graph that shows sales by month. To do this we select all the areas with the months and prices and 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 did not specify which chart, they want to use
Column chart represents sales the best. So, we will just use the first clustered column and this represents sales by month.
Call to Action: 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.
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 on a new worksheet.
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, we’ll not be able to do that. We'll get this error message.
So, 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. We'll rename this row as state and this would be sales.
Now we will click insert map, it built a map which shows sales by state in the United States.
We'll rename the chart as Sales by state
This is a tricky question as a lot of last questions are in Excel interview or assessment test. There reason it's tricky is because the data we're trying to massage doesn't exist yet in the table. We would need to calculate it and 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.
The trickiness of this question relates to the fact that we need to calculate the county. To do calculate the county, click the plus button and add admin division tool 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.
The steps include;
1. Create a new pivot table. We’ll create pivot table in the new worksheet.
2. In order to display the new calculated value, click on the pivot table and select refresh.
3. To calculate which county in California had the largest sales select price, select state, select the county itself.
4. You’ll 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.85
Watch Video Tutorial
Download Files Used in This Tutorial
Download files used in this tutorial
Click here to download or copy and paste link below into your browser:
My goal is to help people with their education, certification tests and assessment test needs and this is why I have created this web site. Make sure to stay in touch and Subscribe to my YouTube Channel and join my email list - this way you are not missing out on any new episodes.
Thanks again for your support, good luck learning and wish you all the best!!!
Below is the list of resources you could use to get prepared for the Excel Interview:
All the best on your interview!!!
Top 50 Excel Assessment Test Questions and Answers
Top 50 Excel Assessment Test Questions and Answers PDF EBook teaches you everything you need to get ready for Basic and Intermediate Microsoft Excel Interview and prepare for Excel Assessment Test. You will practice most frequently asked questions on the exam and will learn what to do when you arrive at the exam testing center, how to manage your time when taking the exam, tips to manage your time during assessment test, tricks for answer questions in different formats and much much more.