Microsoft Excel

3 minute read

Excel Challenge 27: Create a Dataset Using Random Selection

Deb Ashby

Deb Ashby

Join the Excel conversation on Slack

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

It's time for Excel challenge #27!

How good are you at determining which Excel formula to use to complete a specific task? If you consider yourself a budding formula expert, then this challenge should test your problem-solving skills. 

Download your challenge data

Download the file to get started!

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!

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🧠

The scenario 🖼️

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

We want to compile a dataset that we can use to practice our Excel skills. We could download a dataset from a website, but it can take time to find something appropriate. Instead, we are going to build our own using Excel formulas. 

The tasks 🏋️

The challenge is to complete all empty columns in the dataset. We’ve got you started by listing out the employee names. You need to populate the other columns: Emp_ID, Department, Job_Title, and Salary. 

Microsoft Excel challengeMicrosoft Excel challengeStart in cell K2 and calculate the number of employees first. 

Emp_ID

  • This column needs to be populated with the employee ID of the employee. 
  • The format of the employee IDs is ‘EMP-XXXX’. 
  • The first employee ID should be ‘EMP-2000’. All subsequent employee ids should increment by ‘1’ each time. 

Department

  • This column needs to be populated with the department of each employee. 
  • There are 4 departments: HR, Sales, Marketing, and Finance. 
  • Randomly assign a department to each employee using functions. 

Job_Title

  • This column needs to be populated with the job title of each employee. 
  • There are 4 job titles: HR Admin, Sales Agent, Marketing Assistant, and Accountant. 
  • The assigned job title should correspond to the employee’s department. 

Salary

  • This column needs to be populated with the salary of each employee.
  • The salary should be between $30,000 and $70,000. 
  • We need to ensure that the salary is rounded to the 1000 multiplier. 

The clues🕵️

Here are a few things to consider:

  • Think about the best formula or combination of formulas to use.  
  • Ensure that the underlying formulas are removed from the dataset. 
  • When rounding the salary, we don’t mind if you round up or round down. 

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

Maybe a few Excel dynamic array formulas would be useful here! (Like maybe the SEQUENCE function😉)

If you’re already proficient with Excel, maybe you can tell us a creative way to solve this problem in our Slack channel.

Have fun!

Download your challenge data

Download the file to get started!


The solution

We hope you enjoyed taking part in this challenge!


Hungry for more?

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.