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 Exercise 7, all exercises can be solved using Excel 2010 or later. You will need Microsoft 365 to solve Exercise 7.

Download Practice Workbook

Problem Overview

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

• Problem 1 – Separate the First, Middle, and Last Names: 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.

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

• Problem 2 – Generate a Random Number from Range:
  • Use VBA code to return random numbers that have the lowest value 50, highest value 90.
• Problem 3 – Find the Distance Between Two Addresses:
  • The coordinates between two addresses are known. Use the Haversine formula to find the distance.
• Problem 4 – Insert a Picture from URL:
  • Insert an image as a shape in a cell from a URL using VBA.
• Problem 5 – Create a Dependent Dropdown List:
  • Your task is to create a dependent data validation list using the INDIRECT function and a Named Range.
• Problem 6 – Find Duplicate Rows:
  • Highlight the duplicate rows in a range using VBA.
• Problem 7 – Combine Duplicate Rows without Losing Data: 
  • Combine duplicate rows using the UNIQUE, and TEXTJOIN.  You will need Microsoft 365 to solve this.
• Problem 8 – Unhide Rows Within a Range: 
  • Reveal the hidden rows with a specified range using VBA.
• Problem 9 – Transpose Rows to Columns Using Power Query
• Problem 10 – Create a Meter Chart:
  • Prepare a meter chart in Excel using the known data points.

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

Download Practice Workbook