Excel Challenge 27: Create a Dataset Using Random Selection

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. 

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. 

Start 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!

---

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