Microsoft Excel

2 minute read

Excel Challenge #2

Alan Murray

Alan Murray

Welcome to Excel challenge #2!

This week's challenge is designed to test your knowledge of the Power Query concepts from the GoSkills Excel - Basic & Advanced course.

Everything you need to participate in the challenge can be found on this page. To take part:

  • Watch the challenge video
  • Read the instructions below the video
  • Download the Excel worksheet you will use to complete the challenge tasks
  • Put yourself to the test!

Want to chat about your approach and process with other Excel-heads? Join our new Slack channel to share your insights and questions with like-minded learners.

Excel Challenge #2



Download your challenge data

Start working on the Excel challenge with this data worksheet

You have received a file with resolved issues data.

It contains the following columns:

  • The area from which the issue was received
  • A combined column with the name of the person assigned to the issue and the issue date
  • The priority level of the issue
  • The number of days that it took to resolve the issue

Unfortunately the data is not in a format that makes it easy to analyze.

There are various problems such as:

  • The area code is in a row on its own
  • The name and date is in a combined column and requires separating
  • Some of the names have the incorrect case
  • There are average rows and blank rows under each area which we do not need

The challenge is to use Power Query to transform this data into a format that we can analyze easily.

And then answer a couple of questions about the data.

We want to know:

1. The average response time in days

2. The number of level 1 or level 2 issues that were resolved in less than 5 days

Space has been provided between the issues data and the questions to load the Power Query table.

Test data is provided in range L12:O20 so that you can check if your solution works when new rows are added to the issues data.

Download the challenge workbook to take on the challenge.

Download your challenge data

Start working on the Excel challenge with this data worksheet

 

The Solution



We hope you enjoyed taking part in this challenge! Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of Excel courses to further sharpen your skills.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial
Alan Murray

Alan Murray

Alan is a Microsoft Excel MVP, Excel trainer and consultant. Most days he can be found in a classroom spreading his love and knowledge of Excel. When not in a classroom he is writing and teaching online through blogs, YouTube and podcasts. Alan lives in the UK, is a father of two and a keen runner.

2 comments

LoginSign up
  • Bill

    This was an excellent challenge.

  • Mario

    That was a good challenge