How to Replace Data in Excel

Need to quickly update information across your entire spreadsheet? Manually hunting down every instance of a word, number, or code is a recipe for missed entries and wasted time. This guide will walk you through several easy ways to replace data in Excel, from a simple shortcut for bulk changes to powerful formulas that give you precise control over your edits.

Using the Find and Replace Tool for Quick Edits

For straightforward, bulk updates, Excel's built-in Find and Replace feature is your best friend. It’s perfect for tasks like correcting a recurring typo, updating a product name, or changing a status across your entire worksheet or workbook.

Let's say you need to update every instance of "Project Alpha" to "Project Omega". Here’s how to do it in seconds:

1. Press Ctrl + H on your keyboard (or Cmd + H on a Mac) to open the Find and Replace dialog box directly in the "Replace" tab.
2. In the "Find what" field, type the data you want to replace (e.g., Project Alpha).
3. In the "Replace with" field, type the new data you want to insert (e.g., Project Omega).
4. Click Replace All to change every occurrence instantly. If you want to review each change one by one, click "Find Next" and then "Replace" for each instance you want to update.

Excel will then confirm how many replacements were made. Simple as that.

Advanced Find and Replace Options

Sometimes a simple text swap isn't enough. By clicking the "Options" button in the Find and Replace dialog, you can make your search much more specific:

• Match case: Check this box for a case-sensitive search. For example, if you only want to replace "Email" but not "email", this option ensures you don't accidentally change the wrong text.
• Match entire cell contents: This is incredibly useful. It tells Excel to only replace a value if the entire cell contains exactly what's in the "Find what" box. For instance, if you want to replace the department "Sales" with "Client Success", this prevents you from changing "Salesperson" to "Client Successperson".
• Within: This dropdown lets you choose whether to search just the "Sheet" you're currently working on or the entire "Workbook". This is a lifesaver when you need to update data across multiple tabs.

Dynamic Replacements with Excel Formulas

The Find and Replace tool is great, but it permanently alters your original data. For more complex situations where you need to change data based on a condition — or you want to keep your original data intact — Excel formulas are the way to go. Formulas work by creating a new column of modified data, leaving your source unharmed.

Replacing Specific Text with the SUBSTITUTE Function

The SUBSTITUTE function is designed to replace specific text within a cell. It’s perfect for swapping out parts of a text string, like changing a domain in email addresses or updating a prefix in product IDs.

The syntax for the formula is:

SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: The cell containing the original text (e.g., A2).
• old_text: The specific text you want to replace (e.g., "-2023").
• new_text: The new text you want to insert (e.g., "-2024").
• [instance_num] (optional): Use this to specify which occurrence you want to replace. If you leave it blank, it replaces all instances.

Example: Updating Order IDs

Imagine column A has order IDs like "ORD-USA-1234". If you need to change the country code from "USA" to "US" in cell A2, you would use this formula in a new column (like column B):

=SUBSTITUTE(A2, "USA", "US")

You can then drag the fill handle (the small square in the bottom-right corner of the cell) down to apply this formula to the rest of the column.

Targeting Text by Position with the REPLACE Function

Unlike SUBSTITUTE, which looks for specific text, the REPLACE function targets a chunk of text based on its position and length. This is extremely useful for structured data like serial numbers or phone numbers where you know exactly which characters need to be changed.

The formula's syntax is:

REPLACE(old_text, start_num, num_chars, new_text)

• old_text: The source cell (e.g., A2).
• start_num: The starting position of the text you want to replace (e.g., the 5th character).
• num_chars: The number of characters to replace.
• new_text: The new text to insert in its place.

Example: Masking Phone Numbers

Let’s say you have phone numbers in column A and want to mask the middle four digits (from the 5th to the 8th character) with "XXXX". You would use:

=REPLACE(A2, 5, 4, "XXXX")

This formula starts at the 5th character, replaces the next 4 characters, and inserts "XXXX", turning "(123) 456-7890" into "(123) XXXX-7890".

Advanced Conditional Replacements with the IF Function

This is where you can give Excel some real instructions. By combining a logical test with the IF function, you can tell Excel to replace data only if a certain condition is met.

The basic syntax is simple:

=IF(logical_test, [value_if_true], [value_if_false])

Imagine you have a "Status" column (column B) and you want to replace every cell that says "Processing" with "Shipped". In a new column, you would write:

=IF(B2="Processing", "Shipped", B2)

Here’s what this formula tells Excel: Check cell B2. If it contains "Processing", then output "Shipped". If not, just output the original value from B2.

You can even nest formulas inside each other. For example, if you want to change "USA" to "US" only for orders placed in 2024 (checking a date in column C), you could write something like:

=IF(YEAR(C2)=2024, SUBSTITUTE(A2, "USA", "US"), A2)

This level of control is impossible with the standard Find and Replace tool.

Making Your Formula Replacements Permanent

After creating a new column with formulas, you will notice that the data is dynamic. If you delete the original column, your formulas will break and show an error. To make these changes permanent, you need to convert the formula results into static values using "Paste Special".

Here’s how:

1. Select all the cells in your new column containing the formula.
2. Copy them by pressing Ctrl + C (or Cmd + C).
3. Right-click on the very first cell of the original column (or a new location where you want the final data).
4. Select Paste Special... from the menu.
5. In the Paste Special dialog, choose Values and click OK.

This replaces the formulas with their calculated results. Now the new data is locked in, and you can safely delete the original column or the helper column with the formula without causing any errors.

Power Up Your Search: Replacing Data with Wildcards

Hidden inside the ordinary Find and Replace tool is an incredibly powerful feature: wildcards. Wildcards are special characters that can represent other characters in your search, allowing for flexible and pattern-based replacements.

There are two main wildcards you need to know:

• Asterisk (*): Represents any number of characters. It's a "catch-all" for variable text.
• Question Mark (?): Represents any single character. It's perfect for when you know the length of the string but a character might change.

To use them, just put them in the "Find what" field of the Find and Replace dialog (Ctrl + H).

Example 1: Using the Asterisk (*)

Let's say you have a list of email addresses from an old domain (e.g., "jane.doe@oldcompany.com," "support@oldcompany.com") and you want to change them all to "newcompany.com".

• In "Find what," you would type: *@oldcompany.com
• In "Replace with," you would type: *@newcompany.com

Excel will find every cell that ends with "@oldcompany.com", grab the unique text before it (thanks to the *), and then replace the old domain with the new one, all while preserving the username.

Example 2: Using the Question Mark (?)

Imagine you have product codes like SKU-1A, SKU-1B, SKU-1C, and you want to standardize them to a new format like PRD-22-A, PRD-22-B, etc. You notice the only thing that changes is the letter at the end.

• Find what: SKU-1?
• Replace with: PRD-22-?

Final Thoughts

Understanding when to use Excel's Find & Replace feature compared to powerful formula skills is a knowledge that saves time and prevents mistakes. From quick bulk changes to complex conditional updates, these techniques give you control over your data manipulation effectively.

These techniques are effective for text inside a single spreadsheet, but an integrated approach may require also leveraging sources of data from Google Analytics, Shopify, Facebook Ads, and other tools. These usually need a set of manual processes, exporting CSVs, cleaning, and organizing the data combined. We built Graphed to automate this process, allowing you to reduce multiple manual processes and save time. Graphed utilizes AI to create a seamless and efficient data management process that you can rely on consistently.