How to Encode Survey Data in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to encode survey data in excel

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.

Classifying data into different groups to encode survey data in excel

  • 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 2030 as 1, 3040 as 2, and 4050 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.

Classifying data into different groups to encode survey data in excel

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

salary group


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

inserting new columns to encode survey data in excel


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.

Inserting Formulas to Encode Survey Data in Excel

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.

Inserting Formulas to Encode Survey Data in Excel

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

paste as values to Encode Survey Data in Excel

  • Copy the whole dataset of the Encoded sheet by pressing CTRL+C.

copy

  • Now, go to the Final sheet, select cell B4, and right-click here.
  • Choose the Values option from different Paste Options.

paste as values to Encode Survey Data in Excel

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.

delete

Eventually, we will get the following table with encoded survey data.

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.

How to Show Survey Results in Excel

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.

practice


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.


Related Articles

Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo