Zen and the Art of Stack Ranking with Excel


Stack Ranking as the name implies is the process of stacking things or people up and applying a 1 to n rating to each.  Unlike a standard performance rating, a ranking implies that no two employees should have the same assigned rank and that when you’re finished, someone will be number one and someone will be on the bottom of the pile.

Stack Ranking the members of your team can be a loathsome task.  Especially when you consider the fact that many companies use this tool as a way to identify and carry out targeted reductions in force.  The issue that many managers face when it comes to stack ranking is that even if you have a solid team of super hero performers, someone will be on the bottom of the list.

Some have argued that there are many problems with the stack ranking system.  And I must admit the arguments against are valid.

Nevertheless, the stack ranking system, when used effectively can provide great insight into the performance of your team and can highlight important focus areas for enablement and development.  One key to ensuring that you are getting an accurate representation of your team and the rank each member assumes is by including the critical data elements by which you will evaluate them.  This means having some good reference material about what each team member’s job description or role entails.  This will be especially helpful when communicating results to employees as well as superiors.

Implementing a Ranking System

Any ranking system should have a core set of components that will enable you to record the scores of your team members as they relate to criteria or attributes.  I’ve chosen Microsoft Excel as the system I’ll use, but there are many systems available that will work.

Below, I have included an image with various highlighted sections.  The following section of this article will explain the various components and offer suggestions for implementing your own ranking system.

Excel Based Stack Ranking Tool

Core components

A – Success Criteria

Every job has a success profile or a set of criterion upon which success will be judged.  If this has not been communicated properly, you should exit this article here and get that done ASAP.  There is no point in attempting to apply a ranking when your team has no idea about what you consider critical success criteria.  For our implementation, we’re going to start with a two-tiered categorization of success criteria.  This just makes communicating the results easier.

To make things easier to communicate, I’ve enabled a two tiered categorization leveraging the ‘merge and center’ feature of Excel to consolidate the second tier of criteria under the first.

B – Criteria Weighting

I am a huge proponent of incorporating a method of expressing weights to each of the criteria upon which you will rank your staff.  Let’s face it, not every attribute is going to be of equal importance.   Is punctuality as important as follow-through or proactivity?  I won’t debate these here but I will argue that they are of differing levels of importance so any tool used to record your teams’ performance should give you the ability to reflect this.

The weights recorded in this section are used to populate a section of the results summary.   The weighted scores and ranking are calculated by multiplying the score entered by the weight for this criteria divided by the maximum possible weight (10 in my example.)  The function for the weighted score is as follows:


If you’re new to Excel, this will probably look like gibberish. Just understand that the letters and numbers refer to cell locations in the worksheet. Where you see a cell letter or number prefaced with a dollar sign ($), that’s used to harden the reference so that as we iterate through the various cells to perform the calculation, we don’t increment that cell reference.

There’s probably a better or more efficient way to calculate this, but I couldn’t figure one out so I did it the hard way by manually copying and pasting the calculation across all of the various criteria columns.

C – Employees

Ok, now we’re getting to some data entry.  Column B contains a list of your employees, or the members of the team you’re going to be reviewing.

D – Title

Entering a title for each team member under review enables us to gain further insight into performance of the team.  With this, we can answer questions such as “How are Sr. Hero’s doing across our criteria in comparison to Heros?”

E – Discipline

Recording a discipline for each employee is optional.  I view it as essential, however because it will give you even more insight into how your team is performing.  Think of discipline as a specialization within your team.  To determine if you’ll find a discipline useful, examine the differentiation between the roles performed by the members of your team.  Are some focusing on a completely separate project or set of customers?  If so, use this to further differentiate them.

F – Scores

Here’s where the tough work begins.  You  must record a score for each employee as to their performance against the specific criteria.  I’m suggesting a straight integer score from 0 to 10.  To enforce accuracy and eliminate errors in reporting results, I’ve created data validation rules that force users to enter a whole number between 0 and 10.

Conditional Formatting Rules for Scores

As you enter the scores, you will notice that there are special conditional formatting rules applied to the data entry area for scores.  These rules give the data entry area additional readability by color coding high scores in green and low scores in red.  As you complete the process of evaluating and recording scores, the summary results and rankings can be viewed in columns T through Y or sections G through L respectively.

Note, that in columns V and Y, you can sort the data in ascending score or rank by weighted or raw values using the up arrow and down arrow buttons.

G – Score

Section G contains the computed raw score values for each of the values you entered in section F (columns E through S.)  Note that this section does not take into consideration the weightings that you applied in section B.  I have implemented another set of conditional formatting rules to help identify the top and bottom performers by score.  The remainder of the computed sections apply these same conditional formatting rules.

The value for this section is computed by simply summing each of the score columns.


H – Percentage

Section H contains the percentage of total points scored out of the total possible points.  This field is calculated by dividing the total points scored by the total points available from all of the criteria columns.


I – Rank

Section I shows the ranking of the employee based on the unweighted, or raw total score. This is a tricky calculation due to the fact that you may encounter multiple employees with the same exact score. A proper ranking system will never provide the same rank for multiple items or employees. We start with Excel’s built-in RANK() function which will provide a numeric ranking of a series or range of data. However, to ensure that we never present multiple similar ranks, we implement a simple test to add a value to any ranks where duplicate scores in column T may appear. This ensures that no duplicates appear in our ranking system.


J – Weighted Score

Section J gives us an opportunity to leverage the weights you specified in section B for each of the criteria. Here, we’re calculating the total score offset by the individual weights for the separate criteria. This is where is gets a bit tricky. Each score gets multiplied by the weight and then divided by total possible points or 10 in this example.


Be sure to utilize the Up Arrows and Down Arrows in sections M and N to sort the employee data by rankings as you complete the data entry.

K – Weighted Percentage

Section K presents the total percentage of weighted score points achieved versus possible. This is the same calculation that we used for the raw percentage but uses the weighted score in column W instead of the raw score.


L – Weighted Rank

Section L gives us a view into the weighted ranking of the employee. Obviously, if you weighted each of the criteria the same, the weighted value and the raw values will be the same and thus so too will the rankings be the same.

M – Raw Rank Sort Buttons

The Up Arrow will enable you to sort the employees by ascending rank based on the raw scores and data whereas the Down Arrow will sort by descending rank.  To enable this sorting feature, I’ve created a button and an associated macro.

Sub btnSortUWAscending()
' btnSortUWAscending Macro
 ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("uwrank") _
 , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Sheet1").Sort
 .SetRange Range("alldata")
 .Header = xlGuess
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 End With
End Sub

You will notice the use of several named ranges.  For example, “alldata” is a named range which refers to =Sheet1!$A$5:$Y$24.  “uwrank” is a named range which refers to all of the values in the raw ranking calculated column.

You can use the Excel Name Manager to modify this if you should modify the spreadsheet significantly.

Named Range “alldata”

N – Weighted Rank Sort Buttons

The Up Arrow will enable you to sort the employees by ascending rank based on the weighted scores and data whereas the Down Arrow will sort by descending rank.

O – Hide / Unhide Detail Data

This button gives you the ability to hide or show the detail data from sections A, B, and F. The following image shows the result of hiding the detail data.

Detail Data Hidden

Use this button as a toggle to alternately show or hide the detail scores and criteria.

Analyzing the Results

As we can see from the summary view above, we have some issues to address.  The first thing you may gravitate toward is the lower end of the stack but be certain that you must pay just as much if not more attention to those consistently scoring highly.  Retaining top performers is of utmost importance.  You need to be certain that you have a plan to keep these top performers challenged, and interested.  The key to this unfortunately is not illuminated by a tool like this.  That will take more time and effort to get to know your team and what gets them motivated to come to work.

Once you’ve gone through the exercise of recording your team’s scores and reviewed the individual rankings, go back through the scores and make sure that you’ve been objective and that the scores are accurate and reflect real performance.  Also, avoid the temptation to craft the results to get a ranking according to a pre-conceived notion about your team.  Be objective and accept the rankings.  These rankings are a tool to improve the performance of your team as well as improving their level of satisfaction.


So there you have it, a complete example of a stack ranking system built in Microsoft Excel.  I hope you found this tutorial useful.  As with any tool the hard part is really in the implementation and making sure that you create realistic criteria upon which to judge your employees.  Another reminder to make absolutely certain that the criteria upon which you judge is the same that you’ve communicated to your employees.

If you’ve enjoyed this tutorial and would like a copy of the MS Excel Stack Ranking tool, simply click the following to get a free copy.  Keep in mind that this is a “Macro-Enabled” Microsoft Excel spreadsheet and will require you to enable macro content.  Also – this was created and has only been tested with Microsoft Office Excel Version 2010 although the functions and features used should be backward compatible to older versions of Excel.

Use “Tweet and Get It” or download the macro enabled spreadsheet below.

[tweegi-button name=”stack-ranking-tool”]


[download id=”15″]

Excel Project Planning Spreadsheet Version 2

The Excel Project Planning spreadsheet has been a handy tool for many of the projects I’ve worked on and I’ve continued to tweak and add features it. Therefore, I thought I’d add this post to talk about some of the enhancements and provide a link to the updated version.

Enhancements from Version 1 to Version 2

Task color coding based on progress against current day

As you can see from the attached image, the Gantt cells are now color coded.

A – Green cells indicate work that was scheduled and has been completed (100%) as indicated in column F.

B – Yellow cells indicate that tasks were scheduled and more than 50% of the task has been completed but the start date is now in the past.

C – Red. Cells are filled with Red when tasks are scheduled to have had work completed but have not yet started and the start date has come and gone.

D – Dark Blue.  As in version 1 of the Excel Project Planning Spreadsheet, cells filled with dark blue indicate work is scheduled to be started and completed on these dates.

Today’s Date – What/If Analysis

I’ve added a field and some conditional formatting noted in the above image by label E – which enable you to analyze the project as if today’s date were progressing beyond the actual date/time when you’re viewing the project planning spreadsheet.

Clicking the spinner in cell C29 – either up or down will increment the value of Today’s Date.  This will cause the conditional formatting in the Gantt area to show you what the project color coding will look like based on current progress on the date provided in cell B29.

This will also highlight another enhancement that I made to make progress against the current date a bit more obvious.  You’ll notice the dates that appear at the top of the Gantt area that are filled with the project dates (eg: 5/19).  These will now appear in a Grey filled font as they drift into the past.

All of these new enhancements have been accomplished using conditional formatting combined with functions.  To view these, select the cells where the formatting appears and from the home tab, click conditional formatting, then manage rules.

Here’s a link to the spreadsheet download.

[download id=”14″]

I hope you enjoy the new features.  Please Tweet, Like or G+ if so!  Also, if you have a feature request, feel free to comment or use the contact page to reach me.

Gantter Project – A Great, Free Way to Manage Projects

I’ve used OpenProj for several years. It’s a nice, free alternative to MS Project. The only problem is sharing the project plans with folks – rarely do people I work with have OpenProj installed. Fortunately, OpenProj gives you the option of saving as a MS Project XML file. This makes it easier but I was still looking for the ability to save the projects in PDF format. This is possible, but only with the paid version of OpenProj. This is when I found the Gantter Project.

Gantter is written to work with Google Apps. It enables you to import MS Project project plans, save them as PDF and even save them directly to Google Docs.

For me, Google Docs and the Gantter Project work best with my custom domain – mlynn.org – but you can use it even if you don’t have a custom domain. Visit Gantter.com to find out more information about this great, free tool to help you manage your projects.

Minimizing Hacks and Spams on Your WordPress Blog

As long as there’s money to be had by gaining exposure on the web, we’ll have to suffer with the less than reputable out there trying to game the system by filling up your blog with lame comments and links to their crappy porn, drug or adult dating web sites. You can minimize the impact of these n’er do wells, however by implementing several plugins and taking certain actions in response to their attempts.

Continue reading →

A Simple Excel Decision Matrix | The Lynn Decision Tool

Here’s a very simple Excel based decision tool which I’ve used in many different forms for both personal and business use over the years. In this basic format, this tool will enable you to list work through a decision process between two major choices.

The tool lets you provide two basic choices (cell C2 and cell E2), features of the overall decision (these are typically outcomes or impacts of your decision between the two choices), weights for each feature and a grade for each decision by feature.

The tool also provides a calculated score based on your grades and weights and a nice little chart showing which decision came out on top.

To use the tool follow these basic instructions:

1. Provide your two choices in cell C2 and cell E2. eg: Join Army, and Go to College
2. List the features of the decision as a whole in column A rows 3 through 16. You don’t need to complete a rows. You’ll want to focus on outcomes or impacts of your decision when listing these features. eg: Long term impact on life goals.
3. Weight each feature on a scale of 1-5 based on how much you value this feature in column B, rows 3 through 16.
4. Provide a grade for each decision as it pertains to the feature. For many decisions, this will be highly subjective… don’t give too much thought initially to your scores. Try to record your immediate reaction.

Note: Don’t put anything in the “Score” columns… these are computed fields.

That’s it – very simple as I said. This tool can be built upon in many ways. I wanted to keep it simple initially and provide a Simple Decision tool. If you have other tools you use to help you make decisions, please use the comments field and let me know.

[download id=”9″]

SEO 101 – 6 Tips for Optimizing Your Web Site’s Search Engine Visibility

SEO, or Search Engine Optimization is a bit of a black art.  One of the reasons for this is that there is not one simple algorithm or set of steps that each and every site owner can implement in order to increase their visibility or pagerank.  In this article, I’ll talk about some of the common sense, simple steps a web publisher can take to increase their visibility on the major search engines.

Tip #1 – Optimize Your Content

Google’s pagerank is based on hundreds of attributes including analysis of your site content.  Let’s start with some of the more obvious content elements:

Meta Tags

Meta tags are tags placed in the HEAD section of your web site’s pages.  Meta tags allow you to provide additional description elements for your site. You absolutely MUST provide the following Meta Tags if you want to increase your site’s visibility.

  • Description – provide a keyword rich description of your web site – obviously mentioning attributes of your product or service.
  • Keywords – A comma separated list of keywords associated with your site.  Use caution – DO NOT repeat keywords many times.  This is a mistake made by many SEO rookies and can lead to a lower pagerank.
  • Title – not technical a meta tag – but just as important, the title tag provides another opportunity to link your site to your primary target market.

Content Titles

Including the title tag in your page’s content is important. However, just as important is the actual content of the title tag. Obviously, ensure that the title is descriptive but also wherever possible make sure that the titles reference a keyword or something related to your primary content goal.

Tip #2 – Use Alt Tags in Your Image Tags

Alt tags are referenced by search engines during indexing.  The relevance of your site to your keywords of choice can be dramatically enhanced by loading your Alt tags up with keyword rich descriptions.


<img alt='search engine optimization' src=/images/seo.png>

Tip #3 – Be older

A sad fact is that if you’re domain name has been registered for less than one year, you’re not likely to get premium page ranking. Not to fear, you’ll simply need to leverage the remainder of these tips to increaser your visibility.

Tip #4 – Be faster

Google penalizes slower performing sites and sites with high initial page load times by giving them lower page ranks. To ensure that you’re site is snappy, make sure you’re optimizing your image sizes, combining and minimizing your javascript components and using a content distribution network where possible. CDN’s can be very expensive, but if you’re site has national or global appeal and you want to optimize your visibility, you may want to look into a CDN such as Akamai or Limelight. CDN’s are less important if you’re target market is more on the local scale.

Tip #5 – Increase Your Visibility by Increasing Links to Your Site

Yes, it is true that Google will rank your site higher if more sites link to your site – but even more important thank the number of sites linking to you is the pagerank of those sites that link to you.

Consider this example… You create a new site and then create fifteen other new domains and sites that simply link to your new site. You submit your sites to google and google indexes all of the sites. The value of each of the links to your site from your new link farm is minimal because the age and page rank of all of these sites is near zero.

Now consider another example… You create your new site. Then, you create a blogger.com, ezinearticles, tumblr, posterous, squidoo accounts and publish many articles all with a single link in the signature linking back to your primary site. Google ranks ezinearticles highly because of the diversity and consistency of it’s content. Therefore, you’re going to get higher credit for that single link that you would for all of the links you created in your newly established link farm.

To get started, create a free WordPress account. Be sure to try and get a wordpress blog name that reflects something similar to your primary site – or content. This will provide you with a free, easy to your blog on the WordPress domain – for example: mlynn.wordpress.com. Next, carefully craft an article on your blog that speaks to your primary site’s mission, content or target market. Be sure to include a link back to your primary site.

Start slow and do this with WordPress and possibly one other blog site. Be sure that you’re providing good quality content. Here are some sites to consider:

Tip #6 – Know Thy Enemy

Review your competitor’s sites. Evaluate their SEO success or failure. One tool I use to do this is the Chrome SEO Extension. Review the meta keywords and description tags of your competitors to make sure you’re not missing something. Do this on a regular basis to ensure that you’re keeping your meta tags up to date and in line with your target market.

Bonus Tip – Optimize Your Visibility Through Social Media

The increase in popularity of social media sites like Facebook and Twitter can’t be ignored. You’re not likely to get through a day without someone making reference to something they saw or some content that was linked to from facebook or twitter. Today’s savvy business owner must learn to leverage this powerful medium. However, a word of caution. Bad social media practice or improperly executed use of social media can be very damaging for your business reputation – so wade carefully in this new media stream.

Don’t hammer twitter with repetitive links to your site. This is annoying and people will show their disgust by unfollowing you, or even worse, commenting negatively. Again, start slow. Learning how to use twitter can take time – but will be well worth your effort. Once you sign up – use the “interest browser” to find other tweeters with similar businesses or interests. Watch their posts and where appropriate “retweet” their posts. Over time, others will find you and begin to follow your tweets.

A couple interesting usage tips for twitter: You’ll see a couple symbols in tweets – namely, “@” and “#”. The ampersand indicates a reference to another tweeter. For example, I’m @mlynn on twitter. The hash or pound sign is a reference to a tag or a subject on twitter. These can also be used by sites to determine inclusion of the post on other sites. For example, I’m using Linkedin for my professional profile and contacts. I’ve configured twitter and linked in such that when I include the hash sign “#in”, my tweet will be logged as my linkedin status.