Advanced Excel TutorialsExcel Test for Interview CandidatesMicrosoft Excel

Advanced Excel Interview Questions and Answers: Typical First Question

advanced excel interview questions and answers pdf,

As part of interview process a lot of companies test candidate's knowledge of Microsoft Excel by asking them either to complete Excel Test or work with the real data in application, to complete certain assignments. In this post we will look at typical initial questions presented to interview candidate and how to answer them.

If you would like to download XLSX Files used in this tutorial feel free to navigate to the end of this post or click here to download.

Typically employers use workbooks with large sets of data (100K+ rows or more) which wouldn't be perfectly formatted to simulate real working conditions. One of the typical question asked in this type of test: you need to create Unique ID for each row, so you can uniquely identify each one of them for executives. In this tutorial we will look how you can this interview question and assessment test questions and I will teach you how you can succeed on such an assignment. We will look at the most frequent version of the question and we will also look at the different variation of the question being asked during the interview. At the end of the post you can watch video tutorial with all the answers and if you would like to download source files to follow along with the video.

Interview typically starts by sharing with candidate the background behind the data to help candidate better understand how to answer interview question:

Question Background: Cataloria Inc. is a manufacturing company headquartered in the United States which has plants in the U.S., Germany, China, India and Japan. Currently, it manufactures all products at all facilities and only sells at the countries where products are manufactured.

 

Question 1: Can you describe data in the “Sales” Excel tab? What does the data represent?

Can you describe data in the “Sales” Excel tab? What does the data represent?

As the name of the tab implies, this is the sales data. each line item here is a unique transaction. And it shows the date when transaction occurred, shows the Country, shows Manufacturing Costs, shows Sale Price, shows Gross Sales. When you multiply “Units Sold” by the “Sale Price”, you will get to the “Gross Sales”. Then we have “Discounts” column. We have “Sales” which match “Gross Sales.” COGS stands for cost of goods sold. And then it shows the “Profit.” Profit is typically calculated when you subtract cost of goods sold from sales.

Answer: “Sales” tab displays sales transactions. For each transaction, there is a profit calculation since manufacturing cost data and cost of goods sold data is available.

I would recommend you use Pareto rule while answering the questions, spend 20% of the time to get 80% of their results and move on to the next question, this will help you successfully complete the test. The key is to complete the test before the deadline as most likely deadlines is not going to be extended. And if you have as much as possible ready at the end of the test, it will present you as a professional.

 

Question 2: Add unique ID to each row which could be used to uniquely identify the row as part of the presentation.

Add unique ID to each row which could be used to uniquely identify the row as part of the presentation

Answer

  • Right-click on column A
  • Click Insert
  • Name the new column Unique ID

Add unique ID to each row which could be used to uniquely identify the row as part of the presentation

  • Put cursor on Date
  • Click Format Painter
  • Put cursor on Unique ID
  • Select Unique ID
  • Input 1 in A2
  • Input A2+1 in A3

Add unique ID to each row which could be used to uniquely identify the row as part of the presentation

  • Copy the formula in cell A3
  • Move the cursor to the next column (B3)
  • Press Ctrl + ↓
  • Move cursor back to previous column (A702)
  • Press Ctrl + Shift + ↓
  • Paste the formula in selected range (A4 – A702)

 

Question 3: How would you create unique ID as a unique random 6-digit number?

Answer

Add unique ID to each row which could be used to uniquely identify the row as part of the presentation

  • Insert another column
  • Excel Formula =RANDBETWEEN(100000,999999) in cell A2
  • Copy the formula
  • Move the cursor to the next column (B3)
  • Press Ctrl + ↓
  • Move cursor back to previous column (A702)
  • Press Ctrl + Shift + ↓
  • Paste the formula in selected range (A3 – A702)
  • Name the column Unique 6 Digit ID
  • Insert a new column
  • Copy column B
  • Paste as values in column A
  • Delete column B

 

Question 4: How would you create unique ID using this format CNT-CUR-PRN-XXX, to identify each row.

  • CNT- Country of Sales
  • CUR- Currency
  • PRN- Product Name
  • XXX- Unique ID

Answer

  • Insert another column
  • Name the column Concatenated Unique ID
  • Formula =LEFT(F2,3)&"-"&LEFT(G2)&"-"&LEFT(I2,3)&"-"&RANDBETWEEN(100,999) in cell A2
  • Copy the formula
  • Move the cursor to the next column (B2)
  • Press Ctrl + ↓
  • Move cursor back to previous column (A702)
  • Press Ctrl + Shift + ↓
  • Paste the formula in selected range (A3 – A702)

 

Question 5: Create random unique ID as GUID - Global Unique Identifier. GUID is a unique number that consists of five groups of hexadecimal numbers separated by hyphens.

Sample GUID: 123e4567-e89b-12d3-a456-426655440000

 

Answer

  • Insert another column
  • Name the column GUID
  • Excel Formula =CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8)," ",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,4294967295),8),DEC2HEX(RANDBETWEEN(0,42949),4))

in cell A2

  • Copy the formula
  • Move the cursor to the next column (B2)
  • Press Ctrl + ↓
  • Move cursor back to previous column (A702)
  • Press Ctrl + Shift + ↓
  • Paste the formula in selected range (A3 – A702)

 

Watch Video Tutorial

 

 

Next Step: Download PDF EBook

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.

Download PDF EBook

About the Author

Vadim Mikhailenko, MBA

Vadim lives in Milwaukee, Wisconsin and works as an Information Technology Consultant, Coach and Educator. Vadim is very passionate about Milwaukee community and teaches classes to underprivileged students at local community college to help them get employed. Throughout his consulting career Vadim studied a lot of tools and technologies and learned hiring process by helping organizations to hire candidates and also supported students to get prepared and to pass interview and assessment tests.

Vadim loves to share his knowledge with and enjoys teaching  new skills to help you get hired for your dream job.

Download XLSX Files Used in This Tutorial

There are 2 Excel XLSX work files included in this free download. Complete the  form below and you will be redirected to the download page.

  • Advanced.Excel.Interview.Questions.and.Answers.XLSX - XLSX Source File
  • Advanced.Excel.Interview.Questions.and.Answers.ZIP - XLSX Source file in Zip Archive