How to Code & Recode Data in Excel

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Statistical Software for Marketing Research

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:04 Market Researchers and Data
  • 1:05 Excel Commands to Re-Code Data
  • 3:55 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed
Lesson Transcript
Instructor: Scott Tuning

Scott has been a faculty member in higher education for over 10 years. He holds an MBA in Management, an MA in counseling, and an M.Div. in Academic Biblical Studies.

When performing statistical calculations in market research, it is often necessary to convert one value type to another. In this lesson, you will learn how to re-code data in Microsoft Excel in order to facilitate data analysis.

Market Researchers and Data

Thomas has been selling music in the tri-state area for more than 30 years. It's never made him rich, but he loves his work. As he nears retirement, he can't imagine not coming in to the store every day. Nevertheless, it is time, and Thomas begins preparations to decide whether to sell the store or simply close up. As an older gentleman, Thomas never paid much attention to his books. He did most of his business on paper or with a handshake, and as he looks to retirement, his lack of data becomes a headache.

Thomas first has to break down some basic information about his sales. Since he lives near three state lines, his customers came from nearly 12 towns across all three states. Thomas kept a spreadsheet with his customer information, but rather than collecting a customer's city and state, Thomas simply recorded their zip code in an Excel spreadsheet. Now that he needs to determine the number of customers from each state, Thomas' zip codes need to be re-coded so that he can get an answer to his research question.

Excel Commands to Re-Code Data

This first figure shows Thomas' present data:

Figure 1. The original spreadsheet before re-coding.
Figure 1

And this next figure is an example of what Thomas wants his data to look like after re-coding it:

Figure 2. Thomas needs data that looks more like this.
Figure 2

One way Thomas can accomplish this is to use the Excel command VLOOKUP. This command allows Thomas to create a third table in order to re-code his zip code list into a list of states. This figure shows this type of re-coding table:

Figure 3. The translation table used by Thomas.
Figure 3

Once the zip codes have been re-coded into one of the three states, the COUNT command can be used to count the number of customers per state.

In plain English, Thomas wants to instruct the program to look in the postal code column, compare the zip code against his translation table, and return the abbreviation for the state into his column. The technical formula would look something like this:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

This is a great start for Thomas, but the result isn't perfect yet. Even after using the lookup table, his spreadsheet still has hundreds of zip codes that correspond to locations outside the tri-state area. Thomas needs all of those values re-coded into a single output. Since there are so many orphan zip codes, a lookup table isn't practical. Thomas might as well just manually fix them all if that's the case. Since that's inefficient, Thomas needs a command to replace all the orphan postal codes with a single value he wants to call ''Out of State.'' For this, Thomas can re-code using the IF command.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support