Excel Practice Test for Employment: 8 Exercises

In this article, you will learn about eight practice Excel test for employment. You should have beginner to intermediate knowledge of Excel to solve all the problems. Moreover, you should know the following: INDEX, MATCH, SUMIF, LEFT, SEARCH, MID, RIGHT, FIND, SUBSTITUTE, LEN, COUNTIF, COUNTBLANK and VLOOKUP functions; the Fill Handle, ways to AutoFill the formulas, custom cell formatting, conditional formatting, working with the chart elements, inserting a pie chart, Flash Fill, Filter feature, Sort feature and adjustment of background cell color features to find the solutions to the problems. The problems can only be solved with Excel 2010 or later.

Download Practice Workbook

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 sales department of the company. The “Problem” sheet shows the exercises, and the “Solution” sheet shows the problems 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. Firstly, find the number of the 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 first name of the employee.

• 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. Additionally, 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. Apply 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 attend 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. Firstly, you will create a pie chart from the given data. Then, add Title, Data Labels (Percentage), and Legend to it.
  • Secondly, create a combo chart from another dataset. The sales values will be on the secondary axis. Then, 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

Conclusion

Thank you for reading this article. By completing the Excel practice test for employment, we hope that you have gained knowledge about Excel. You can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!