In this article, you will get eleven problem exercises for the Excel test on advanced topics. You will need an advanced understanding of Excel to solve all the problems. Moreover, you should know the following: 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. You should have the latest version of Excel (Microsoft 365) to solve all the exercises.
Download Practice Workbook
You can download the Excel file from the following link.
Problem Overview
The downloadable file for the Excel test on advanced topics contains eleven problems. The dataset is different for each exercise. The exercises are shown on the “Problem” sheet, and the solutions are shown on the “Solution” sheet. Moreover, additional data is given in the “Reference Table” sheet. Here is a snapshot of the dataset for the second exercise.
- Exercise 01 Separate First, Middle, and Last Name:
- You need to split the first, middle, and last name from the full name using a formula.
- You will need to use the LEFT, SEARCH, MID, RIGHT, LEN, SUBSTITUTE, and FIND functions to solve this problem.
- Exercise 02 Generate Random Number from Range:
- Type a VBA code to return random numbers that have the lowest value 50, highest value 90. Then, these values will be added 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 03 Find Distance Between Two Addresses:
- The coordinates (latitude and longitude) between two addresses are known. You will use the Haversine formula to find the distance between these two.
- Exercise 04 Insert Picture from URL:
- You will insert images as shapes in a cell from a URL using VBA. This code will still work if there are any empty cells within the source URL range.
- Exercise 05 Create Dependent Dropdown List:
- Your task is to create a dependent data validation list using the INDIRECT function and Named Range. 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. Additionally, we’ve given the reference data in the “Reference Tables” sheet.
- Exercise 06 Find Duplicate Rows:
- You will highlight the duplicate rows in a range using VBA.
- Exercise 07 Combine Duplicate Rows without Losing Data:
- You know the car model data for twenty people. Then, your task is to combine duplicate rows using the UNIQUE, and TEXTJOIN functions. You will need Microsoft 365 to solve this.
- Exercise 08 Unhide Rows Within Range:
- We have made some rows hidden; you will reveal the hidden rows in that specified range using a VBA code.
- Exercise 09 Transpose Rows to Columns Using Power Query:
- In this exercise, your objective is to transpose rows to columns by applying the Power Query.
- Exercise 10 Create Meter Chart:
- Prepare a meter chart in Excel using the given data points.
- Exercise 11 Scrape Data from Website:
- Your task is to scrape data from a website and import a table and we’ve given the website link in the Excel file. Additionally, perform some formatting to make that table look better.
The following image shows the solution to the second exercise.
Conclusion
Thank you for reading this article. By completing this article, we hope that you have gained knowledge about the Excel test on advanced topics. You can find more articles similar to this on the ExcelDemy website. Moreover, 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!
