Table of Contents
Reverse coding in Excel is a process of reversing the order of the cells in a certain range of cells. It is useful when you want to rearrange the data in a certain way, such as in a descending or ascending order. To perform reverse coding in Excel, you can use the Sort command and select the reverse order in the pop-up window. As an example, you can select a range of cells containing numbers in ascending order and sort them in the reverse order to get the numbers in descending order. This same technique can be used to reverse the order of numbers, words, or text in a certain range of cells.
When creating surveys, researchers sometimes rephrase “positive” questions in a “negative” way to make sure that individuals are giving consistent responses.
We say that these types of questions are reverse-coded.
When using a survey to assign a composite score to individuals, it’s important to make sure the reverse-coded questions are reverse-scored as well.
The following example shows how to reverse the scores on reverse-coded questions in Excel.
Example: Reverse Coding in Excel
Suppose researchers administer a survey with 5 questions to 10 individuals in which the possible responses to each questions are:
- Strongly Agree
- Agree
- Neither Agree Nor Disagree
- Disagree
- Strongly Disagree
The following screenshot shows the results of the survey in which “Strongly Agree” is assigned a value of 5, “Agree” is assigned a value of 4, and so on:
Suppose questions 2 and 5 are reverse coded, so we must reverse their scores.
That is:
- 1 should become 5.
- 2 should become 4.
- 3 should become 3.
- 4 should become 2.
- 5 should become 1.
The easiest way to do this is to take the max possible score (5) and add 1 to get 6. Then subtract the original scores from 6 to get the reverse scored value.
For example:
- 5 becomes: 6 – 5 = 1.
- 4 becomes: 6 – 4 = 2.
- 3 becomes: 6 – 3 = 3.
- 2 becomes: 6 – 2 = 4.
- 1 becomes: 6 – 1 = 5.
To do this in Excel, simply copy and paste all of the original answers into a new area on the spreadsheet:
In cell E17 type: =6-E2. Then copy and paste this formula down to all other cells in column E.
The scores in column B and column E will now both be reverse coded:
The following tutorials explain other commonly used terms in questionnaires and surveys: