Build an Employee Performance Tracker in Excel

An employee performance tracker in Excel is a simple table of review scores that Excel summarizes for you: averages by person, team and quarter, a label for each score, and a quick view of the highest results. This guide is for team leads, HR coordinators and anyone asked to keep review scores in a spreadsheet. It covers the layout and formulas only; how scores are set and used is up to your organization's own process.

What goes in a performance tracker?#

A performance tracker records the same few facts for every review: who, which team, which period, and the result. Keep it to the columns you'll actually use:

You might add goal columns, a reviewer, or a date. Keep long comments out of the main table, or in a final column, so the numbers stay easy to work with.

Set up the tracker: one row per review#

Put one row per employee per period, not one column per quarter. A long table like this means a new quarter adds rows, and every formula and pivot table keeps working. Here is the example data in A1:D8:

Turn it into an Excel table with Ctrl+T (Mac: ⌘T) so new rows are picked up by formulas automatically. Add a drop-down list for Team and Quarter with Data > Data Validation > List to stop spelling differences like "support" and "Suport".

Average scores with AVERAGEIFS#

AVERAGEIFS averages the cells that meet every condition you give it. The range to average always comes first, then pairs of "range to check, what to match".

For a summary grid, put the team names down column G and the quarters across row 1, then use cell references instead of typed text, with $ to lock the ranges:

=AVERAGEIFS($D$2:$D$8,$B$2:$B$8,$G2,$C$2:$C$8,H$1)

Fill it across and down to complete the grid. If a team has no scores for a quarter, AVERAGEIFS returns #DIV/0!; wrap it in IFERROR(...,"") to show a blank. The AVERAGEIFS function guide and the article on AVERAGEIFS with several criteria cover the details.

Label each score with IFS#

IFS checks conditions in order and returns the result for the first true one. In E2, type:

=IFS(D2>=4,"Above target",D2>=3,"On target",TRUE,"Below target")

Fill it down. Amara's Q1 score of 3.5 becomes On target, and her Q2 score of 4 becomes Above target. The final TRUE catches every score below 3.

Check the highest band first, or every score of 4 or more would stop at the >=3 test. The labels and cut-offs here are only an example. Use the bands from your organization's own review scheme. IFS needs Excel 2019 or later, or Microsoft 365; the IFS function guide shows the nested IF version for older Excel.

Find the top scores with LARGE#

LARGE returns the nth largest value in a range, and SMALL returns the nth smallest.

• =LARGE(D2:D8,1) returns 4.5, the highest score.
• =LARGE(D2:D8,2) returns 4.
• =LARGE(D2:D8,3) also returns 4, because two scores tie at 4.
• =SMALL(D2:D8,1) returns 3, the lowest score.

To show who has the top score, look up the name next to it. In Excel 2021 or Microsoft 365:

=XLOOKUP(MAX(D2:D8),D2:D8,A2:A8)

This returns Lena. With ties, a lookup returns only the first match, so check the list by eye too. For a full ranking, the guide to the RANK function explains how ties are numbered.

Highlight results with conditional formatting#

Conditional formatting colors cells automatically based on their value, so patterns stand out:

1. Select the scores, D2:D8.
2. Go to Home > Conditional Formatting > Color Scales and pick a scale.

To color the label column, select E2:E8, choose Highlight Cells Rules > Equal To, type Above target and pick a fill. Repeat for the other labels. The conditional formatting guide shows formula-based rules too, such as coloring a whole row.

Keep the colors calm and consistent. The goal is to make the numbers easy to read, not to make a judgement on screen. For a one-page view of the averages, the guide to building a KPI summary in Excel shows a clean layout, and the guide to HR analytics in Excel covers headcount and turnover alongside scores.

Keep the tracker accurate and private#

A tracker is only useful if people can trust it. A few practical habits help:

• Use formulas, not typed results, so averages and labels always match the scores.
• Use the same scale and periods for everyone, and note the scale on a separate sheet.
• Limit who can open the file. Performance data is personal. Store and share it the way your organization's policies require.
• Protect formula cells with Review > Protect Sheet so they aren't overwritten by accident. Sheet protection prevents mistakes; it is not strong security.

Practise it#

AVERAGEIFS is the formula you'll use most in a tracker. Practise AVERAGEIF, MAXIFS and MINIFS in a live spreadsheet: you type the formula and it checks it instantly. Then find top results in the LARGE and SMALL lesson and color them in the conditional formatting lesson. Try the AVERAGEIFS exercise on this page first.