Others

How to Calculate Employee Evaluation Scores from Microsoft Forms in Excel

Steve K Oct 21, 2026 868 views

Question details

The user needs to calculate a total evaluation score from a Microsoft Forms survey consisting of 30 rating-based questions.

How to Calculate Employee Evaluation Scores from Microsoft Forms

Product: Microsoft Forms / Excel
Device & OS: not provided
Scenario: Processing employee evaluation surveys that use a 1 to 3 rating scale where answers are subjective rather than strictly right or wrong.
Observed behavior: Standard Microsoft Forms do not automatically sum up non-quiz rating values, requiring a workaround to calculate the total evaluation score per employee.

Before you start

Ensure you have collected all necessary responses in Microsoft Forms and have a spreadsheet program installed on your device to process and calculate the exported data.

Solution 1Recommended

Export Responses to Excel and Calculate Totals

Convert your Microsoft Forms data into a spreadsheet to manually assign numerical values to text responses and calculate the total score for each employee.

Since traditional Forms do not calculate scores for non-quiz questions, exporting the raw response data to a spreadsheet is the most effective way to analyze the evaluation metrics.

Export Forms data

Open your Microsoft Form, navigate to the Responses tab, and click 'Open in Excel' to download the survey data.

Replace text with numerical values

Open the downloaded file. Press Ctrl+H to open the Find and Replace dialog. Replace text ratings (e.g., 'Exceeds Expectations') with their corresponding numerical values (e.g., '3'). Repeat this for all rating types.

Calculate the total score

In a new blank column at the end of the employee's row, type the SUM function (e.g., =SUM(B2:AE2)) to calculate the total score across all 30 questions.

Apply formula to all rows

Drag the fill handle from the first total score cell down to the bottom of your data to apply the SUM formula to all other employees in the sheet.

Export Responses to Excel and Calculate Totals

Data Processing Tip: Using the Find and Replace tool ensures complete accuracy and saves significant time when converting text-based survey responses into calculable numbers.

Solution 2

Assign Point Values in Quiz Mode

If you are setting up a new form from scratch, you can use the Quiz feature to assign points directly to specific answers.

Process Form Data in WPS Spreadsheet

Easily Calculate Evaluation Scores with WPS Office

WPS Spreadsheet offers powerful data calculation tools that make processing survey and evaluation data quick and easy. You can seamlessly open exported Microsoft Forms data, convert text to numbers, and use advanced formulas to analyze employee performance.

1. Open the exported file: Launch WPS Spreadsheet and open the Excel file you downloaded from Microsoft Forms.
2. Batch replace text: Press Ctrl+H to open the Replace dialog and quickly swap text responses like 'Meets Expectations' with numerical values.
3. Sum the scores: Click the AutoSum button on the Home tab or type =SUM() to instantly calculate the total evaluation score for each employee.

100% compatible with Microsoft Excel (.xlsx) files downloaded from FormsAdvanced Find & Replace tools for quick score conversionLightweight, fast, and completely free to useFamiliar spreadsheet interface requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can standard Microsoft Forms automatically sum ratings?

No, standard Microsoft Forms do not calculate scores natively. You must either use the Quiz format to assign points or export the data to a spreadsheet application to manually calculate the totals.

How do I assign different weights to specific evaluation questions?

Once you have exported the data to a spreadsheet, you can multiply specific column cells by a weighting factor in your final formula. For example, use =SUM(B2:D2) + (E2*2) to give the question in column E double the weight.

Is there a formula to convert text to numbers without using Find and Replace?

Yes, you can use the SWITCH or IFS function in a new set of adjacent columns to automatically translate text responses into numbers based on your specific criteria.

Why did 'Open in Excel' download a static file instead of a live spreadsheet?

If the form was created directly from the Forms web portal, it downloads a static copy of the data. To achieve live syncing where new responses automatically populate an Excel file, you must create the form from within OneDrive for Business or SharePoint Online.

Office Quick Solutions