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.

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 Analyze Raw Data 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.

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: How to Analyze Text Data in Excel

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.

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.

Analyze Likert Scale Data.xlsx

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.

<< Go Back to Data Analysis with Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

13 Comments

P Mumu Aug 10, 2022 at 10:18 PM

This guide is AMAZING. As a masters student analysing data like this for the first time, this guide has been a saviour. Thank-you!!
- Reply
  Shanto Aug 11, 2022 at 11:28 AM
  
  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.
Franczesca Oct 1, 2022 at 10:09 PM

Thank you so much for this. The tutorial is very specific and organize. i was able to understand clearly, thank you so much.
Claire Apr 30, 2023 at 4:34 AM

This is a very handy step-through. However, how do you make the stacked bar charts of Likert scales (much easier to comprehend than the bar graph you have).
- Reply
  Alif Bin Hussain Apr 30, 2023 at 3:30 PM
  Hello Claire,
  
  Follow these steps to make the stacked bar charts of Likert scales.
  - First, select the range B4:G10.
  - Then go to,
  Insert >> Charts >> 100% Stacked Column
  - As a result, the chart will look like the following image.
  - Reply
    Kh Paydar May 10, 2023 at 2:58 AM
    
    Thank you.
Kh Paydar May 10, 2023 at 3:00 AM

Thanks for your time. I was so helpful.
- Reply
  Shamima Sultana May 10, 2023 at 9:24 AM
  
  Dear Kh Paydar,
  
  You are most welcome.
  
  Regards
  ExcelDemy
fhasim015 Jun 26, 2023 at 5:20 PM

I am a beginner; it is very helpful.
- Reply
  Masum Mahdy Jul 2, 2023 at 11:39 AM
  
  Thanks fhasim015! Glad to know that.
Navini Aug 14, 2023 at 11:25 PM

Very helpful. Organized in a clear way. Thank you so much
- Reply
  Shamima Sultana Aug 16, 2023 at 10:02 AM
  
  Dear Navini,
  
  You are most welcome.
  
  Regards
  ExcelDemy
Israel Weikama Sep 29, 2023 at 8:11 PM

Thanks for the work. It saved me, save going to SPSS and other long statistical softwares.