How to Stack Rank Employees in Excel (3 Methods)

In this article, we’re going to show you 3 methods of how to Stack Rank employees in Excel. To demonstrate our solutions, we’ll be using a dataset containing 4 columns: “Name”, “Work Pace”, “Quality of Work”, and “Attendance”.

how to stack rank employees in excel


Download Practice Workbook


3 Ways to Stack Rank Employees in Excel

1. Using RANK Functions to Stack Rank Employees in Excel

For the first method, we’ll be using the RANK function to Stack Rank Employees.

We’ll take the individual scores and using the assigned weights we’ll find the overall score. Then, we’ll use the RANK function to reach our goal.

Steps:

  • Firstly, type the following formula in cell F5.
=(C5*$C$13)/100+(D5*$C$14)/100+(E5*$C$15)/100

Our total scale of the weights is 100. We’re dividing it with the weight and multiplying it with the employees’ scores.

how to stack rank employees in excel

  • Secondly, press ENTER.

We’ve got our weighted score of all the criteria.

  • Thirdly, press ENTER and AutoFill the formula into the rest of the cells.
  • Then, type this formula to cell G5.
=RANK(F5,$F$5:$F$10)

The RANK function returns the position of a number in a range. We’re finding out the position of 58 in the range F5:F10.

how to stack rank employees in excel

  • After that, press ENTER.

Here, we’ve got 2 as the position of 58 in that range.

  • Finally, AutoFill the formula.

how to stack rank employees in excel

Thus, we’ve got the Stack Rank of the employees in Excel. The weight was inequal, and we got “Nolan” as the best employee.

how to stack rank employees in excel

Read More: Ranking Data in Excel with Sorting (3 Quick Methods)


2. Stack Rank Employees in Excel by Merging SUMPRODUCT & RANK Functions

For the second method, we’re going to use SUMPRODUCT & RANK functions to Stack Rank Employees. Moreover, the weights are given in percentage format.
Here, we’ll get the overall scores by using the assigned weights in the SUMPROUDCT function, after that, we’ll use the RANK function to find the best employee.

Steps:

  • Firstly, type the following formula in cell F5.
=SUMPRODUCT(C5:E5,$B$14:$D$14)

The SUMPRODUCT function multiplies a range of numbers and returns the sum of those numbers. Our formula basically does this -> C5*B14 + D5*C14 + E5*D14.

how to stack rank employees in excel

  • Secondly, press ENTER.

This will return the weighted total score of the employees.

  • Thirdly, AutoFill the formula.
  • Then, type another formula from below to cell G5:G10.
=RANK(F5,$F$5:$F$10)

The RANK function returns the position of a number in a range. We’re finding out the position of 56 in the range F5:F10.

how to stack rank employees in excel

  • Finally, press CTRL + ENTER.

In conclusion, we’ve got the Stack Rank of the employees in Excel. The weight was inequal, and we got “Nolan” again as the best employee.

Read more: How to Rank Within Group in Excel (3 Methods)


Similar Readings


3. Use of Combined Functions for Equal Weight to Stack Rank Employees

For the final method, we’re going to use the SUM, COUNTIF & RANK functions to Stack Rank employees. This time, we’re considering equal weight for the criteria.
Moreover, we’ll Rank the employees on each criterion, and lastly, R1, R2, and R3 refer to the Ranking of the individual score in the respective criteria.

how to stack rank employees in excel

Steps:

  • Firstly, type the following formula in cell C13.
=RANK(C5,C$5:C$10)+COUNTIF(C$5:C5,C5)-1

Formula Breakdown

  • We’ve two parts to this formula.
  • The first one is the RANK function, which returns the position of a number in a range. We’re finding out the position of 50 in the range C5:C10.
  • The second one is the COUNTIF function, which counts cells with a defined condition.
  • We’re using this to eliminate any duplicate positions in the Rank. Moreover, we’re subtracting 1 to stop our formula from adding when there is no duplicate.

  • Secondly, press ENTER.

This will show the position of 50 in our range.

how to stack rank employees in excel

  • Then, AutoFill the formula.
  • After that, use the Fill Handle to AutoFill the formula to the right side.

We’ll get the position of the rest of the cells.

  • After that, type this formula into the cell range F13:F18.
=SUM(C13:E13)

The SUM function performs addition opeartion. This is the operation: C13 + D13 + E13.

how to stack rank employees in excel

  • Then press CTRL + ENTER.

This will AutoFill the formula.

  • After that, type this formula in cell G13.
=RANK(F13,$F$13:$F$18,1)+COUNTIF(F$13:F13,F13)-1

This formula is similar to the formula just above.

  • Finally, press ENTER and AutoFill the formula.

Thus, we’ve shown you the final method of Stack Rank employees with equal weight to the criteria.

how to stack rank employees in excel

Read More: How to Calculate Weighted Ranking in Excel (4 Ways)


Practice Section

We’ve provided practice datasets for each method in the Excel file.


Conclusion

We’ve shown you 3 methods of how to Stack Rank employees in Excel. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo