Advanced Excel Exercises with Solutions PDF

In this article, you will find advanced Excel exercises with solutions in PDF format. You need to possess advanced Excel knowledge to solve these problems. You will need to know 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, and create a meter chart. Except for problem 07, all problems can be solved using Excel 2010 or later. You will need Microsoft 365 to solve Exercise 07.

Download Practice Workbook

• You can download the PDF file from the following link.

• You can download the Excel file from the following link.

Problem Overview

There are 10 problems in this PDF file. Most of the problems are based on VBA. You can solve the problems in many ways, however, we have specified one way to make them harder to solve. The following image shows the first two exercises from the PDF file.

• Problem 01 Separate First, Middle, and Last Name: You need to split the first, middle, and last name from the full name using a formula.
  • Firstly, you will need to use the LEFT, SEARCH, MID, RIGHT, LEN, SUBSTITUTE, and FIND functions to solve this problem.

The following animated image shows the formula to separate the first name from the full name.

• Problem 02 Generate Random Number from Range:
  • Secondly, you will type a VBA code to return random numbers that have the lowest value 50, highest value 90.
• Problem 03 Find Distance Between Two Addresses:
  • The coordinates between two addresses are known. You will use the Haversine formula to find the distance.
• Problem 04 Insert Picture from URL:
  • Fourthly, you will insert an image as a shape in a cell from a URL using VBA.
• Problem 05 Create Dependent Dropdown List:
  • Then, your task is to create a dependent data validation list using the INDIRECT function and Named Range.
• Problem 06 Find Duplicate Rows:
  • Afterward, you will highlight the duplicate rows in a range using VBA.
• Problem 07 Combine Duplicate Rows without Losing Data: 
  • Then, your task is to combine duplicate rows using the UNIQUE, and TEXTJOIN You will need Microsoft 365 to solve this.
• Problem 08 Unhide Rows Within Range: 
  • After that, you will reveal the hidden rows with a specified range using VBA.
• Problem 09 Transpose Rows to Columns Using Power Query:
  • Then, your objective is to transpose rows to columns by applying the Power Query.
• Problem 10 Create Meter Chart:
  • Finally, the exercise 10 is to prepare a meter chart in Excel using the known data points.

The image below depicts the solution to the first two advanced Excel exercises’ problems, with solutions in PDF format.

Conclusion

Thank you for reading this article about the advanced Excel exercises with solutions PDF. We hope that with these practice exercises in PDF format with answers, you can test your Excel skills. 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!