How to Stack Rank Employees in Excel (3 Methods)

We’ll use the following dataset, which contains four columns for employee attendance.

How to Stack Rank Employees in Excel: 3 Easy Ways

Method 1 – Using RANK Functions to Stack Rank Employees in Excel

We’ll take the individual scores and, using the assigned weights, we’ll find the overall score, which will be used to rank the employees. The weights are displayed in a separate table.

Steps:

Use the following formula in cell F5.

=(C5*$C$13)/100+(D5*$C$14)/100+(E5*$C$15)/100

The total scale of the weights is 100. We’re dividing that with each weight and multiplying it with the employee scores.

Press Enter.

Press Enter and AutoFill the formula into the rest of the cells.
Use this formula in cell G5.

=RANK(F5,$F$5:$F$10)

Press Enter.
AutoFill the formula.

We’ve got the Stack Rank of the employees in Excel. You can use that column to sort the dataset.

Read More: Excel Percentile Rank Inc vs Exc

Method 2 – Stack Rank Employees in Excel by Merging SUMPRODUCT and RANK Functions

The weights are given in the percentage format.

Steps:

Use 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. Our formula basically calculates the following in the first row: C5*B14 + D5*C14 + E5*D14.

Press Enter.

AutoFill the formula.
Use the following formula in cells 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.

Press Ctrl + Enter.

Method 3 – Use Combined Functions for Equal Weight to Stack Rank Employees

Each aspect is weighed equally, so we’ll also rank the employees on each criterion.

Steps:

Use the following formula in cell C13.

=RANK(C5,C$5:C$10)+COUNTIF(C$5:C5,C5)-1

Formula Breakdown

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