A Likert scale is used to measure the degree of agreeability with a subject. If you have ever participated in a survey or answered one of those online questionnaires— usually after a task or experience— asking you how much you enjoyed the experience or found an article you just read helpful, then yes, you have seen a Likert scale.
Why use the Likert Scale?
Likert scales are popular because they take a more elaborate approach to gathering feedback than a simple binary technique—Yes or No. The Likert scale tries to ascertain to what degree you hold opinions or perceptions regarding the matter of discussion.
For instance, after reading the FAQs section in a blog about building furniture. If you are asked at the end whether you found the tutorial helpful in simple Yes or No terms, you are going to pick one of the two which cannot help the site deduce to what degree you found it helpful or unhelpful. On the other hand, if you are presented with a 5-point Likert scale with a range—say, very helpful, helpful, neutral, needs improvement—then you would have more room to express your sentiments.
The website, in turn, will have more detailed responses from its audience aiding them to make meaningful improvements based on these insights.
In this guide, we will learn, using a step-by-step approach, how to create a Likert scale table in Google Sheets.
How to use Google Sheets to create a Likert Scale table
To create a Likert scale table we need a few things in place. The first of them is the survey questions and then a scale to rate the answers we obtain. The survey questions can be presented to the target audience using a tool like Google Forms.
The data obtained from this survey can then be taken into Google Sheets where we can analyze the data.
We will use a 5-point Likert scale i.e the degree of agreeability—from Strongly Disagree to Strongly Agree, with a neutral option in between.
Make a copy of the spreadsheet template with formulas
Below is a table of data from a survey by a health clinic that offers online services. Assume that it is trying to ascertain how its outgoing patients feel about the services it offers.
- Each row in the table represents one patient.
- Each column represents a question asked in the survey.
- The patients are given 5 options as potential answers for each question.
A 5-point Likert scale is used to rate 5 questions and a total of 10 patients participated in this survey. Some of these patients skipped a few questions so we had left the corresponding cells blank.
Next, we are going to use Google Sheets formulas to analyze the data in this table. But before that we need to add some rows beneath for the total responses and the 5-point scale for each response in our Likert table.
We have added some rows beneath for the total responses and the 5-point scale for each response in our Likert table.
Calculate the total responses in the Likert table
Next, let’s calculate the total response per question. This is very important, especially in large surveys, there is a need to know how many responses you got for each question and how many were left unanswered by the survey participants.
We will use the COUNTA formula in Google Sheets to count all the non-empty cells. Since each cell only holds a value if there is a response to the question we can effectively determine response rates.
Follow the steps below to sum the cells with responses:
- Enter =COUNTA into a cell
- For range, select cells D6:D15
- Press Enter
The formula correctly counted all the non-empty cells. We will do this for the remaining cells.
Tip: You can click on the dot in the bottom right corner of the cell and drag it across the empty cells. This will autofill them.
As you can see, the total number of responses has been correctly calculated. Now let us consider how many of these responses were Strongly Agree.
Determine the category of the responses in the Likert table
Knowing the total number of responses in our Likert table isn’t really useful until we find out what each response represents. So we are going to find out how many responses fall into each scale of our Likert table. To do this, we will use a special Google Sheets formula, the COUNTIF function.
The COUNTIF function is an aggregate of two functions—the COUNT and IF functions. And is used to perform conditional counting operations.
Syntax of the COUNTIF function
=COUNTIF(range, criterion)Where the parameters are:
- range – refers to the dataset to count
- criterion – refers to the condition to be met.
The logic we are going to use is simple. We want to COUNT cells IF they contain a particular text. We will supply two parameters- range to lookup and text to find in them.
Follow the steps below to determine the category of each response
- Enter the COUNTIF function into a cell
- For range, we will select D6:D15.
- For the text to look up i.e the criterion, let us enter C18 (the cell containing the response Strongly Agree).
- Press Enter.
Use autofill to obtain the values for the remaining cells in that column.
Now that we have obtained the value for the first column we can quickly generate others but first, we have to make the cells absolute reference. This can be done by using the dollar signs before the values we want to use absolute reference for. This ensures that they remain the same no matter the cell the formula is copied to.
The modified formula is as given below.
=COUNTIF(D$6:D$15,$C18)Now drag across the remaining empty cells to apply the formula to the remaining cells.
And that is how we use Google Sheets to create a Likert scale.
Visualize the Likert table data in Google Sheets
If we want a way to visualize this data – total responses categorized per scale then we can use a simple chart e.g pie chart
- Highlight the response data i.e C18:H22
- Click on Insert ➡ Charts
- Select the Pie Chart
Conclusion
A Likert scale is a helpful tool for carrying out surveys as it provides more insights than the regular yes or no responses. Using Google Sheets we can easily create Likert scale tables and perform analysis on the data.
Related Questions
What is the 5-point Likert scale?
The 5-point Likert scale measures agreeability using 5 cs such as Strongly Agree, Agree, Neutral, Disagree, and Strongly Disagree. The 5-point scale is popular because it does a good job of capturing the sentiments of the survey participants concerning the topic.
Can you use Google Forms for a survey?
Definitely. Google Forms is an excellent tool for all kinds of surveys, it is also quite easy to create a survey In Google Forms. To begin our Likert scale table we need to first conduct a survey which will provide us with the needed data.
See also:
You might also like other articles on our blog:
How To Create An Organizational Chart In Google Sheets
How to Use Pivot Tables in Google Sheets