Excel Practice Test for Employment

Problem Overview

The downloadable file for the Excel practice test for employment contains eight problems. The dataset shows the data for twenty workers at a specific company. They all work in the company’s sales department. The “Problem” sheet shows the exercises and the “Solution” sheet shows the problems that have been worked out. Here is a zoomed-out version of the dataset for this exercise.

• Exercise 01 Count the Number of Empty Cells & Use of Flash Fill: There are two tasks in this exercise. Find the number of blank cells inside the dataset. Finally, fill the email rows (with the format “[email protected]”).
  • Hint: You can fill in the rows in various ways. The easiest way to do so is to use the Flash Fill feature.
• Exercise 02 Lookup Values Using VLOOKUP and INDEX MATCH: Six employee IDs are given. Your task is to find the name and position of the employee. Firstly, using the VLOOKUP function and then using the INDEX MATCH functions.
  • Hint: An invalid employee ID is given. You need to use the IFERROR or IFNA function to resolve the issue.
• Exercise 03 Separate the Names into Three Parts: The full name of twenty four people is provided, your task is to use three formulas to separate the names into first name, middle name, and last name.

The following animated image shows the formula to return the employee’s first name.

• Exercise 04 Find Sales Statistics Using Formulas: Use different formulas to find the number of female employees, sales generated by them, employees joined before May 2022 and the sales generated by the travelling salesperson.
• Exercise 05 Use of Filter Feature: Apply the Filter feature to show the sales values greater than $100,000. Use the Sort feature to sort the sales values by largest to smallest.
  • Hint: It is better to convert the range into a table before doing the filter operation.
• Exercise 06 Create a Pivot Table: Sales values generated by five employees are given for a particular period. The PivotTable feature to find the sales value originated by the employees.
• Exercise 07 Application of Conditional Formatting: Twenty employees enrolled for a course that took place for three days in the company auditorium. It was mandatory to attend the session for at least one day. Your task is to find who did not participate in the course and use conditional formatting.
  • Red color for 3 days absent
  • Yellow color for 2 days absent
  • Green color for perfect attendance (0 day absent)
  • Moreover, count the number of employees present and absent for each day
  • Then, apply conditional formatting (color Red) for more than six employees absent
• Exercise 08 Use of Excel Charts: In the final exercise, your task is to create two charts. You will create a pie chart from the given data. Add Title, Data Labels (Percentage), and Legend to it.
  • Create a combo chart from another dataset. The sales values will be on the secondary axis. Add the axis title, chart title, and data label to the chart.

The following image shows the solution to the third problem.

Download Practice Workbook