Tanjima Hossain
If you are looking for ways to encode survey data in Excel, then you are in the right place. Here, we will show 4 easy steps for doing this task.
Download Practice Workbook
4 Easy Steps to Encode Survey Data in Excel
In the following dataset, you can see some data from a survey among some jobholders. We have gathered their gender, age, occupation, and salary here. But we want to encode them so anyone with this survey data cannot understand it easily. For decoding these data they have to know the coding symbols.
For creating this article, we have used Microsoft Excel 365 version. However, you can use any other version at your convenience.
Step-1: Classifying Values into Different Groups
Here, we have created some tables to put down the corresponding symbols of different Genders, Ages, Occupations, and Salaries.
- Firstly, we put down the stated genders in our survey data. We have assigned the Male as 1, and the Female as 2.
- Now, it’s time to classify the age group of the survey. Here, we have classified the age group of 20–30 as 1, 30–40 as 2, and 40–50 as 3.
- To encode the occupations of different respondents of the survey, we assigned 1 for Faculty, 2 for Software Engineers, 3 for Managers, and 4 for Construction Workers.
- Finally, we will encode the salaries of the participants of this survey. We have classified the salary group for $10,000-$20,000 as 1, $20,000-$30,000 as 2, $30,000-$40,000 as 3, and $40,000-$50,000 as 4.
Step-2: Inserting New Columns with Coded Column Headers
To enter the corresponding symbols for the gender, age, occupation, and salary of the participants of our survey we have to add some new columns.
- Insert 4 columns after the Gender column, Age column, Occupation column, and Salary column correspondingly.
- Name these newly added columns as G (short form for Gender), A (short form for Age), O (short form for Occupation), and S (short form for Salary).
Step-3: Inserting Formulas to Encode Survey Data in Excel
In this step, we will use the IF function to apply multiple conditions to have the symbols corresponding to the data of this survey.
- Type the following formula in cell G5.
=IF(C5="Male",1,2)Here, IF will check if the value in cell C5 is Male, if it is then we will get 1 otherwise 2.
- Drag down the Fill Handle tool.
In this way, we encoded all of the genders of the survey participants.
- Now, use the following formula to have the symbols corresponding to different age groups.
=IF(AND(E5>=20,E5<=30),1,(IF(AND(E5>=30,E5<=40),2,3)))Formula Breakdown
- AND(E5>=20, E5<=30) → The AND function will check both of these conditions, and return TRUE for satisfying both, otherwise FALSE. If we get TRUE here, then IF will return 1, otherwise, we will go to the next IF function.
- IF(AND(E5>=30, E5<=40),2,3) → If both of the conditions are satisfied then AND will return TRUE, as a result, we will get 2 otherwise 3.
- Similarly, we will use the following formula for generating different coded values for different occupations.
=IF(G5="Faculty",1,(IF(G5="Software Engineer",2,(IF(G5="Manager",3,4)))))Here, we have used 4 IF functions to generate 1 for Faculty, 2 for Software Engineers, 3 for Managers, and 4 for Construction Workers.
- Finally, we will use the following formula to encode the salaries of this survey data.
=IF(AND(I5>=10000,I5<=20000),1(IF(AND(I5>=20000,I5<=30000),2(IF(AND(I5>=30000,I5<=40000),3,4)))))Here, we have used 3 IF functions and AND functions to generate 1 for the $10,000-$20,000 salary group, 2 for the $20,000-$30,000 salary group, 3 for the $30,000-$40,000 salary group, and 4 for the $40,000-$50,000 salary group.
Step-4: Pasting As Values and Deleting Unnecessary Columns
Here, we will paste our created table in the previous step as values in a new sheet.
- Copy the whole dataset of the Encoded sheet by pressing CTRL+C.
- Now, go to the Final sheet, select cell B4, and right-click here.
- Choose the Values option from different Paste Options.
In this way, we have pasted the dataset where we don’t have any formulas only values.
- Now, select the unnecessary non-adjacent columns by selecting them while pressing the CTRL key.
- Delete the selected columns.
Eventually, we will get the following table with encoded survey data.
Read More: How to Encode Data in Excel (with Easy Steps)
How to Show Survey Results in Excel
If you want to display your survey data vividly in Excel, then you can follow this section.
Suppose, we have the following dataset where we have our survey data of the satisfaction of different employees. For different mental conditions of satisfaction, we have created five groups to measure the level of their satisfaction. Then, we entered the numbers representing the number of participants supporting the criterion.
To make this survey more eye-catching, and easy reading you can follow this link.
Practice Section
To practice by yourself, we have created a Practice section on the right side of each sheet.
Conclusion
In this article, we have discussed various steps to encode survey data in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.
