Rafiul Haq
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”.
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)/100Our total scale of the weights is 100. We’re dividing it with the weight and multiplying it with the employees’ scores.
- 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.
- After that, press ENTER.
Here, we’ve got 2 as the position of 58 in that range.
- Finally, AutoFill the formula.
Thus, we’ve got the Stack Rank of the employees in Excel. The weight was inequal, and we got “Nolan” as the best employee.
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.
- 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.
- 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
- How to Rank with Ties in Excel (5 Simple Ways)
- Calculate Rank Percentile in Excel (7 Suitable Examples)
- Rank IF Formula in Excel (5 Examples)
- How to Calculate the Top 10 Percent in Excel (4 Ways)
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.
Steps:
- Firstly, type the following formula in cell C13.
=RANK(C5,C$5:C$10)+COUNTIF(C$5:C5,C5)-1- 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.
- 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.
- 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)-1This 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.
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!
