How to Find Your Top Performers Using a
Productivity Score in Excel

Top 10% Productivity Contribution %

1. Create the weighted points system

• Start by building a points column that combines the three productivity variables used in the video:
  • Revenue
  • Gross profit
  • Jobs completed
• Assign weights to each variable so the ranking is not based on just one measure.
• In the example shown, revenue carries the largest weight, gross profit carries the next largest weight, and jobs completed receives the remaining weight.
• For jobs completed, the video uses an average deal size assumption to convert completed jobs into points.
• Adjust these weights based on how your business wants to define productivity.

2. Create a year field from the date column

• Add a helper column that extracts the year from the existing date field.
• Use the TEXT function to convert each record into a year value that can be used for filtering later.
• This makes it possible to switch between years and recalculate the ranking dynamically.

3. Build a unique list of producers only

• Create a list that includes only employees marked as producers.
• Use the FILTER function to return names where the producer flag equals one.
• Since that list will still contain duplicate names, wrap it with the UNIQUE function.
• Then use the SORT function to display the final producer list in alphabetical order.
• Paste the result as values if you want a fixed list.

4. Add a dropdown to filter by year

• Insert a dropdown cell that lets the user choose the reporting year.
• Use Data Validation with a list of available years.
• In the example, the dropdown includes multiple years so the ranking can update when the selected year changes.

5. Calculate total points by employee and year

• Use the SUMIFS function to total the points for each employee based on:
  • The selected year
  • The employee name
• This gives each producer a total annual point value using the weighted productivity system.
• Lock the year reference so the formula copies down correctly for every employee.

6. Rank all employees by total points

• Use the RANK.AVG function to rank employees based on their total points.
• Lock the full ranking range before dragging the formula down.
• This creates a ranked productivity list that updates automatically when the year changes.

7. Create a Top 10 ranking list

• Set up a simple list with the numbers 1 through 10.
• This will act as the lookup driver for your Top 10 employees.
• Because rankings can change by year, this section is built to update dynamically rather than manually.

8. Return the Top 10 employee names

• Use INDEX and MATCH to pull the employee name associated with each rank from 1 to 10.
• The lookup searches for rank 1, rank 2, rank 3, and so on, then returns the matching employee name.
• Copy the formula down through all 10 positions.

9. Return the Top 10 point values

• Use INDEX and MATCH again, this time to return the point total for each ranked employee.
• This gives you the Top 10 employees and their corresponding point values side by side.
• These values update automatically when the year selection changes.

10. Calculate each person’s productivity contribution

• Divide each Top 10 employee’s point total by the sum of all employee points.
• Use the SUM function for the total points denominator.
• Lock only the total range so the percentage formula copies down correctly.
• Format the result as a percentage.

11. Calculate the Top 10% Productivity Contribution %

• Sum the contribution percentages for the Top 10 ranked employees.
• This gives the final percentage showing how much of total weighted productivity comes from the top group.
• The result changes dynamically based on the selected year and the point system being used.

12. Create a chart for the Top 10 employees

• Highlight the Top 10 employee names and their point totals.
• Go to Insert and select one of the recommended charts.
• This creates a visual ranking of the highest contributors based on the weighted points system.

13. Build a dynamic chart title

• Create a title cell that combines static text with the selected year.
• Use cell references so the title updates automatically when the dropdown year changes.
• Link the chart title to that cell through the formula bar.
• This keeps the visual aligned with the selected reporting year.

14. Adjust the model as needed

• The weights used in the video are only one example.
• You can increase or reduce the importance of revenue, gross profit, or jobs completed depending on how you want to define productivity.
• The main idea is to avoid ranking employees using only one variable when productivity is influenced by several factors.