How to Make an Interview Scorecard in Excel for Hiring

An interview scorecard in Excel turns interview notes into numbers you can compare: a list of criteria, a weight for each, and a score for every candidate. SUMPRODUCT combines them into one weighted score, AVERAGEIFS handles a panel of interviewers, and a few checks keep the data clean. This guide builds the spreadsheet step by step. It covers the Excel side only: which criteria and weights to use is a decision for your organisation and its hiring policies.

How an interview scorecard is laid out#

A scorecard needs three things: the criteria, how much each one counts, and the scores. The simplest layout puts criteria down the rows and candidates across the columns:

The scores use a 1 to 5 scale. Whatever scale you choose, write it at the top of the sheet, and consider adding a short description for each level so everyone reads the numbers the same way. Put =SUM(B2:B6) in B7 to check the weights add up to 100%.

Calculate the weighted score with SUMPRODUCT#

The weighted score multiplies each score by its weight and adds the results. SUMPRODUCT does both in one step. In C8, type:

=SUMPRODUCT($B$2:$B$6,C2:C6)/SUM($B$2:$B$6)

For Sam, that's 0.30×4 + 0.25×3 + 0.20×5 + 0.15×4 + 0.10×3 = 3.85. Dividing by the total weight keeps the answer on the 1 to 5 scale, and it still works if the weights add up to 10 or 50 instead of 100%.

The $ signs lock the weights. Fill the formula right to D8 and E8 (Windows: Ctrl+R; Mac: ⌘R), and the score range moves to D2:D6 and E2:E6 while the weights stay put. Priya gets 3.95 and Lee 3.75.

The SUMPRODUCT function guide explains how it multiplies ranges, and the article on weighted averages in Excel shows why a plain AVERAGE would give the wrong answer here: it treats every criterion as equally important.

Average a panel's scores with AVERAGEIFS#

When several people interview each candidate, keep the raw scores in a long list with one row per score. This is easier to fill in and to check than one grid per interviewer:

To pull Sam's average Communication score into the scorecard:

=AVERAGEIFS(D2:D50,A2:A50,"Sam",C2:C50,"Communication")

The result is (4 + 5) ÷ 2 = 4.5. In the real scorecard, keep this list on its own sheet, named Panel, and point to the scorecard headers instead of typing names: =AVERAGEIFS(Panel!$D$2:$D$50,Panel!$A$2:$A$50,C$1,Panel!$C$2:$C$50,$A2). The mixed references (C$1 and $A2) let one formula fill the whole grid. The AVERAGEIFS function guide covers the syntax, and the article on AVERAGEIFS with several conditions has more examples.

AVERAGEIFS skips blank scores, so an interviewer who didn't assess a criterion doesn't pull the average down. If no one scored it, AVERAGEIFS returns #DIV/0!, which you can catch with IFERROR.

Handle missing scores#

SUMPRODUCT treats a blank score as 0, which unfairly lowers the weighted score. If Sam's Teamwork score in C5 is blank, the basic formula returns 3.25 ÷ 1 = 3.25 instead of a fair score.

To score only the criteria that were assessed, divide by the weights of the filled-in scores:

=SUMPRODUCT($B$2:$B$6,C2:C6)/SUMIFS($B$2:$B$6,C2:C6,"<>")

SUMIFS adds the weights where the score is not blank: 0.85. The result is 3.25 ÷ 0.85 ≈ 3.82. Whether to compare candidates with different numbers of scored criteria is a judgement call, so it helps to flag incomplete scorecards with =COUNTBLANK(C2:C6)>0.

Keep scores valid with data validation#

Data validation stops typing mistakes before they reach the formulas. To limit scores to whole numbers from 1 to 5:

1. Select the score cells, such as C2:E6.
2. Go to Data > Data Validation.
3. Under Allow, choose Whole number. Set Minimum to 1 and Maximum to 5.
4. On the Error Alert tab, write a message such as "Scores must be 1 to 5."

Now a 7 or a 3.5 is rejected. Add a drop-down list for the Criterion and Candidate columns in the panel list too, so "Communication" is never typed as "Comms", which AVERAGEIFS would treat as a different criterion.

Rank and compare candidates#

Once each candidate has a weighted score, RANK.EQ puts them in order. With the scores in C8:E8:

=RANK.EQ(C8,$C$8:$E$8)

Fill it right: Priya is 1, Sam is 2 and Lee is 3. Equal scores share a rank. The guide to the RANK function explains ties and ascending order.

Use conditional formatting to make the grid easier to read. A Color Scale on C2:E6 shades low scores red and high scores green, so you can see at a glance where candidates differ. Keep in mind that a weighted score summarises the interview; it doesn't replace the notes behind it. Link the scorecard to your job application tracker or candidate list by name or ID so the scores and the rest of the record stay together.

Keep the scorecard fair and private#

A few spreadsheet habits help the file stay consistent and secure:

• Lock the formulas. Unlock only the score cells, then protect the sheet (Review > Protect Sheet) so no one overwrites a weight by accident.
• Fix the weights before interviews start. Changing them after you've seen the scores changes the ranking, so record any change and the date.
• Limit who can open the file. Interview scores are sensitive information about people. Store and share the file the way your organisation's policies require.

For other HR spreadsheet tasks, from headcount to tenure, see the Excel skills for HR jobs.

Practise it#

A scorecard comes down to weighted sums and conditional averages. Practise AVERAGEIF, AVERAGEIFS and MAXIFS in a live spreadsheet: you type each formula and it checks it instantly. Then try the HR roster interview drill for timed-test style people-data questions, and start with the exercise on this page to calculate Sam's weighted score.