Microsoft Excel

4 minute read

Excel Challenge 41: Transforming Data With Excel Functions

Deb Ashby

Deb Ashby

Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

Are you ready to test your problem-solving and Excel formula skills? This challenge tests your ability to convert data from one format to another using Excel functions.

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

  • First, watch the challenge video and read the instructions below the video.
  • Review the previously published video(s) and article(s) on which the challenge is based.
  • Download the Excel worksheet you will use to complete the challenge tasks.
  • Put yourself to the test!

Take the challenge

Think you have what it takes to solve this challenge? Download the workbook and get started!

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


The challenge

Here’s the scenario to be solved from the download file:

We are organizing the start waves for the London Marathon based on each athlete’s estimated completion time. 

Points to note:

  • There are three waves: blue, red, and green.
  • The fastest athletes should be assigned to the blue wave, and the slowest athletes should be assigned to the green wave. 
  • We know each athlete’s 10-kilometer time. 
  • The total distance for the marathon is 42 kilometers.

excel challenge transform data dataset

✅ STEP 1: Calculate each athlete’s estimated marathon finishing time.

Calculate the finishing time for each athlete based on their 10K time. 

Things to note:

  • Fill down for all athletes. 
  • Ensure the formatting is h:mm.

✅ STEP 2: Generate a random bib number. 

Next, we need to generate a random bib number for each athlete. 

Things to note:

  • Bib numbers should be between 10000 and 99999. 
  • There should be no duplicate bib numbers. 
  • Remove any volatile functions. 

✅ STEP 3: Convert the data to an Excel table. 

Convert the dataset to an Excel table and name the table ‘Marathon2025’. 

Excel challenge dataset

✅ STEP 4: Choose columns and sort the data.  

Next, in columns G to I, we need to pick out of the dataset the following columns, ‘Athlete’, ‘Bib Number’ and ‘Marathon Time’. 

Things to note:

  • Use column index numbers in the formula. 
  • Ensure the marathon times are formatted correctly as h:mm (hours and minutes). 

Sort the new list by marathon time and then by athlete name in ascending order so the fastest athlete is at the top of the list.

GoSkills Excel challenge

✅ STEP 5: Calculate the total number of athletes. 

In cell C38, calculate the total number of athletes. 

✅ STEP 6: Organize the athletes into start waves.  

Finally, we need to organize the athletes into start waves based on their time. The fastest athletes should be in the blue wave (starting first) and the slowest athletes in the green wave (starting last). Athletes should be organized evenly.  

Excel challenge dataset

How quickly can you complete this challenge? Fifteen minutes? Ten? Less?

If you’re just getting started with Excel, all the knowledge you need to solve this challenge has been shown in the following resources: 


 Read:

GoSkills Excel challenge

  Excel SORT and SORTBY functions   

  Working With Excel Dynamic Arrays   

 

Watch:

If you’re already proficient with Excel, this should be quite easy.

We hope you'll enjoy taking part in this challenge! Have fun!

Take the challenge

Think you have what it takes to solve this challenge? Download the workbook and get started!


The solution

Did you remember to share your method in our Slack channel? If you're stumped, check out how other learners solved this problem. And don't forget to watch Deb Ashby's solution below.

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you learn more essential formulas, functions, and practical real-world Excel skills.

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial

Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

Deb Ashby

Deb Ashby

Deborah is a Microsoft MVP and TAP Accredited Microsoft Instructor and Content Creator. She has had various careers in the IT industry since the mid 1990s. This includes various roles in IT training, including the starting of her own company where she focuses on creating video-led, Microsoft training courses for clients. She is an instructor at GoSkills with various Microsoft courses, including Excel for Business Analysts. In her spare time, she likes to swim, practice yoga, travel, and refine her photography skills. View her profile here.