How to use Excel to highlight Employee Performance Rating

  • Sharebar

Chandoo recently posted a challenge to help a reader with a problem.

The problem is: how do you highlight performance rating in a list of employee according to percentile?

Shelly, an HR Manager, wants to divide her list of employees highlighting those who belong in the Top 5% then the next 15%, the next 65%, the next 15% and the bottom 5%.

She needs an efficient way of doing this as it needs to be done for hundreds of different departments.

Both Chandoo and his readers have already posted the solution so I am not creating anything new.

Rather, in this article and in the embedded video I am showing you how to do it and I include an additional way of categorizing the information which I think is really useful.

The starting point can be seen in the following image.

The list named ‘Input’ needs to be turned into ‘Output’ i.e. assigning conditional formatting to it so that each employee and his/her Performance Rating is shaded in a color which depends on the rating itself.

The color selection can be seen in the Percentile Thresholds table.

How to use Excel to highlight Employee Performance Rating - Starting Point

How to use Excel to highlight Employee Performance Rating - Starting Point

We need to work out the numeric percentile thresholds by applying the PERCENTILE function to the range of Performance Ratings at our disposal.

The Syntax for the formula is ‘=PERCENTILE(array,k)

In our particular case the array is $F$6:$F$33 and the the k value is ’1 less the cumulative % of employees for specific range’.

How to use Excel to highlight Employee Performance Rating

How to use Excel to highlight Employee Performance Rating

So if the employee‘s Performance Rating is equal or above 4, he or she will belong to the Top 5%. If his/her PR score is between 3.46 and 4, he or she will belong to the following 15% and so on.

The next step is to apply conditional formatting to the table of employee names and performance rating using the individual rating to decide which color shading the cell should have.

We do this by creating 5 rules which check the Performance Rating value against each threshold.

If the condition is satisfied then the formatting is applied otherwise Excel progresses onto the next rule until all 5 five are taken into consideration.

How to use Excel to highlight Employee Performance Rating - Conditional Formatting

How to use Excel to highlight Employee Performance Rating - Conditional Formatting

This produces the result of highlighting each individual employee’s performance using a color coding.

How to use Excel to highlight Employee Performance Rating - Result

How to use Excel to highlight Employee Performance Rating - Result

One thing I believe would make the information more usable is the ability to filter the list of employees based on Performance Rating Categories.

I do this by adding a ‘Performance Category’ value next to the threshold classification which we created earlier.

How to use Excel to highlight Employee Performance Rating - Category

How to use Excel to highlight Employee Performance Rating - Category

Next I add a ‘Performance Category’ column in the Employee table and I assign values using an IF statement which evaluates the PR value and compares it to the Threshold values.

How to use Excel to highlight Employee Performance Rating - Adding a Category Column

How to use Excel to highlight Employee Performance Rating - Adding a Category Column

After applying filters we can then select, for instance, only Employees belonging to Categories D and E (the bottom 20%) to identify who needs extra training.

How to use Excel to highlight Employee Performance Rating - Filtering

How to use Excel to highlight Employee Performance Rating - Filtering

About the Author

Leave a Reply




If you want a picture to show with your comment, go get a Gravatar.