How to Make a Self-Marking Quiz in Excel

You can make a quiz in Excel that marks itself with two everyday functions: IF compares each answer with the correct one, and COUNTIF adds up the score. Drop-down lists keep answers tidy, a hidden key sheet holds the correct answers, and a few extra formulas give instant feedback. This guide builds a five-question quiz step by step, for teachers, trainers and anyone who wants to practise IF and COUNTIF on a real project.

How a self-marking quiz works#

A self-marking quiz has two sheets. The Quiz sheet holds what people see: questions, answer cells, marks and the score. The Key sheet holds the correct answers. Formulas on the Quiz sheet compare the two.

Here is the Quiz sheet:

Create a second sheet named Key, with the question numbers in A2:A6 and the correct answers in B2:B6: SUM, 2.5, $, Ctrl+Z and COUNTIF. Keep each answer in the same row as its question on the Quiz sheet, so row 2 always matches row 2.

If you need questions for an Excel skills quiz, our list of Excel quiz questions with answers is a good source.

Step 1: Add drop-down lists for the answers#

A drop-down list makes sure people pick an answer exactly as the key spells it. A typed "Sum " with a trailing space would be marked wrong, even though it is right.

1. Click C2, the answer cell for question 1.
2. Go to Data > Data Validation.
3. Under Allow, choose List.
4. In Source, type the options separated by commas: SUM,COUNT,AVERAGE.
5. Click OK, then repeat for each question with its own options.

To reuse the same options, such as True and False, set up one cell and copy it down. Our guide to drop-down lists in Excel covers lists that read from a range of cells.

Step 2: Mark each question with IF#

IF checks a condition and returns one value if it's true and another if it's false. In D2, type:

=IF(C2="","",IF(C2=Key!B2,1,0))

Fill it down to D6. Read it from the outside in:

• C2="" checks whether the question is unanswered. If so, the mark stays blank instead of showing 0.
• C2=Key!B2 compares the answer with the key. A correct answer scores 1, a wrong one 0.

The = comparison ignores capital letters, so "sum" matches "SUM". If case must match, use EXACT(C2,Key!B2) as the test instead. For typed answers, wrap the answer in TRIM, TRIM(C2)=Key!B2, so extra spaces don't cost a mark.

To accept more than one answer, test each with OR: =IF(OR(C5="Ctrl+Z",C5="Control+Z"),1,0). The IF function guide and our article on how the IF function works cover more patterns.

Step 3: Add feedback and the score#

Feedback tells people right away how they did on each question. In E2, type and fill down:

=IF(D2="","",IF(D2=1,"Correct","Not quite"))

Then add a score panel to the right, in G1:H4:

COUNTIF counts the cells in D2:D6 that equal 1, which is the number of correct answers. The Result formula waits until every question is answered, then compares the percentage with a pass mark of 80%. Change 0.8 to set your own pass mark. The COUNTIF function guide and our COUNTIF article explain the criteria.

To colour the marks, select D2:D6, choose Home > Conditional Formatting > Highlight Cells Rules > Equal To, and set 1 to green fill. Add a second rule for 0 with red fill.

Add checkbox questions#

For "select all that apply" questions, checkboxes work well. In Excel for Microsoft 365, select the cells and choose Insert > Checkbox. Each box holds TRUE when ticked and FALSE when not.

Say options for one question sit in C8:C11, and the key has TRUE or FALSE for each option in Key!B8:B11. This marks the question correct only if every box matches:

=IF(AND(C8:C11=Key!B8:B11),1,0)

In older versions of Excel, use Developer > Insert > Check Box (Form Control) and link each box to a cell. Older versions may also need Ctrl+Shift+Enter for the range comparison above. Our guide to checkboxes in Excel covers both types.

Hide the answer key#

Hide and protect the key so people don't simply look up the answers:

1. Right-click the Key tab and choose Hide.
2. Go to Review > Protect Workbook, tick Structure, and set a password. Now the sheet can't be unhidden without it.
3. On the Quiz sheet, select the answer cells C2:C6, press Ctrl+1 (Mac: ⌘1), open the Protection tab and untick Locked.
4. Select the mark and feedback formulas, and on the same tab tick Hidden, so the formulas don't show in the formula bar.
5. Go to Review > Protect Sheet and set a password.

People can now choose answers but can't change formulas or see the key. Excel's protection is designed to prevent accidental changes, not to stop someone determined, so don't use it for high-stakes tests. Our guide to protecting a sheet and locking cells has more detail.

Ideas to extend the quiz#

Once the basic quiz works, try these:

• A results sheet that records each person's score for a class, like a gradebook in Excel.
• Points per question, with a Points column and =SUMIF(D2:D6,1,F2:F6) to add the points for correct answers.
• A reset step: select the answer cells and press Delete. The marks, feedback and score clear themselves.

Practise it#

Try the exercise on this page to score a quiz with COUNTIF. Then practise the IF function in a live spreadsheet to write marking formulas, and practise COUNTIF to count correct answers and pass marks. Both lessons check your formula instantly. To test your own skills, take the Excel level test.