Abrar-ur-Rahman Niloy
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.
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)
- 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.
- 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)
- After that press Enter on your keyboard and you will have the total number of blank values in the questionnaires for product 1.
- 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)
- After pressing Enter, you will have the total number of participants who participated in the survey.
- 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)
- After pressing Enter you will have the total number of people who are “Very Unsatisfied” with the first product.
- 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)
- After pressing Enter, you will have the total number of people who have responded to the first product’s question.
- 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
- How to Analyze Large Data Sets in Excel (6 Effective Methods)
- Analyze Data in Excel Using Pivot Tables (9 Suitable Examples)
- How to Analyze Time-Scaled Data in Excel (With Easy Steps)
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
- After pressing Enter you will have the ratio of the total people who were very unsatisfied with the product.
- 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.
- To validate the data, select cell C34 and write down the following formula.
=SUM(C29:C33)
- After pressing Enter you should get 100% as value.
- 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.
- 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.
- Now click OK and you will have it look something like this.
- 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.
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.
- Finally, after some modifications, the chart will look something like this.
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 this, visit Exceldemy.com.
This guide is AMAZING. As a masters student analysing data like this for the first time, this guide has been a saviour. Thank-you!!
Hello P MUMU,
We are glad to know that this article helped you to solve your problem. For more like this, make sure to check our other articles from ExcelDemy.
Thank you so much for this. The tutorial is very specific and organize. i was able to understand clearly, thank you so much.