Excel Challenges, Learn Excel

Highlight Employees by Performance Rating – Conditional Formatting Challenge

Last updated on May 18, 2012

Chandoo

So who is up for an Excel challenge?

Shelly, who is an HR Manager sent this distress call last week,

First off, I LOVE your site. It is the first place I go for any excel question and I love the daily emails. I’m not sure if you answer direct emails, but I’m begging you to at least read this and let me know if I’m crazy or not (good thing you don’t know me personally :>). I’ve searched through you ‘ask an excel’ blog and I have not come close to anything I’m trying to accomplish. I’ll do my best to explain it.

I have a group of employees- lets say 100 employees.

Each employee has a performance rating attached to them.

I want to divide the group by 5%, 15%, 65%, 10%, 5% based on their performance rating.

So for example how I manually do this is by running the report of employees.

I then sort the list by Performance ratings from High to Low (0.0-5.0 is our range and you can have decimals in between 0.1, 2.5, 2.3 etc.)

I then take the total number of employees and calculate the top 5%, next the 15%, then 65%, then 10%, 5% (so breaking them up into groups).

Doing this isn’t horrible, but I have to do this for each department and we are talking 700 departments. Each department is not alike- so some may have 50 employees while others may have 200+.

Is there an easier way to do this in excel??

Anytime an email starts with I Love, I am all ears. So naturally I read the entire mail. And I had to sympathize with her. 700 DEPARTMENTS?!? Can you imagine dealing with 700 departments with lots of disgruntled employees. I remember my performance evaluation & rating days back when I had a full time job. Almost everyone I knew hated their bosses during the appraisal season. And when hikes are announced, everyone (including the person with fabulous hike) would call their favorite head hunter and flirt. Aah, good old days of ratings & reviews.

But I digress.

So going back to our HR manager in distress, how would you help her?

Your challenge – highlight employees by performance rating

Here is your challenge.

Download this file.
It contains data & coloring rules.
Set up conditional formatting such that you can highlight the data based on the rules
Bonus points if you can set up conditional formatting rules such that they work on any sheet (assuming each department has their own sheet of data in same format)
Share your rules & solution with us in comments
Feel jolly good knowing that you are awesome in Excel.

Need some help? Check out these articles

Conditional formatting is one of my favorite features in Excel. Naturally, I want you to be awesome in it. Check out these tutorials & examples to understand how to solve this problem.

Download my solution

Now, some of you might be in same boat as Shelly. Please note that I sympathize with anyone who deals with people from 700 departments or more. But sympathy seldom solves struggle. So, go ahead and download my solution. Break it apart, examine the conditional formatting rules and fire the bottom 5% of your employees. Well, go easy on the last part 😛

Click here to download my solution.