Evaluators love a good survey. And why shouldn’t we? They are a cost-effective, quick method for capturing good data! However, not all surveys are awesome – we’ve all come across poorly crafted surveys. Maybe it’s full of double-barreled questions. Maybe the questions are loaded, or leading. Or maybe the response options don’t match how you would choose to respond.  

The Likert scale (check this out for a debate on how to pronounce it! Personally, I’m on the LIKE-ert side of this one) is one of the more commonly used rating scales in surveys. As evaluators, we should know a thing or two about it, and how to navigate some of the decisions involved in using a Likert scale.

---

What are Likert Scales? 

Likert scales were named after Rensis Likert, a social scientist, who developed the scale as a way to assess a person’s attitudes or feelings. There are many factors that can be assessed using a Likert scale, including (but not limited to): 

• Level of agreement, satisfaction, concern, acceptability, support, importance, difficulty, and awareness 

• Frequency 

• Valence/Quality 

• Likelihood 

The Likert scale is an important move away from binary-only responses (i.e., yes/no) and helps the evaluator assess a respondent’s feelings or thoughts on a range or spectrum, allowing for a better, more nuanced understanding. Statistically, this offers more variance or discrimination in your data. 

Notably, as opposed to rating scales the Likert rating scale uses labels – actual words – for each rating.

Likert scales have some great advantages, including the options to use icons or faces for children or others who may have difficulty reading:

They are also great for formatting into a matrix when you are designing your survey, making it take up less space and easier for the respondent to fill in.

---

Common Questions about Using the Likert Scale

Like other surveys, a Likert scale survey is still susceptible to the pitfalls of poor survey design: e.g., you still can’t have a double-barreled question. In fact, there are a few additional concerns, or at least conversations, that swirl around the Likert scale: 

1. Do you have to use an odd number of ratings (with a middle point)? 

2. How many anchor points should I use: 3, 4, 5, 6, 7? 

3. How do I report a Likert scale survey? 

   • How do I analyze the data? 

   • Can you combine ratings? 

I’ll tell you right now, I don’t have clear answers for any of these 3 questions. But I can help to lay out the arguments for you so that you can make an informed decision. 

Does a Likert scale have to have an odd number of choices?

Most Likert scales you come across will have a middle point that offers a neutral selection choice:

Do you have to include this? No. If you don’t, you’re created what’s called a “forced choice” Likert scale. That is, you’re forcing the respondent to choose a side without the option to be neutral. Many resources advocate for the middle point but if you’re not sure, some questions to ask yourself in consideration include: 

• Are you working with sensitive subject matter where respondents may be reluctant or feel uncomfortable in a forced choice? (if yes, include the middle point) 

• Is it possible that neutrality is a valid option? (if yes, include the middle point) 

• Is there potential that respondents will be reluctant to answer negatively and bias towards a mid-point? (if yes, exclude the middle point) 

Sometimes that middle point gives respondents a way to answer quickly without thinking more deeply about their selection, leading to the potential that the collected data are not accurate. Interpretation of the middle point can also be problematic: does it reflect true neutrality or just indifference? Did the respondent not understand the question? Forced choice can offer more declarative data and reporting, but it can also turn off respondents who may genuinely be in that middle ground. Statistically, removing the middle point does not affect the validity or reliability of your data. 

Ultimately (and perhaps unfortunately?) it’s up to you. There is no right or wrong choice. 

Keep in mind that “not applicable” or “don’t know” are still valid considerations whether or not you have a neutral point. Neutrality and “not applicable” are not the same thing. Consensus is that including a “don’t know” also does not affect the reliability and validity of your data. 

Another thing to watch out for is an unbalanced scale. You can remove the middle point, but you can’t offer more choices on one side than the other. In fact, you can’t do this whether the middle point is there or not.

How many anchor points should I use? (and a side conversation about polarity)

Again, there is no right or wrong answer here, but certainly the most common number is 5. In general, the more anchor points you have, the more sensitive your data are and the more variance you have (which is a good thing!). Some research has shown that reliability and validity are highest with a 7-point scale when it is bipolar, but unipolar scales are optimized at 5-points.

A side conversation about polarity 

Unipolar scales measure the amount of one factor, whereas bipolar scales offer two opposing views: 

Unipolar scales can be used wherever there is the possibility of expressing all or none of dimension. Unipolar scales have less need for that middle point discussed above, whereas bipolar scales have a more natural midpoint. Bipolar scales create more cognitive load on the respondent – having to decide which end of the spectrum they align with, and then where on that spectrum they fall. But bipolar scales can be problematic when there is the potential for interpretation about “opposite”. For example, is “dark” the opposite of “light” or perhaps “bright”? Psychometric testing suggests that where possible, a unipolar scale is the better option for improved scale reliability.

Back to the question of how many anchor points: consensus is that anything above 7 doesn’t provide additional variance in your data. So then, what about 3 points? Arguments against using only 3 anchor points is that it provides less discrimination – which is kind of the whole point of using a Likert scale. However, if you are surveying a topic with little expected variance and are hoping for a quick survey option, 3-points can be a valid option. 

Another factor to consider is about the method of administration: doing a phone survey and keeping a 7-point scale in your head is likely to be confusing! 

How do I report a Likert scale survey?

How to report a Likert scale is as equally an important conversation as creating the scale itself. There is lots of debate swirling around the most appropriate statistical methods to use. This debate centres on the question: can you assume equal distance between the anchor points? That is, are the data ordinal or interval? 

Generally, psychometrists seem to agree that a Likert Scale is ordinal (rank) and approximates an interval data set. To analyze Likert scales, many suggest median (or mode) and range (as opposed to mean and standard deviation). Personally, I’m a big fan of reporting the median in a Likert scale for a few reasons: 1) you don’t have to try to interpret what 3.4 means on a 5-point scale – the median will be a whole number that is found on your scale and 2) it isn’t skewed by outliers. Graphically, Likerts can be depicted in bar charts, or any number of great data viz options. 

If you’re looking to do some statistical analysis on a Likert scale survey, the rule of thumb is to use non-parametric tests, which mean Spearman’s r for correlations, and Wilcoxon Signed-Rank (in place of the paired t-test) or Mann Whitney (in place of the independent samples t-test). There is debate, however, about whether a Likert approximates interval data well enough to use parametric tests, especially if you are looking at overall questionnaire data (as opposed to a single Likert scale question). Some reports have shown little difference in parametric and non-parametric analyses, so you may be justified in selecting either, particularly if your data follow a normal distribution and you have an adequate sample size. If your data are skewed (as many Likert scales are), best to stick with non-parametric. 

Now that we can analyze the data, how do we report it? A personal pet peeve of mine is reports that have data from a 5-point scale and then report it as a 3-point scale, combining the two ends of the scale: 

The purpose of a Likert scale is to add discrimination to your data. Combining these scales removes that discrimination.  I’m sure you’ve all seen reports that read “x% of respondents either agreed or strongly agreed with the statement.” So, is this ok or not? 

Again, the answer is it depends. If you are performing statistical analysis on your tool, absolutely do not combine any of the anchor points – this greatly reduces the value of using the Likert scale to begin with! If, however, you are reporting to a lay audience and are aiming for clear, simple reporting, combining items on a scale can help your audience with interpretation and perhaps make the data more actionable. 

Some final tips when drafting or reviewing your scale: 

• Primacy and recency effects apply to Likert scales, like any other scale. Ideally, you would have two versions of your survey – one where the positive side is on the left and one where it is on the right – and you would distribute these versions randomly to your sample. Some survey platforms can actually do this for you! You’ll need to pay close attention to this when analyzing your data to avoid any mistakes!

• As in any survey, there are several respondent biases that come into play, including confirmatory bias and social desirability bias. Good survey design, including allowing for anonymity and clear instruction, can mitigate some of this risk.  To avoid confirmatory bias (this is when respondents have a bias toward accepting the statement or agreeing with the question) add variance into your survey (i.e., more anchor points!). You can also try including some question reversals, where the negative statement is used: e.g., “I don’t like the workshop.” This can also help you identify those who are just answering down in a straight line, selecting “agree” down the whole column to get through the survey quickly. Like above, reversals can be tricky in analysis. Communication about reversal statements is required, and likely it’s best to do a double check to ensure the data were analyzed properly. 

• Pilot test! Though it takes a bit of time and resources, pilot testing any survey (Likert or not) is helpful. You can ask your respondents about any confusing language, measure the amount of time it takes to complete the survey, and assess if the response options reflect the respondents’ desired response. 

---

I’m confident that as an evaluator, Likert scales are part of your toolkit. Hopefully, we’ve shared some relevant tips about how to use and report them effectively.

Let us know some other tools in your toolkit that you have questions about! Are there some templates that would be helpful? Comment on this article or connect with us on LinkedIn or Twitter!

---

Sign up for our newsletter

We’ll let you know about our new content, and curate the best new evaluation resources from around the web!

Email Address


Sign Up

We respect your privacy.

Thank you!

 

 

If you work with data in Excel, whether frequently or infrequently, learning the basics of PivotTables will improve your ability to quickly explore and analyze raw data. PivotTables can transform your raw data into meaningful insights and reports in minutes. And the best part: creating PivotTables does not require any prior knowledge of Excel’s built-in formulas. With a few clicks of the mouse, you can generate fast, accurate results. 

With PivotTables you will be able to quickly: 

• Summarize data (e.g., averages, counts, sums) 

• Sort data (e.g., alphabetically, numerically) 

• Group data (e.g., dates by month) 

• Filter data (e.g., by department, by region) 

PivotTables let you go from rows and columns of raw data to results in just a few clicks (see the table below). Creating meaningful data summaries is a breeze with PivotTables, and once you understand the basics, you will not have to go back to using manual formulas for most of your reporting needs. Just let PivotTables do all the work for you. 

This article will walk you through the basics of working with PivotTables, using sample data from patient surveys conducted in medical clinics.

---

Preparing your data

Before jumping into working with PivotTables, we need to take care of some basic housekeeping. While there are several ways of entering data into an Excel spreadsheet, data should be entered in a tabular format such that: 

• The first row contains a clear header describing the data in the columns 

• Each column should only contain data of a single type (e.g., a Date column should only contain dates) 

• Each row should only contain data from a single time point (e.g., a program participant may answer a survey on two different dates, but each survey should be entered into its own row) 

The data are now ready to be used in a PivotTable. It is possible to create a PivotTable using the as formatted above. However, if you were to enter new data to the spreadsheet, the PivotTable would not automatically update with the new data. Luckily, there is an easy fix for this: convert the data range into an Excel Table (Insert > Table OR Ctrl + T). An Excel Table organizes the data and makes it easy to sort, filter, and format.

The appearance of your Tables can be edited within the Table Design tab. Within this tab you can change appearance of your Table with preset Table Styles, or even opt to remove all formatting completely. You may also want to give your Table a name (located on the left of the Table Design tab). Naming the spreadsheet becomes important when you are working with many data Tables and PivotTables, as these names will allow for easy reference.

---

Inserting a PivotTable

With data organized into a Table, it is time to insert a PivotTable. There are a couple of options: 

1. Insert > PivotTable 

• Click anywhere within your Table 

• Navigate to the Insert Tab (top left of Excel spreadsheet) 

• Select ‘PivotTable’ (or ‘Recommended PivotTables’)* 

2. Table Design > Summarize with PivotTable 

• Click anywhere within your Table 

• Navigate to the Table Design tab (top right of Excel spreadsheet) 

• Select ‘Summarize with PivotTable’ 

 *‘Recommended PivotTables’ will provide recommendations for summarizing your data in a PivotTable. This is a good option if you are unsure of how to summarize your data.  

 

Both options will bring up the ‘PivotTable from table or range’ pop-up box. Within this box, you can select your Table or Range, if you have not done so already; the name of your Table would show here if you converted your data into a Table prior to inserting a PivotTable. You can also select where the PivotTable will be placed: (1) to a New Worksheet or (2) to an Existing Worksheet.  

Note: ‘Add this data to the Data Model’ will allow you to perform more complex tasks on your PivotTable, including the ability to create your own formulae within a PivotTable or the ability to link two or more PivotTables together based on a common attribute. However, these are more advanced skills and are not covered in this article.

---

Getting familiar with PivotTables

Unless you used the ‘Recommended PivotTables’, your PivotTable will not look like much. You will be presented with an empty PivotTable located within the spreadsheet and a PivotTable Fields menu on the right-hand side of your workbook. We will start with the PivotTable Fields menu to really get started working with PivotTables.

There are many ways to organize your data within a PivotTable. Using the data presented above, let’s look at the ‘Rating of Care Received’ first. 

 

Summary of ‘Rating of Care Received’ 

• Within the PivotTable Fields menu toggle on ‘Rating of Care Received’ (or click and drag) 

• ‘Rating of Care Received’ will be added to the Rows field 

• Navigate back to ‘Rating of Care Received, and click and drag the option down to the Values field 

Note that as you drag and drop data into their respective field, the table in the spreadsheet will update. This provides you within instantaneous feedback. If you drop data into the wrong field, simply click and drag the data outside of the box and it will be removed from the PivotTable. 

 

We now have a basic summary of the Counts of survey participants based on their response to the ‘Rating of Care Received’ question. However, you’ll notice that the Row Labels are ordered alphabetically. PivotTables will automatically organize text date alphabetically; numbers will be ordered numerically, and dates will be ordered chronologically. This is likely not the order that you’d prefer the data to be organized in. To reorder the Row Labels, you can click on a label (e.g., Very good) and drag it to the desired location (e.g., below Excellent). 

The data are currently summarized at the aggregate level. That is, all survey responses, regardless of Date, Clinic, Gender, or Age Range are summarized in the PivotTable. But you would probably like to summarize the data at a more granular level. 

 

‘Rating of Care Received’ by Date 

• Click and drag the Date data into the Columns field 

Immediately, you will notice that when you drag the Date data into the Columns field, the Columns field populates with Years, Quarters, and Date. This will occur automatically for dates (if the data are formatted properly as dates). But you may not want all these additional levels added to the PivotTable. For undesired labels, simply click and drag to remove. In this example, we want only Years and will remove the Quarters and Date information.

Note: Dates can be grouped by Seconds, Minutes, Hours, Days, Months, Quarters, and Years. By right-clicking on the Date labels in the PivotTable you will get a menu with the option ‘Group’. This will open a menu where you can select the grouping level you desire. If you do not want the data grouped, right-click on the Date labels in the PivotTable and click the ‘Ungroup’ option from the menu. 

We now have the Counts of each response from 2017 to 2021. This may be good enough, but having the percent response rate will allow for better comparisons between years. 

Counts to Percent of Column Total 

• Within the PivotTable Fields menu, click the arrow of Count of Rating of Care Received within the Values field 

• Select Value Field Settings from the pop-up menu 

• Select Show Value As within the Value Field Settings menu 

• ‘Show value as’ the % of Column Total 

The ‘Rating of Care Received’ data will now be summarized as the percent of the column total (i.e., summarized by Year).

Again, these results may be sufficient for your reporting needs. However, you may be asked to break the data down by a specific group. This can be accomplished with Filters and Slicers. A Filter is a built-in drop-down menu within the PivotTable, while a Slicer is a separate filter menu that moves independently of the PivotTable. Both are used to filter data based on one or more variables. However, a Slicer has the added benefit of being able to link to multiple PivotTables. Selecting a filter option within a Slicer can apply the filter across multiple, linked PivotTables at the same time.

Adding a Clinic Filter 

• Drag the Clinic data into the Filters field 

Adding a Clinic Slicer 

• Navigate to the PivotTable Analyze tab at the top right of the workbook 

• Select ‘Insert Slicer’ 

You now can actively filter your data using either the Filter or Slicer option. This is beneficial when you do not need to present all clinics’ data at the same time. It also offers interactivity within the spreadsheet, where different clinic results can be prepared with a few clicks.

---

Adding multiple variables to PivotTable fields

With the previous example, we only entered a single variable per PivotTable field (Filters, Rows, Columns, and Values). However, you can add as few, or many, variables as needed in each field. Note that with Columns, Rows, and Values the data will become nested and the PivotTable can become expansive quickly. For example, by pulling the Clinic data out of Filters and moving it into the Rows field, we can get a summary by clinic within the same PivotTable.

Because PivotTables update quickly as you drag and drop variables into the different fields, I recommend experimenting with your PivotTables in the beginning. With experience and experimentation, you will get a better feel for how PivotTables should be organized to best summarize your data. PivotTables are powerful tools, and this quick walkthrough only scratches the surface of what PivotTables can do.

---

Up your PivotTable game

Design 

 If desired, you can accept the default PivotTable format. But, if you’re anything like me, you will want to change the PivotTable design as soon as possible. Luckily, this is simple. When clicked within your PivotTable, a Design tab will appear at the top right of the Excel workbook (next to PivotTable Analyze). Within this tab, you have the option of changing the style and colours of your PivotTable. You also have the option to insert headers, banded rows, and banded columns. Further, you have options to include or exclude data summaries (e.g., row or column totals). 

 The Design tab will drastically improve the look of your PivotTables. Match to your company or clients’ colours or select a design that distinguishes between different datasets. Or, if you prefer, remove all design formatting for a less distracting appearance. 

Grouping non-date data 

 I briefly discussed how dates are grouped within PivotTables. Just as with dates, other variables can be grouped too. For example, with the ‘Rating of Care Received’ row labels, you may want to group the responses into fewer categories (e.g., Positive responses = Excellent, Very good; Neutral responses = Good, Fair; Negative responses = Poor, Very poor). To group the labels, highlight the labels you wanted grouped (e.g., Positive responses = Excellent, Very good) and right click. Navigate to the ‘Group’ option within the menu. This will group these labels under ‘Group 1’, which can be changed to any group title you want by simply writing within the group label cell. 

Using Slicers to connect two or more PivotTables 

 Like I mentioned previously, it is possible to link multiple tables to the same Slicer. With the Slicer already created in the previous example, adding an additional linked PivotTable is easy. For this example, I copied the original PivotTable creating two identical PivotTables. For simplicity, I filtered the Column Labels to present only 2020 in the first PivotTable and 2021 in the second PivotTable. 

To link a Slicer to the two PivotTables, right click a Slicer that you have already created. From our example, this is the Clinic Slicer. From the menu, navigate and select Report Connections to bring up all potential connections for the Slicer. The list of Report Connections will list the different PivotTables for which it can connect. Simply toggle on all PivotTables you want to connect to the Slicer.

You will now have two PivotTables that can be filtered simultaneously with the click of button. Select any option within the Slicer and both PivotTables will update. You can use this approach to link several PivotTables and can be useful for designing interactive dashboards within Excel.

---

You should now have the fundamentals to begin working with PivotTables. With no background knowledge of Excel formulas required, PivotTables offer a fast, accurate, and intuitive alternative to organize and analyze your data.

The ease and flexibility of PivotTables encourages experimentation. And the best method for mastering PivotTables is to jump in and experiment with your data. Soon you’ll be able to tie in all these fundamentals to generate insights with ease.

---

Sign up for our newsletter

We’ll let you know about our new content, and curate the best new evaluation resources from around the web!

Email Address


Sign Up

We respect your privacy.

Thank you!