In this article, you will get nine exercises on Excel test questions and answers. You will need an advanced understanding 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, 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 to find the solutions to the problems. You should use the Excel 2010 version of Excel to solve all the problems without any issues.
Download Practice Workbook
You can download the Excel file from the following link.
The downloadable file for the Excel test questions and answers contains nine 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. 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 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.
- 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 traveling 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.
- Exercise 09 Problem Related to Statistics: In this exercise, the final score between Brazil and Argentina is given for all soccer matches. 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 animated image shows the formula to return the number of goals scored by Argentina.
Lastly, the following image shows the solution to the first part of the ninth exercise.
Thank you for reading this article. By completing this article, we hope that you have gained knowledge about Excel test questions and answers. 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!