How to Analyze Likert Scale Data in Excel (with Quick Steps)

The Likert Scale has become quite popular as a survey scale in oppose to the binary scales. This creates flexibility for responders and a better understanding of the data collected on a group of people. In this tutorial, we are going to focus on how to analyze the Likert Scale data collected from surveys in Excel.


Download Practice Workbook

You can download the workbook containing the dataset and reports used for the demonstration from the link below. Download and try yourself while you go through the article.


What Is Likert Scale?

The Likert Scale is named after its creator Rensis Likert. Sometimes also referred to as a satisfaction scale, this scale generally consists of multiple choices for questions. The choices generally range from 5 to 7 points. These options range from one extreme point of a possible answer to another. In other words, it is a wide range of possible answers instead of just binary black and white answers.

Likert Scale options can be of many forms too. For example, a performance Likert Scale can be excellent, good, okay, bad, or horrible. Agreeability of a statement can be strongly agreeable, agreeable, somewhat agreeable, neither agreeable nor disagreeable, somewhat disagreeable, disagreeable, or strongly disagreeable. While analyzing survey data, all of these scales can be used for a more clear picture instead of binary options like good or bad performance, agreeable or disagreeable, etc. As a result, it lets us uncover more degrees of opinion or options. Also, it helps pinpoint particular areas that need improvement.


Step-by-Step Procedure to Analyze Likert Scale Data in Excel

Now we will focus on how to analyze Likert Scale data in Excel. For any surveys, we are going to need to fill up a form first. Then categorize our data into a form of a dataset. After that, we will move on to different parts of the analysis. For this demonstration, we are going to make a Likert Scale data chart for customer surveys on how satisfied they are with certain products and analyze them in Excel.


Step 1: Create Survey Form and Make Dataset

First, we need to collect data from participants or customers. Of course, you can collect data manually by going over every customer. But many online survey tools make the job way easier. For example, we have created the following survey with the help of Google Forms.

Now gather all the data and organize them. Accordingly, fill the responses in Excel to make a workable dataset. A sample dataset of 12 people participating in the survey will look like this.

how to analyze likert scale data in excel

At this instant, we are good to go to analyze the Likert Scale data in Excel.


Step 2: Count Blank and Non-Blanck Responses of Likert Scale Data

The first thing to do while analyzing a Likert Scale data in Excel is to find the blank and non-blank data in the dataset. It is often common for people to skip questions in surveys. While analyzing the whole group, these blank values may change the outcome for certain parameters. For that, we should prioritize counting the blank values in the dataset for particular parameters, (or in this case, questions).

We are going to need the COUNTA and COUTBLANK function to do that. And with the help of the SUM function, we are going to calculate the total number of people participating. Follow these steps to count blank and non-blank values in the Likert Scale Dataset.

  • First, select cell C18 and write down the following formula.

=COUNTA(C5:C16)

how to analyze likert scale data in excel

  • After that, press Enter on your keyboard. As a result, you will have the total number of people that have answered the question for product 1.

how to analyze likert scale data in excel

  • Then select the cell again. Now click and drag the fill handle icon to the right of the row to fill up the formula for the rest of the cells.

  • Now select cell C19 and write down this formula.

=COUNTBLANK(C5:C16)

how to analyze likert scale data in excel

  • After that press Enter on your keyboard and you will have the total number of blank values in the questionnaires for product 1.

how to analyze likert scale data in excel

  • Then select the cell again and click and drag the fill handle icon to the end of the row to fill out the rest of the cells with this formula.

  • Next, select cell C20 and write down the following formula in the cell.

=SUM(C18:C19)

how to analyze likert scale data in excel

  • After pressing Enter, you will have the total number of participants who participated in the survey.

how to analyze likert scale data in excel

  • Now select the cell again. Then click and drag the fill handle icon to the end of the row to replicate the formula for each cell.

Read More: How to Analyse Qualitative Data from a Questionnaire in Excel


Step 3: Count All Feedback from Dataset

At this instant, we are going to count all the individual feedback from the survey. That is how many people were satisfied, or unsatisfied, or that fell into other categories for each of the products. Similar to the previous step, we are gonna need the SUM function for this. We also need the help of the COUNTIF function. Follow the steps to count all feedback from the Likert scale data.

  • Firstly, let’s freeze the dataset that helps view the dataset and all of the charts below. For that, select the row after where the dataset ended. You can do that by selecting the row header from the left of the spreadsheet.

  • Then go to the View tab on your ribbon and select Freeze Panes from the Windows group.
  • After that, select Freeze Panes from the drop-down menu.

  • Now scroll down to the bottom of the sheet, select cell C22, and write down the following formula.

=COUNTIF(C$5:C$16,$B22)

how to analyze likert scale data in excel

  • After pressing Enter you will have the total number of people who are “Very Unsatisfied” with the first product.

how to analyze likert scale data in excel

  • Now select the cell again and click and drag the fill handle icon to the end of the column to fill out the rest of the cells with this formula.

  • While the range is selected, click and drag the fill handle icon to the left of the chart to fill out the rest of the cells with the formula for their respective cells.

  • To count the total of the people who have responded to each product, select cell C27 and write down the following formula.

=SUM(C22:C26)

how to analyze likert scale data in excel

  • After pressing Enter, you will have the total number of people who have responded to the first product’s question.

how to analyze likert scale data in excel

  • Now select the cell again. Then click and drag the fill handle icon to the end of the row to replicate the formula for the rest of the cells.

Read More: How to Analyze Text Data in Excel (5 Suitable Ways)


Similar Readings


Step 4: Calculate Percentage of Each Feedback

Now, let’s calculate how many of the people were satisfied/unsatisfied and how satisfied/unsatisfied they were with a particular product. Similar to the previous steps, we are gonna need the SUM function for this. Follow these steps.

  • First, select cell C29 and write down the following formula.

=C22/C$27

how to analyze likert scale data in excel

  • After pressing Enter you will have the ratio of the total people who were very unsatisfied with the product.

how to analyze likert scale data in excel

  • Then select the cell again and click and drag the fill handle icon to the end of the column to fill the rest of the cells with this formula.

  • While the range is selected, click and drag the fill handle icon to the right of the chart to replicate the formula for the rest of the cells.

  • Now select the range C29:H33 and go to the Home tab on your ribbon. Then select % from the Number group.

You will have all of the ratios in a percentage format.

how to analyze likert scale data in excel

  • To validate the data, select cell C34 and write down the following formula.

=SUM(C29:C33)

how to analyze likert scale data in excel

  • After pressing Enter you should get 100% as value.

how to analyze likert scale data in excel

  • Now select the cell again and click and drag the fill handle icon to the end of the row to fill out the rest of the cells with the formula.


Step 5: Make Report on Likert Scale Analysis

In this step, we are going to make a report of the Likert Scale data analysis in Excel. We are going to represent the freshly created data in a new spreadsheet in a report-like manner. This will make the analysis and summarization a whole lot easier for an outsider.

  • To do that, first, select the range B4:H4 and copy it to the clipboard.
  • Now go to the new spreadsheet and right-click on the cell you want to start the report (we have selected cell B4 here) and click on Paste Special from the context menu.

  • Then in the Paste Special box, check on Transpose.

  • After clicking on OK, you will have the range pasted vertically.

  • Now rename the value in cell B4 that looks more appropriate with the report.

  • In a similar manner, go back to the Likert scale sheet, select the range B29:H33, and copy it.

how to analyze likert scale data in excel

  • Then move to the report sheet, select cell B5, and right-click on it.
  • Next, select Paste Special from the context menu.

  • After that, check the Values and Transpose options in the Paste Special box.

how to analyze likert scale data in excel

  • Now click OK and you will have it look something like this.

how to analyze likert scale data in excel

  • Now format the cells to make it a % value by going to the Home tab and selecting % from the Number group.

  • Finally, you will have a report that looks like this.

how to analyze likert scale data in excel

Read More: [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)


Step 6: Generate Final Report with Charts

For making the report more presentable, let’s add a chart to it. Follow these steps to make a chart out of the newly created report in the previous step.

  • First, select the range B4:G10.
  • Then go to the Insert tab on your ribbon and select Recommended Charts from the Charts group.

  • After that, in the Insert Chart box, select the All Charts tab and select the type of chart you want from the left side of the box and then the specific graph from the right of the box. Then click on OK.

  • As a result, a graph will pop up on the spreadsheet.

how to analyze likert scale data in excel

  • Finally, after some modifications, the chart will look something like this.

how to analyze likert scale data in excel


Conclusion

So these were the steps we can take to analyze the Likert Scale data in Excel. Hopefully, you will be able to analyze your Likert Scale data from the knowledge you have gathered. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more step-by-step and other detailed guides like

Large Data Sets

this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo