How to Create an Answer Key for Tests in Excel or WPS Spreadsheet

An answer key is the fastest way to grade a test, and a spreadsheet is the fastest way to build one. Instead of retyping answers for every version of an exam, you type the correct answers once, then let lookup formulas pull them into any test layout. This guide shows how to create an answer key for tests in Excel or WPS Spreadsheet, including shuffled test versions and automatic grading.

The same steps work in Microsoft Excel and WPS Spreadsheet because both support INDEX, MATCH, and IF with identical syntax. You will build a basic answer key, expand it for multiple test versions, and finish with a self-scoring sheet.

How to Create an Answer Key for Tests in Excel or WPS Spreadsheet

What Is an Answer Key and Why Build One in a Spreadsheet

An answer key is a list of every question number paired with its correct answer. Teachers use it to grade consistently, students use it to check practice work, and trainers use it to score onboarding quizzes. A good answer key can also record the point value of each item and a short explanation for open questions.

A spreadsheet beats a paper key because the answers become data. Once your answer key lives in cells, you can reuse it for every class, share it without retyping, and let formulas match questions to answers automatically. That also removes the most common grading mistake: marking an answer wrong because the questions were shuffled in another test version.

Answer key maps questions to answers

Use Word, Excel, and PPT for FREE

Free Download

How to Build a Basic Answer Key in WPS Spreadsheet

Start with the master version of your test. The goal is one row per question, with the correct answer in its own column so formulas can read it later:

1. Open a new workbook in WPS Spreadsheet and create two column headers: Question in A1 and Answer in B1.

2. List the question numbers in column A, one per row (1, 2, 3, and so on down the sheet).

3. Type the correct answers in column B next to each question number. Use a consistent format, such as capital letters A, B, C, D for multiple choice.

4. Keep the key on its own sheet if the workbook also holds student responses. A sheet named Key keeps the answers separate from the test itself.

5. Save the file as .xlsx so it opens in both WPS Office and Microsoft Excel without conversion.

The screenshot below shows a finished basic answer key: question numbers in column A and correct answers in column B, ready to be referenced by formulas.

Answer key table in WPS Spreadsheet

Use Word, Excel, and PPT for FREE

Free Download

How to Create an Answer Key for Shuffled Test Versions with INDEX and MATCH

When you print several versions of the same test, the questions are the same but the order differs. Retyping the key for each version invites errors. Instead, build one question key that records where each master question appears in every version, then let a lookup formula write the answer key for you:

1. Keep the master answer key in columns A and B: question numbers 1–19 in A, correct answers in B.

2. Add a question key block to the right, one column per test version. In the column for Test B, each row holds the master question number that appears at that position in Test B. For example, if question 1 of Test B is question 13 of the master test, that cell reads 13.

3. Enter the lookup formula in the first answer cell of the version column:

=INDEX($B$5:$B$23,MATCH(C5,$A$5:$A$23,0))

This reads: find the master question number stored in C5 inside the question column, then return the answer stored next to it in column B. The dollar signs lock the master ranges so they stay fixed while the formula is copied.

4. Copy the formula across and down with the fill handle until every version column is filled. Each column becomes the complete answer key for that test version.

5. Spot-check a few rows against the printed tests. If a wrong answer appears, the question key numbers, not the formula, are usually at fault.

Because the question numbers only point to where a question lives, MATCH finds the question in the master list first and INDEX then returns its answer. This two-step lookup is exactly why the INDEX and MATCH combination works so well here; you can see more examples in our guide to the Excel INDEX MATCH function.

INDEX MATCH matches shuffled test questions

How to Auto-Grade with an Answer Key

Once the answer key exists, the sheet can grade for you. Put each student's answers in a column next to the key, then compare them row by row:

1. Copy the key answers into a column beside the student responses, or reference the Key sheet directly.

2. Compare each answer with a formula such as =IF(C2=$B$2,1,0), which returns 1 for a match and 0 for a miss.

3. Total the results with =SUM(D2:D21), or count correct responses with =COUNTIF(D2:D21,1).

4. Convert to a percentage by dividing the score by the question count, for example =SUM(D2:D21)/19.

Because the comparison references the answer key, updating one answer in the key updates every student's score. For lookup-based grading across sheets, the same logic shown in our VLOOKUP between two spreadsheets guide also applies. Modern XLOOKUP works too, and you can read when to prefer it in our XLOOKUP vs VLOOKUP comparison.

Auto grading answers against answer key

Use Word, Excel, and PPT for FREE

Free Download

The Best Free Alternative: WPS Office for Answer Keys

WPS Spreadsheet handles every step above for free: multiple answer key sheets, INDEX MATCH lookups, and auto-grading formulas, with full .xlsx compatibility. WPS Office also bundles Writer and Presentation, so the same test file can hold the questions, the answer key, and the printable exam in one workbook. If you grade tests regularly, it is a practical free alternative to Microsoft Office.

WPS Office home screen overview

FAQs

What should an answer key include?

At minimum, each question number with its correct answer. For longer tests, add the point value per question and a short explanation for open-ended items so anyone grading with the key reaches the same result.

Can I build an answer key in Excel and open it in WPS Spreadsheet?

Yes. WPS Spreadsheet opens .xlsx files directly and supports INDEX, MATCH, IF, COUNTIF, and SUM with the same syntax, so a key built in Excel works without changes.

How do I make an answer key for multiple test versions?

Keep one master answer key, add a question key that records where each master question appears in every version, and use INDEX with MATCH to generate each version's answer column automatically.

Why does my lookup formula return #N/A?

The question number was not found in the lookup range, or the values do not match exactly. Check for extra spaces, number-vs-text formatting, and confirm the ranges are locked with dollar signs before copying the formula.

Is a formula-based answer key better than grading by hand?

For a handful of questions, manual grading is fine. For 100 or more questions, or several shuffled versions, formulas are faster and do not make copying mistakes, and updating one answer updates every score.

Free Office Download

   

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

100% secure

Algirdas Jasaitis