How to Automatically Score Yes-or-No Microsoft Forms Responses in Excel with WPS Office
Question details
The user needs to automatically score Yes-or-No Microsoft Forms responses in an Excel-compatible workbook by assigning a point value to each question and calculating total scores with formulas that update as new responses arrive.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user receives Microsoft Forms responses in an Excel workbook and wants to score each Yes-or-No answer automatically instead of grading manually.
- Observed behavior
- Each Yes answer is automatically converted to the assigned point value, and a total score is calculated for every response row as new form submissions arrive.
Make sure the Microsoft Forms responses are exported to an .xlsx workbook and open it in WPS Office Spreadsheets. Confirm that the Yes-or-No answer cells use the exact text “Yes” or “No,” because the formulas in this guide rely on that exact match.
Score Yes-or-No Responses with a Scoring Row and IF-VLOOKUP Formulas
Use this method when you need a quick, fixed-range scoring setup and want each Yes answer to pull its point value from a scoring row.
This approach works well for forms where each question has one correct Yes-or-No answer and the point values are stored in a dedicated scoring row above the response data.
Launch WPS Office, click Menu > Open, select the exported .xlsx file from Microsoft Forms, and go to the worksheet that contains the responses.
Below the question headers or in a dedicated scoring row, enter the numeric point value to award for a Yes answer in each question column. Keep the No response point value as 0 in the formula.
In the first empty cell under a question column, enter =IF(B3="Yes",VLOOKUP(B2,A2:D2,COLUMN(D2)-COLUMN(A2)+1,FALSE),0), replacing B3 with the first response cell and B2/A2:D2 with your scoring-row range.
Select the formula cell, drag the fill handle across the remaining question columns, and adjust the references so each column reads from its own response cell and the same scoring row.
In the next empty column, enter =SUM(E3:E100) or the range covering all scored question cells for that row, then fill the formula down to calculate each respondent’s total.
Use an Excel Table So Scoring Formulas Expand Automatically
Choose this method if you export Microsoft Forms responses regularly and want the scoring formulas and total column to extend without adjusting ranges.
Automatically Score Yes-or-No Microsoft Forms Responses in WPS Office Spreadsheets
Use WPS Office Spreadsheets to assign point values to Yes-or-No Microsoft Forms answers and calculate totals with IF, VLOOKUP, and SUM formulas. The workbook stays compatible with Excel .xlsx files and recalculates totals as new responses arrive.
- 1. Download and install WPS Office: Download WPS Office free from the official WPS site, install it, and launch WPS Spreadsheets.
- 2. Open the Microsoft Forms export: Click Menu > Open and choose the .xlsx file exported from Microsoft Forms.
- 3. Create the scoring row and answer formula: Enter point values for each question row and use =IF(B3="Yes",VLOOKUP(B2,A2:D2,COLUMN(D2)-COLUMN(A2)+1,FALSE),0) to convert each Yes answer into points.
- 4. Add a total formula or Excel Table: Use =SUM(E3:E100) or insert an Excel Table so new response rows are included automatically.
- 5. Save and share the workbook: Save the file as .xlsx to keep it compatible with Microsoft Excel and Microsoft Forms workflows.
Frequently Asked Questions
Can I score Yes-or-No Microsoft Forms responses automatically without opening Excel?
Yes. Export the Microsoft Forms responses as an .xlsx file, open the workbook in WPS Office Spreadsheets, and use IF, VLOOKUP, and SUM formulas. WPS Spreadsheets recalculates the formulas when new rows are added within the covered range.
What formula converts a Yes answer into a point value in WPS Office?
Use =IF(B3="Yes",VLOOKUP(B2,A2:D2,COLUMN(D2)-COLUMN(A2)+1,FALSE),0). Replace B3 with the answer cell and adjust B2/A2:D2 to the scoring-row range; the formula returns the assigned point value only when the response is exactly “Yes”.
How do I make the scoring formulas expand when Microsoft Forms adds new responses?
Select the response data and click Insert > Table to convert it into an Excel Table. Add the scoring formula as a calculated column; WPS Office automatically fills the formula down for each new response row.
Does WPS Office support Excel formula compatibility for scoring Microsoft Forms exports?
Yes. WPS Office Spreadsheets supports Excel .xlsx workbooks and common functions such as IF, VLOOKUP, SUM, COLUMN, and structured table references, so your scoring formulas remain compatible with Excel.
What should I do if my Yes-or-No column contains different text like “yes” or “TRUE”?
Standardize the response text first. The formulas in this guide match the exact text “Yes”; you can change the formula or clean the exported data so all Yes values use the same spelling and capitalization.
