Excel Challenge 44: Build an Employee Performance Review Sheet

It’s time for another exciting Excel challenge! Whether you’re in HR, management, or simply looking to sharpen your Excel skills, this challenge is perfect for you.

Can you create a performance review sheet that calculates an employee’s score based on their ratings? This challenge involves tackling five key tasks. The best part? By the end, you’ll have a ready-to-use template for future needs!

1. Watch the challenge video and read the instructions below.
2. Download the Excel worksheet needed to complete the tasks.
3. Ready, set, put your skills to the test!

Remember to share your progress in the GoSkills Learners Community on Slack and swap tips and tricks with fellow Excel pros!

The challenge

In this challenge, you’ll build a performance review sheet that scores employees automatically based on their ratings. Here are five tasks you’ll need to complete to make it all work.

Task #1: Calculate the score for measurable objectives

Section A consists of 5 performance objectives, each with an equal weight of 20%. Your formula in column F needs to calculate the overall score for this section, ensuring that a score of 5 in all areas results in 100% in row 28. Can you write the formula that handles this weighting?

Task #2: Handle missing performance ratings

Section A also includes three columns for ratings: employee’s self-assessment (column C), manager’s rating (column D), and another assessor’s rating (column E). Each row has a 20% weight, but the catch is that sometimes there won’t be another assessor involved. How can your formula adjust to calculate the score accurately if column E is empty?

Task #3: Calculate the score for core competencies

Section B (the core competencies section) works similarly to Section A, but each row weighs 10%. However, row 42 is unique—it only applies if the employee holds a leadership role. Your task is to create a formula in cell F43 that accurately calculates the total score for this section, regardless of whether the employee is a leader.

Task #4: Calculate the overall score and include a rating

For the final task, you’ll calculate the overall performance score in row 46, factoring in that Section A is weighted at 40% and Section B at 60%. In addition to the score, the employee should see a phrase that corresponds to their performance based on the guide at the top of the sheet. Can your formula display both the score and the appropriate phrase?

Task #5: Create a radar chart

Lastly, let’s add a visual element! Use the self-assessment and manager’s rating data to create a radar chart. The chart title should display “Core Competencies” along with the employee’s name (entered in cell B3). How will you dynamically update the chart title?

The clues 🕵️

Ready to tackle this challenge? Before diving in, take a moment to brush up on IF functions in Excel, as they’ll come in handy. That’s all we’ll say—good luck and enjoy!

The solution

Were you able to complete all the tasks? Dive into our solution and compare it to yours. Let us know if you have a different approach—there are always multiple ways to solve an Excel challenge!

If you enjoyed this challenge and want to hone your skills further, check out our Basic and Advanced Excel course. We also have a variety of other Excel challenges to tackle and an Excel Resources hub full of tutorials, tips, and tricks to enhance your mastery.

Until next time, happy Excel-ing! 👋