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.
- What is conditional formatting & how to use it?
- Using formulas with conditional formatting
- Highlight top 10 performances with conditional formatting
- More on conditional formatting [more than 60 examples]
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.
Go ahead and share your solution
So what are you waiting for. Put on your Excel hats and get thinking. Once you have an answer, rush back to us & post it in comments. Go!
Need more challenges? Try these too
If you want more Excel challenges & homework, check out these.
21 Responses to “Red vs. Blue – 35 Cool Visualizations on 2008 US Presidential Election”
[...] Read the rest of this great post here [...]
[...] post by WP-AutoBlog Import var AdBrite_Title_Color = '0000FF'; var AdBrite_Text_Color = '000000'; var [...]
Impressive list, though a few of these clearly qualify as junk (the second one with the hairy circle segments, for example).
Also, that McCain vs. Obama tax plan comparison is wildly distorted, for a debunking and redesign see here: http://chartjunk.karmanaut.com/taxplans/
Holy information/data overload. There are some great visualizations here, but also that are not so good. This list may have been better in small chunks.
[...] Haired Dilbert has some pretty cool visualizations for the ‘08 Election featured on his blog. I really liked this [...]
Cool list!
I know another widget that might have your interest.
It shows the progression of polls and uses data from electoral-vote.com.
I think you might like it:-)
http://www.youcalc.com/apps/1221747067033
... and its easy to put on your blog and fits in your sidebar!
Make a difference, keep on voting!
@Robert .. Agree, few of the charts are not really great. thanks for link, I have updated the post with the link.
@Tony ... That was the point. I wanted to compile a huge list with all the visualizations worth a look.
@Michael .. Welcome to PHD blog 🙂 thanks for sharing that link.
[...] Check the rest out here. [...]
[...] has progressed. With one look you can see on what issues candidates debated most. Also see these 35 different visualizations on 2008 US Elections [via Information [...]
[...] Red vs. Blue - 35 Cool Visualizations on 2008 US Presidential Election Perspctv - another Election Tracking Site. Presidential Watch - what various websites are saying. The Economist’s pole - Economists prefer Obama over McCain. NYTimes - Poll Tracker. Gallup poll tracker… Google Maps Projections Tracker … [...]
[...] Aqui pode igualmente encontrar uma compilação de 35 [...]
[...] 35 Cool Visualizations on 2008 US Presidential Election - Obama vs. McCain [...]
[...] Also see these 35 visualizations from on Obama vs. McCain in US Polls. [...]
First let me say that I love this blog. I have been scouring the Internet and more than likely overlooking the obvious. Can someone lead me to the OFFICIAL source of elections results? I am looking for voter data by county or even town if possible.
The reason I ask is because on Boston.com, they listed the results by town, and have to assume that there is an offical source.
Anyway, any help you can provide will be greatly appreciated!
@Brock: Thank you so much. I guess fec.gov should put up the results as soon as all counties report the results officially. I dont know but I guess it should take a few days before the data is compiled and released to public.
Alternatively did you see what nytimes.com has to offer? They have a county level breakup of results and majority figures in visualization form.
Thanks for your help!
[...] 35 Cool Visualizations on 2008 US Presidential Election - Obama vs…. [...]
@Brock: You can get the data from USAToday site : http://www.usatoday.com/news/politics/election2008/president.htm
just scroll down and select the state name to see its county results in tabular form.
Thanks again. I also stumbled upon this. http://general-election-2008-data.googlecode.com/svn/trunk/json/votes/2008/. It appears as if there was a Google project with the data. I do not know a web programming language, but I am sure there is an easy way to catch the data and put it into a database.
[...] to give a deeper insight into the elections. A top 35 of those visualizations are listed in the Chandoo.org website. B. Shneiderman’s very interesting network analysis of the Senatorial voting patterns is [...]
ts not cool or notx