Image by Editor

When anyone needs to collect feedback, measure opinions, or conduct research, Likert Scale surveys are a popular method for quantifying attitudes and perceptions. They are commonly used to measure attitudes, opinions, or behaviors on a spectrum, typically ranging from “Strongly Disagree” to “Strongly Agree.” These surveys generate ordinal data, which can be quantitatively analyzed to uncover patterns in responses.

In this tutorial, we will show the steps to create Likert Scale surveys in Microsoft Forms, followed by analyzing the results in Microsoft Excel.

What is the Likert Scale?

A true Likert scale is a composite measure: a set of related items whose scores are summed or averaged to produce a single construct score. A Likert scale typically uses a 5- or 7-point scale in which respondents rate their level of agreement with a statement. The scale usually ranges from Strongly Agree/Satisfied to Strongly Disagree, with intermediate levels such as Agree, Neutral, and Disagree. For instance:

• Very Satisfied
• Satisfied
• Neutral
• Dissatisfied
• Very Dissatisfied

Step 1: Designing a Balanced Likert Scale Survey in Microsoft Forms

1.1 Balanced Question Design

To ensure statistical rigor, your Likert scale questions should be well-balanced and avoid any form of bias or ambiguity. Follow these guidelines:

• Clear Wording: Ensure that each question is straightforward and easy to understand. Avoid complex phrasing or jargon that could confuse the respondents.
• Use a Single Idea Per Item: Avoid double-barreled questions. “I find the product features efficient and enjoyable” measures two constructs simultaneously. Split it: “I find the features efficient” and “I find the features enjoyable” become two separate items.
• Balanced Rating Options: Use an odd number of response options (e.g., 5 or 7 points). An odd scale allows for a neutral option, which is important for respondents who may not have a strong opinion. For example: “How satisfied are you with the product?”

Choosing Scale Width: 5-Point vs. 7-Point

For most organizational surveys, a 5-point scale is the practical standard. Choose one width and apply it consistently across all items in a construct.

1.2 Avoid Leading Questions

Leading questions that prompt a specific answer should be avoided. For instance, avoid asking, “Don’t you think our product is the best?” Instead, ask, “How would you rate our product?”

1.3 Minimize Ambiguity

Ensure the questions are specific and not open to multiple interpretations. Questions like, “How satisfied are you?” without specifying what the satisfaction refers to (e.g., service, product quality) can lead to ambiguous responses.

Step 2: Guide to Creating the Survey

• Go to Microsoft Forms >> click on New Form to start creating your survey
• Set Up the Form Title and Description
• Add Likert Scale Questions:
  • For each Likert question, click Add new, then choose the Choice question type
  • Enter the question like “How satisfied are you with the product?”
  • Under the choices section, enter the Likert scale options (e.g., Very Satisfied, Satisfied, Neutral, Dissatisfied, Very Dissatisfied)
• Make Questions Required: Toggle the Required button for each question to ensure that all respondents answer the question.
• Add More Questions: Continue adding Likert scale questions for each aspect of the product you want feedback on. You can also include open-ended questions to collect qualitative feedback.

Step 3: Collecting and Exporting Data from Microsoft Forms

Once your survey is complete, export the data to Excel:

• Go to the Collect Response tab >> share the form link

• Once your survey is complete, export the data to Excel
• Go to the Response tab >> click on Open results in Excel >> select Open in Excel Desktop to download the responses

Preparing the Data for Analysis:

In Excel, you’ll need to encode the ordinal responses numerically. For example:

• Very Satisfied = 5
• Satisfied = 4
• Neutral = 3
• Dissatisfied = 2
• Very Dissatisfied = 1

Ensure each Likert scale question has its own column, and the responses are listed in rows. This setup will allow you to perform statistical analysis.

You can use formulas to convert the textual responses into numerical data:

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

Step 4: Calculating Summary Statistics

Once the data is encoded, you can calculate summary statistics.

Mean vs. Median: When analyzing Likert scale data, it’s essential to understand when to use the mean versus the median:

• Mean: If the data is symmetrically distributed (i.e., there are no extreme outliers), use the mean to summarize the data.
• Median: If the data is skewed, the median is a more appropriate measure of central tendency, as it is not affected by extreme values.

Likert items are ordinal. The differences between scale points are not proven to be equal. Therefore, the arithmetic mean is not a mathematically valid statistic for a single item. The median which requires only rank ordering, is the appropriate measure of central tendency.

It is better to report both. Calculate the median as your primary statistic for single items. Report the mean as supplementary. For composite scores (summed or averaged across multiple items), means become more defensible.

Summary Statistics:

• Median for single items:

=MEDIAN(FormsResponse[Satisfaction Score])

• Calculate mean:

=AVERAGE(FormsResponse[Satisfaction Score])

• Valid response count:

=COUNTIF(FormsResponse[Satisfaction Score],"<>"&"")

• To assess the spread or variability of responses, you can calculate the standard deviation using:

=STDEV(FormsResponse[Satisfaction Score])

Step 5: Analyzing Distributions and Response Patterns

Frequency Distribution

To understand how responses are distributed across the Likert scale, create a frequency table. This will show the count of respondents in each category (e.g., how many chose “Very Satisfied”, “Satisfied “, etc.).

In Excel, you can create a frequency table using the COUNTIF function:

• For example, to count how many times “Very Satisfied” (encoded as 5) appears as:

=COUNTIF(FormsResponse[Satisfaction Score], 5)

=COUNTIF(FormsResponse[Satisfaction Score], 4)

=COUNTIF(FormsResponse[Satisfaction Score], 3)

=COUNTIF(FormsResponse[Satisfaction Score], 2)

=COUNTIF(FormsResponse[Satisfaction Score], 1)

Visualizing the Results

Visualizations help in understanding the response patterns:

• Bar Chart: A bar chart is ideal for displaying the frequency of each Likert scale response.
  • Select the data (for example, the number of responses for each Likert scale option)
  • Go to the Insert tab >> select Column Chart
• Pie Chart: You can use a pie chart for a high-level view of the overall distribution of responses.
  • Select the data
  • Go to the Insert tab >> select Pie Chart

Step 6: Reporting the Results

Once you’ve analyzed the data and created visualizations, it’s time to report the findings. When reporting Likert scale survey results, focus on:

• Key Findings: Summarize the main trends, such as the most common responses and any notable patterns.
  • “The average response for question 1 was 4.2 (Satisfied), with a standard deviation of 0.8, indicating a relatively consistent opinion towards customer satisfaction.”
• Visuals: Include charts and graphs that clearly illustrate your findings. This is especially useful for stakeholders who are not familiar with statistical analysis.
• Implications: Discuss what the results mean in the context of the product, service, or research question.

Advanced Tips for Rigor

• Sample Size: Aim for n ≥ 100–200+ for stable estimates; power analysis for subgroups
• Handling Missing Data: Listwise or pairwise deletion; document decisions
• Non-Parametric Tests: Use the Mann-Whitney U or Kruskal-Wallis test instead of t-tests/ANOVA when comparing groups (Data Analysis ToolPak or Real Statistics add-in)
• Factor Analysis / PCA: For scale validation (requires add-ins or R/Python export)
• Anonymity & Ethics: Clearly state in Forms; comply with data protection (GDPR, etc.)

Conclusion

By following the above steps, you can create Likert Scale surveys in Microsoft Forms for analysis in Excel. Creating and analyzing Likert scale surveys in Microsoft Forms is an effective way to gather valuable feedback from respondents. By carefully designing your questions, encoding the responses properly in Excel, and applying appropriate statistical methods, you can gain actionable insights into your product or service. Visualizing the results will help make the data more digestible for decision-makers and stakeholders. Now, you can design and analyze Likert scale surveys in a statistically rigorous way, ensuring your results are reliable and actionable.