How to Recode Data in Excel

Cleaning messy data is one of the most common headaches when building reports. If your spreadsheet has inconsistent categories, text responses you need to quantify, or terms you need to consolidate, you need to recode that data before you can analyze it. This tutorial will walk you through four simple but powerful methods to recode data directly in Excel, from basic formulas to more advanced, scalable techniques.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Recoding Data?

Recoding data is the process of changing the values in a column to a new set of values. It's a fundamental part of data cleaning and preparation. You're not changing the meaning of the data, but rather standardizing it for easier analysis and visualization.

A few common examples include:

• Standardizing Categories: Your data might contain multiple versions of the same thing, like "USA," "U.S.", and "United States." Recoding lets you consolidate them all into a single, clean category like "United States."
• Quantifying Qualitative Data: If you have survey responses like "Strongly Agree," "Agree," and "Disagree," you can't easily average them. Recoding them into a numerical scale (e.g., 5, 4, 3) makes them quantifiable for analysis.
• Grouping Data: You might want to group specific values into broader buckets. For example, recoding individual country names into continents like "North America," "Europe," or "Asia."

Let's look at a few ways to do this in Excel.

Method 1: Using the IF Function for Simple Recoding

The IF function is perfect for simple, binary recoding where you only have two potential outcomes. The function checks whether a condition is true, then returns one value if it is and another value if it isn’t.

The syntax for the IF function is:

=IF(logical_test, value_if_true, value_if_false)

Imagine you have a column with product categories and you want to recode "Digital" products as 1 and all "Physical" products as 0 for a calculation. Here’s how you’d do it.

Step-by-Step Instructions:

1. Create a new column. In your worksheet, add a new column next to your original data. Let's call it "Product_Type_Code." This is where your recoded values will go. It's best practice to keep your original data intact.
2. Enter the IF formula. Assuming your product categories are in column A starting at cell A2, click into cell B2 and type the following formula:

=IF(A2="Digital", 1, 0)

Here's what this formula is doing:

• A2="Digital": This is the logical test. It checks if the value in cell A2 is equal to "Digital."
• 1: This is the value that will be returned if the test is TRUE.
• 0: This is the value that will be returned if the test is FALSE.

Drag the formula down. Click on cell B2. You'll see a small square dot in the bottom-right corner of the cell (the "fill handle"). Click and drag this handle down the column to apply the formula to all your rows. Excel will automatically adjust the cell reference (A3, A4, etc.) for each row.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 2: Using the IFS Function for Multiple Categories

When you have more than two categories to recode, you could use a series of complicated "nested" IF functions, but that gets messy fast. A much cleaner solution is the IFS function (available in Excel 2019, Office 365, and later). It lets you test multiple conditions in a single, easy-to-read formula.

The syntax for IFS is:

=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...)

Let’s use a classic survey data example. Imagine you have a column with feedback ranging from "Very Satisfied" to "Very Unsatisfied" and you want to convert them to a 1-5 numerical scale.

Step-by-Step Instructions:

1. Add your new column. Create a column next to your survey data and label it "Satisfaction_Score."
2. Write the IFS formula. If your survey responses are in column C starting at C2, you would click into D2 and enter this formula:

=IFS(C2="Very Satisfied", 5, C2="Satisfied", 4, C2="Neutral", 3, C2="Unsatisfied", 2, C2="Very Unsatisfied", 1)

This formula checks the value in C2 against each condition in order. When it finds a match, it returns the corresponding value and stops.

Apply the formula to all rows. Just like with the IF function, click the fill handle on cell D2 and drag it down to recode all your survey responses.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 3: Using VLOOKUP for Large-Scale Recoding

The IF and IFS functions are great for a handful of categories, but they become impractical if you have dozens or hundreds of values to recode. For large-scale tasks, VLOOKUP is the most efficient and scalable solution.

The idea is simple: you create a separate "lookup table" that maps all your original values to your desired new values. Then, the VLOOKUP formula looks up each original value in this table and returns the correct new value.

The syntax for VLOOKUP is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let's say you have sales data with inconsistent state abbreviations ("CA," "Calif.", "NY," "N.Y.") and you want to standardize them to the official two-letter code.

Step-by-Step Instructions:

1. Create a lookup table. In a new worksheet (or on a different part of the same sheet), create a two-column table.

• Column 1 should contain all the messy, original values (e.g., "CA," "Calif.", "California").
• Column 2 should contain the new, standardized values you want to use (e.g., "CA").
• Important: Your lookup value must be in the first column of the lookup table. Let's assume your lookup table is on a sheet named "Lookup" in the range A2:B10.

1. Create your new column. Back in your main data sheet, add a new column, like "State_Standardized."
2. Write the VLOOKUP formula. If your original state data is in column E starting at E2, click into F2 and write your formula:

=VLOOKUP(E2, Lookup!$A$2:$B$10, 2, FALSE)

Let's break that down:

• E2: This is the value you want to look up from your original data.
• Lookup!$A$2:$B$10: This is your lookup table. The dollar signs $ create an absolute reference, meaning the table range won't change as you drag the formula down. This is critical for VLOOKUP to work correctly across many rows.
• 2: This is the column index number. It tells Excel to return the value from the 2nd column of your lookup table (where your new values are).
• FALSE: This tells Excel to look for an exact match. You will almost always want to use FALSE for recoding tasks to prevent incorrect matches.

Drag the formula down the column. Apply the formula to all your rows using the fill handle. Now you have a perfectly standardized column! The best part is that if you find a new messy value, you just have to add it to your lookup table, and the formula continues to work.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 4: The Fast (But Permanent) Find and Replace

If you're in a hurry and need a permanent change without creating a new column, Excel's built-in Find and Replace tool can do the job. This method directly overwrites the data in your selected column.

Warning: This is a destructive method. Because it permanently changes your data, it's a good idea to work on a copy of your dataset or column to avoid losing the original information.

Step-by-Step Instructions:

1. Select the column. Click the header of the column containing the data you want to recode. This ensures the replacement only happens within that specific column.
2. Open Find and Replace. Press Ctrl + H (or Cmd + H on Mac) to open the Find and Replace dialog box.
3. Enter your values.
4. Click "Replace All." Excel will find all instances of the original text in your selected column and replace them.
5. Repeat for all values. You will need to repeat this process for each unique value you need to recode.

This method is quick for simple one-off replacements but is not efficient for ongoing or complex recoding tasks. For those, an IFS or VLOOKUP approach is superior.

Final Thoughts

Recoding data is an essential skill for anyone who works with spreadsheets. Whether you're running a quick Find and Replace or setting up a scalable mapping table with VLOOKUP, these techniques will save you hours of manual work and make your reports far more accurate and professional.

This entire process of exporting data, cleaning it in a spreadsheet, and painstakingly building charts is precisely the kind of tedious, manual work that slows teams down. That’s why we created Graphed. We automate the whole reporting pipeline by connecting directly to your sources like Shopify, Google Analytics, and CRMs, so you're always working with live, clean data from the start. Just describe the report or dashboard you need in plain English, and our AI assistant turns your request into real-time visualizations in seconds - no more time lost recoding spreadsheets.