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.

**Read More: **How to Analyze Large Data Sets in Excel

### 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.

**Read More: **How to Analyze Time Series Data 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.

## 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.

## Related Articles

- How to Analyze Sales Data in Excel
- How to Analyze qPCR Data in Excel
- How to Analyze Time-Scaled Data in Excel
- How to Analyze Quantitative Data in Excel
- How to Analyze Qualitative Data in Excel
- How to Analyse Qualitative Data from a Questionnaire in Excel
- How to Convert Qualitative Data to Quantitative Data in Excel

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

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.

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).

Hello Claire,

Follow these steps to make the

stacked bar chartsof Likert scales.B4:G10.Insert >> Charts >> 100% Stacked ColumnThank you.

Thanks for your time. I was so helpful.

Dear

Kh Paydar,You are most welcome.

Regards

ExcelDemyI am a beginner; it is very helpful.

Thanks fhasim015! Glad to know that.

Very helpful. Organized in a clear way. Thank you so much

Dear

Navini,You are most welcome.

Regards

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