In this tutorial are eleven problem exercises for the Excel test on advanced topics.
You will need an advanced understanding of Excel to solve all the problems, in particular knowledge of the following: the LEFT, SEARCH, MID, RIGHT, FIND, SUBSTITUTE, LEN, INDIRECT, UNIQUE, TEXTJOIN, COS, SIN, SQRT, IF, and TRUE functions; Named Range, how to separate names, generate random numbers, calculate distance between addresses, insert pictures from URLs, create dependent dropdown lists, find duplicate rows, combine duplicate rows, unhide rows in a range, transpose rows to columns, create a meter chart, and scrape data from websites.
The latest version of Excel (Microsoft 365) is required to solve all the exercises.
Download Practice Workbook
Problem Overview
The downloadable file above contains eleven problems. The dataset is different for each exercise. The exercises are shown in the “Problem” sheet, and the solutions are shown in the “Solution” sheet. Additional data is provided in the “Reference Table” sheet. As an example, here is a snapshot of the dataset for the second exercise.
- Exercise 1 – Separate First, Middle, and Last Name:
- Split the first, middle, and last name from the full name using a formula.
- The LEFT, SEARCH, MID, RIGHT, LEN, SUBSTITUTE, and FIND functions will be required to solve this problem.
- Exercise 2 – Generate a Random Number from a Range:
- Write a VBA code to return random numbers that have a minimum value of 50 and a maximum value of 90, then add these values in another column. This exercise can be useful to pick up a lottery winner.
- The following animated image shows the code to generate random numbers.
- Exercise 3 – Find the Distance Between Two Addresses:
- The coordinates (latitude and longitude) between two addresses are given. Use the Haversine formula to find the distance between these two points.
- Exercise 4 – Insert a Picture from an URL:
- Insert images from an URL in a cell as shapes using VBA. The code should still work if there are any empty cells within the source URL range.
- Exercise 5 – Create a Dependent Dropdown List:
- Create a dependent data validation list using the INDIRECT function and Named Ranges. For example, in the Category column, users will select either Drinks or Chips. Then the items from that Category will be shown in the Item column.
- The reference data is provided in the “Reference Tables” sheet.
- Exercise 6 – Find Duplicate Rows:
- Highlight the duplicate rows in a range using VBA.
- Exercise 7 – Combine Duplicate Rows without Losing Data:
- The car model data for twenty people is given. Combine the duplicate rows using the UNIQUE and TEXTJOIN functions.
- Microsoft 365 is required to solve this problem.
- Exercise 8 – Unhide Rows Within a Range:
- Some rows are hidden. Reveal the hidden rows in a specified range using a VBA code.
- Exercise 9 – Transpose Rows to Columns Using Power Query:
- Transpose the specified rows to columns by applying the Power Query.
- Exercise 10 – Create a Meter Chart:
- Prepare a meter chart using the given data points.
- Exercise 11 – Scrape Data from a Website:
- Scrape data from the website link provided, then import and format a table.
Here is the solution to the second exercise.
Read More: How to Pass Excel Assessment Test
