In this article, we present nine exercises on Excel test questions and answers.
You will need an advanced understanding of Excel to solve all the problems. Specifically, to find the solutions to the problems you should know the following: the INDEX, MATCH, SUMIF, LEFT, SEARCH, MID, RIGHT, FIND, SUBSTITUTE, LEN, COUNTIF, COUNTBLANK, SUBTOTAL, MEDIAN, MODE, STDEV, COUNTA 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, the Sort feature and the adjustment of background cell color features.
You should be able to use any version of Excel from Excel 2010 onwards to solve all the problems without any issues.
Download Exercise Workbook
Download the Excel file containing the exercises and solutions from the following link:
Problem Overview
The downloadable file above contains nine problems. The “Problem” sheet shows the exercises, and the “Solution” sheet shows the problems worked out. As an example, the information for the Argentina vs Brazil soccer matches is included in the ninth exercise. Here is a snapshot of the dataset for that exercise.
Exercise 1 – 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. Then, 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 2 – 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 3 – Separate the Names into Three Parts
The full names of twenty four people are provided. Your task is to use three formulas to separate the names into first name, middle name, and last name.
Exercise 4 – Find Sales Statistics Using Formulas
Use different formulas to find the number of female employees, the sales generated by them, the employees joined before May 2022, and the sales generated by the traveling salesperson.
Exercise 5 – Use of the 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 from largest to smallest.
Hint: Convert the range into a table before doing the filter operation.
Exercise 6 – 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 generated by the employees.
Exercise 7 – Application of Conditional Formatting
Twenty employees enrolled for a course that took place over 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 the following conditional formatting:
- Red color for 3 days absent,
- Yellow color for 2 days absent,
- Green color for perfect attendance (0 day absent).
Further, count the number of employees present and absent for each day. Then apply conditional formatting (color Red) for days with more than six absent employees.
Exercise 8 – Use of Excel Charts
Your task is to create two charts:
- Firstly, create a pie chart from the given data. Then, add a Title, Data Labels (Percentage), and Legend to it.
- Secondly, create a combo chart from another dataset, with the sales values on the secondary axis. Then, add the axis title, chart title, and data label to the chart.
Exercise 9 – Problems Related to Statistics
The final scores for all soccer matches between Brazil and Argentina are given . Your task is to:
Find the goals scored by each team
The winner is given for all matches. So if Argentina wins a match, they will have the highest goals, and so on. Combine the IF, LEFT, and RIGHT functions to solve this. Additionally, add 0 to make the values numeric.
Find Goal Statistics
For each team’s goals scored, determine the total, average, standard deviation, median, and mod.
Find Match Statistics
Determine the total games played, the number of victories, draws, and goalless draws for each team.
The following animation shows the formula to return the number of goals scored by Argentina.
The following image shows the solution to the first part of the ninth exercise.
Happy problem solving!
