Image by Editor

Microsoft Forms is one of the easiest tools to use for distributing surveys and collecting responses, but the raw export is rarely ready for statistical testing. Raw Microsoft Forms exports are often messy: long question-text column headers, mixed data types, incomplete responses, text answers instead of numbers, and hidden inconsistencies. The raw spreadsheet that opens in Excel is rarely ready for any statistical test.

In this tutorial, we will walk through the steps for cleaning Microsoft Forms survey data for statistical testing. This workflow will turn a raw .xlsx file into a clean, analysis-ready dataset you can immediately use for t-tests, ANOVA, chi-square tests, regression, or correlation.

Step 1: Export Raw Responses from Microsoft Forms

1.1 Performing the Export

• Open your form in Microsoft Forms
• Go to the View responses tab >> expand Open results in Excel >> select Open in Excel Desktop

• The file opens in Excel desktop as YourFormName.xlsx
• It is automatically saved in your OneDrive/SharePoint account

Pro Tip: Never overwrite the raw export file. Treat it as a read-only archive throughout the entire cleaning process. Immediately go to File → Save As, name the file, and create a copy. Always work on the copy.

1.2 Understanding the Raw Export Layout

The exported workbook contains a single worksheet. Each row represents one respondent, and each column represents one survey question. The first row is a header row generated automatically by Forms.

1.3 Making a Working Copy

• Press Ctrl+A to select all, then Ctrl+C to copy
• Create a new sheet named Working_Copy
• Paste with Ctrl+Shift+V or select Values Only to strip any live connections to the Forms service
• Freeze the top row:
  • Go to the View tab >> select Freeze Panes >> select Freeze Top Row
• Apply AutoFilter:
  • Go to the Data tab >> select Filter, so you can quickly inspect subsets
  • If you use an Excel table, AutoFilter is already enabled

Warning: If you paste with live references, the data may refresh and overwrite your cleaning work. Always paste as values.

Step 2: Remove Incomplete Responses (Systematic Listwise Deletion)

Run a Quick Inventory Formula

Use the following formulas to get an instant overview. Assume your data occupies columns A through Z and rows 2 to 501 (500 respondents):

Total Responses:     

=COUNTA(A2:A501)

Complete Responses:

=SUMPRODUCT((MMULT((A2:Z501<>"")*1,TRANSPOSE(ROW(A1:Z1)^0))=COLUMNS(A2:Z501))*1)

Missing Per Column:  

=COUNTBLANK(B2:B501)

% Missing:           

=COUNTBLANK(B2:B501)/COUNTA(A2:A501)*100

Pro Tip: Paste the % missing formula across all question columns in a summary row beneath your data. Any column with more than 10% missing values warrants investigation before analysis.

Incomplete responses can undermine statistical validity, especially for hypothesis testing.

Simple Filter (Recommended for <500 Responses)

• In the table, click the filter arrow on the Completion time column
• Uncheck (Blanks) >> click OK
• This keeps only fully submitted responses

Use a Helper Column

• In a new column next to the last column, use this header: Status
• Insert the following formula:

=IF([@Completion time]<>"","Complete","Incomplete")

• Filter the Status column >> keep only Complete
• (Optional) Delete the incomplete rows permanently: 
  • Select visible rows >> right-click >> select Delete >> select Entire Sheet Row

Pro Tip: Use Power Query for reproducibility.

Step 3: Shorten Column Headers (Make Them Analysis-Friendly)

Long question text makes formulas difficult to manage. It also makes it harder to see the full worksheet, and column widths can increase drastically.

Right-click each column header and rename it (or use Power Query later):

• AgeGroup
• Gender
• Satisfaction (Likert 1-5)
• Recommendation (0-10)
• Reason
• FeaturesUsed

Keep ID, Start time, Completion time, and Email as they are because they are standardized names for all forms.

Step 4: Handle Missing Values in Optional Questions

Missing data is rarely random, so a single blanket strategy rarely applies. This step helps you classify and treat missingness correctly according to the type of question and the mechanism behind the gap.

Highlight Missing Values: 

• Select the entire data range
• Go to the Home tab >> select Conditional Formatting >> select New Rule
• Select Format only cells that contain >> select Blanks
• Select Format >> choose a yellow fill color
• Click OK

Decide Your Strategy:

• Listwise deletion (safest for most hypothesis tests): Delete entire rows with missing values on key variables
• Pairwise deletion (for correlations): Excel handles this automatically in later steps
• Never impute without justification because mean or median imputation can bias results

Step 5: Recode Categorical Variables for Statistical Testing

Statistical tests in Excel often require numbers, not text.

Example 1: Likert Scale / Rating 

• Raw: “Very Dissatisfied”, “Dissatisfied”, “Neutral”, “Satisfied”, and “Very Satisfied” 
• Add a new column and use:

=SWITCH([@Satisfaction], "Very Dissatisfied", 1, "Dissatisfied", 2, "Neutral", 3, "Satisfied", 4, "Very Satisfied", 5, "")

Example 2: Yes/No or Binary 

=IF([@QuestionColumn]="Yes",1,IF([@QuestionColumn]="No",0,""))

Example 3: Gender → Numeric Codes

• Add a new column with the header GenderCode

=IFS([@Gender]="Male",1,[@Gender]="Female",2,[@Gender]="Non-binary",3,[@Gender]="Prefer not to say",4,TRUE,99)

Example 4: AgeGroup → Numeric Codes (Ordinal)

=MATCH([@AgeGroup],{"18-24","25-34","35-44","45-54","55+"},0)

• Result: 1 to 5, which is useful for correlation or regression when treating the age groups as ordinal categories

Example 5: Multiple-Select (“Choose All That Apply”) 

Forms puts selected options in one cell separated by commas (e.g.,  “Mobile app, Website, Chat”).

• Split the responses into dummy variables (0/1) using Text to Columns
• Go to the Data tab >> select Text to Columns >> select Delimited >> select Comma
• Click Finish

• Or, in Power Query, select Split Column by Delimiter

Better Way (Keep Original):

• Add new columns:
• MobileApp: =IF(ISNUMBER(SEARCH(“Mobile app”,[@FeaturesUsed])),1,0)
• Website: =IF(ISNUMBER(SEARCH(“Website”,[@FeaturesUsed])),1,0)
• Chat: =IF(ISNUMBER(SEARCH(“Chat”,[@FeaturesUsed])),1,0)
• EmailSupport: =IF(ISNUMBER(SEARCH(“Email support”,[@FeaturesUsed])),1,0)

Clean Open-Text Responses (Reason):

• Clean your text data

=TRIM(PROPER([@Reason]))

• This removes extra spaces and applies consistent capitalization

• Manually fix obvious typos, if any (e.g., change “Awsome” to “Awesome”)
  • Find & Replace: press Ctrl + H
• Use Remove Duplicates on key identifier columns

Rename all columns to short, analysis-friendly names (e.g., Q1_Likert, Age, Gender_Recoded).

Step 6: Detect Outliers and Inconsistent Entries

Numeric variables (Age, Salary, Rating):

• Go to the Data tab >> select Data Analysis >> select Descriptive Statistics  >> click OK
• Select Input Range
• Select Labels in first row
• Select Output options
• Click OK

Summary Statistics:

Or use Conditional Formatting: 

• Select the NPS column
• Go to the Home tab >> select Conditional Formatting >> select Highlight Cells Rules >> select Greater Than >> enter 10 (red)
• Do the same for values Less Than 0
• For Satisfaction, highlight values <1 or >5
• For Age, you can add a check

=IF(OR([@AgeCode]<1,[@AgeCode]>5),"Check","OK")

Dates/Times: Ensure the Start time and Completion time columns are real dates by formatting them as Date. Use DATEDIF if you need the response duration.

Univariate Outlier Detection for Numeric Variables

Z-Score Method:
Values with an absolute Z-score above 3.29 are outliers at the 0.1% significance level (Tabachnick & Fidell, 2013):

Z-score:  

=(I2-AVERAGE(Cleaned_Data[Recommend]))/STDEV.S(Cleaned_Data[Recommend])

Flag:     

=IF(ABS(U2) > 3.29, "OUTLIER", "OK")

IQR Fence Method (Tukey)

The IQR method is more robust to non-normal distributions:

Q1:

=QUARTILE.INC(Cleaned_Data[Recommend], 1)

Q3:      

=QUARTILE.INC(Cleaned_Data[Recommend], 3)

IQR:     

=Q3 - Q1

Lower:

=Q1 - 1.5 * IQR

Upper:

=Q3 + 1.5 * IQR

Flag:    

=IF(OR(I2 < Lower, I2 > Upper), "OUTLIER", "OK")

In this sample, everything is clean, but in real data, you would investigate or cap extreme values and document the decision.

Step 7: Final Preparation for Hypothesis Testing & Modeling

• Create a final “Analysis Ready” sheet, or use the Power Query output
• Hide or delete original long columns you no longer need
• Delete helper columns you do not want, but keep codes and clean versions
• Reorder columns logically: demographics first, then key variables, then open text
• Check data types: 
  • Go to the Home tab >> verify Number / Text / Date
• Run Data → Data Analysis:
  • Descriptive Statistics
  • Histogram (for normality checks)
  • t-Test, ANOVA, Correlation, Regression
• Save as:
  • YourFormName_CLEAN.xlsx (for Excel work)
  • YourFormName_CLEAN.csv (for R/Python/SPSS)

Advanced: Make It Reproducible with Power Query (Best Practice)

If you will receive more responses later:

• Go to the Data tab >> select Get Data >> select From File >> select From Workbook >> select your file

• In Power Query Editor:
  • Filter Completion time to remove blanks
  • Rename columns
  • Add custom columns for recoding, such as AgeCode and GenderCode
  • Split FeaturesUsed by delimiter (comma)
  • Replace values, trim text, and apply other cleaning steps as needed
• Close & Load to a new sheet called “Clean Data”
• Every time you get new responses: 
  • Right-click the query >> select Refresh
  • Everything updates automatically

Quick Checklist Before Running Any Statistical Test

• Only complete responses
• No blank cells in key variables, or missing values have been handled
• All categorical variables recoded to numbers
• Outliers reviewed and documented
• Column names short and consistent
• Data in Excel table format

Ready-to-Use Statistical Tests in Excel

You now have a dataset ready for chi-square tests of independence, independent t-tests, one-way ANOVA, linear regression, or non-parametric alternatives, all directly in Excel. Now you can go to Data >> Data Analysis (enable the ToolPak if it is not visible):

• Descriptive Statistics on Satisfaction and Recommendation
• t-Test: Two-Sample Assuming Equal Variances to compare males vs. females
• ANOVA: Single Factor to compare satisfaction by AgeGroup
• Correlation between Satisfaction and Recommendation
• Regression to predict NPS from Satisfaction, AgeCode, and GenderCode

Conclusion

By following the steps above, you can clean Microsoft Forms survey data for statistical testing. Cleaning is not just administrative work. It directly affects the validity of your hypothesis tests and models. A carefully cleaned dataset gives you more trustworthy results, fewer analysis errors, and clearer reporting. Following this workflow helps you spend minutes instead of hours cleaning your data every time you run a new survey.