How to Create an Automatic Excel Assessment or Test in WPS Office Spreadsheets
Question details
The user wants to create a simple Excel assessment that automatically calculates and displays a learner's score immediately after answers are entered or submitted.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- An instructor or trainer needs to build a self-scoring quiz in a spreadsheet for learners.
- Observed behavior
- The assessment should compare learner answers with correct answers and display the total score immediately after answers are entered or submitted.
Before building the quiz, list the questions, answer choices, correct answers, and scoring method in a separate sheet or table. Make sure your WPS Office Spreadsheets version supports Data Validation and formulas such as IF and IFS.
Create a Self-Scoring Excel Quiz Using Data Validation and IF Formulas
Use this when you want an automatic result as soon as the learner selects or types an answer, without locking or submission buttons.
This method uses dropdown lists for consistent answers and formulas to compare each response to the answer key. It works well for multiple-choice, true/false, or single-word answers.
In WPS Office Spreadsheets, enter learner questions in one column and create a hidden or separate answer-key area listing the correct answer for each question.
Select the learner answer cells, go to the Data tab, click Data Validation, choose List under Allow, and set the Source to the answer choices for that question.
In the score column, enter =IF(LearnerCell=CorrectAnswerCell,1,0), replacing the cell references with the actual learner answer cell and answer-key cell.
Use =SUM(ScoreRange) to total the scores, then add a result cell with =IFS(Total>=90,"Excellent",Total>=70,"Passed",TRUE,"Needs Review") or a simple Pass/Fail IF formula.
Create a Formal Assessment with a Form or VBA Submission Workflow
Use this if you need to lock answers, control submission, or collect responses outside the visible quiz sheet.
Build a Self-Scoring Excel Assessment in WPS Office Spreadsheets
WPS Office Spreadsheets lets you create an automatic quiz with dropdown answer lists, IF comparison formulas, a total score, and an immediate Pass/Fail result—no extra tools needed.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open a new blank spreadsheet or your existing .xlsx assessment file.
- 2. Create answer choices and response cells: Enter questions and answer choices, then use Data > Data Validation > List to add dropdown response cells.
- 3. Add scoring formulas: Use =IF(UserAnswer=CorrectAnswer,1,0) for each question and =SUM(range) for the total.
- 4. Display the final result: Add =IFS(Total>=70,"Passed",TRUE,"Needs Review") or a similar formula in the result cell.
Frequently Asked Questions
Can I make the answer cells dropdown lists?
Yes. Select the learner answer cells, go to the Data tab in WPS Office Spreadsheets, click Data Validation, choose List under Allow, and set the Source to the answer choices.
How do I display Pass/Fail instead of a numeric score?
Use =IF(Total>=70,"Passed","Failed") in the result cell, or use =IFS for graded bands such as Excellent, Passed, and Needs Review.
Will the score update automatically after the learner answers?
Yes. WPS Office Spreadsheets recalculates formulas automatically, so when a learner answer changes, the IF comparison formulas, SUM total, and result cell update immediately.
Can I lock the quiz after the learner submits?
Yes. You can use Review > Protect Sheet to lock the quiz, or use a submit button and a VBA macro from the Developer tab for a more formal submission workflow.
Which file format should I use to keep Excel compatibility?
Save the assessment as .xlsx to keep full compatibility with WPS Office and Microsoft Excel. If you use macros, save it as .xlsm instead.
