How to Create a FIND and REPLACE in Excel

I once inherited a spreadsheet with over 3,000 rows where someone had typed “N/A,” “n/a,” “NA,” and “-” all to mean the same missing value. My first thought was that I’d have to fix each one by hand. Then I remembered Find and Replace — and within about two minutes, every inconsistent entry was standardized. It’s one of those features that seems basic on the surface but saves an enormous amount of manual work once you learn its full capabilities.

In this guide, I’ll cover everything from the basics of Find and Replace to advanced techniques using wildcards and formatting options.

What Is Find and Replace?

Find and Replace is a built-in Excel tool that lets you search for specific text, numbers, or formatting within a worksheet or entire workbook, and replace it with something else — either one instance at a time or all at once. It works on cell values, formulas, and even cell formatting, making it far more powerful than a simple text search.

Why Use Find and Replace?

• It quickly standardizes inconsistent data entries.
• It saves time compared to manually editing cells one by one.
• It can search and replace within formulas, not just plain text.
• It supports wildcards for flexible, pattern-based searching.
• It can search an entire workbook, not just the active sheet.

Required Data Before You Start

You don’t need any special setup — just a spreadsheet with data you want to search through. That said, it helps to:

• Know exactly what text or value you’re looking for (or the pattern, if using wildcards).
• Decide whether you want to replace one instance or all instances at once.
• Consider whether the search should be case-sensitive or match the entire cell content only.

Step-by-Step: How to Use Find and Replace

Step 1: Open the Find and Replace Dialog

Press Ctrl+H on your keyboard, or go to Home > Find & Select > Replace.

Step 2: Enter the Text to Find

In the “Find what” field, type the text or value you want to search for (e.g., “N/A”).

Step 3: Enter the Replacement Text

In the “Replace with” field, type what you want it changed to (e.g., leave blank to remove it entirely, or type “Not Available”).

Step 4: Choose Your Search Scope

Click Options to reveal additional settings:

• Within – choose “Sheet” or “Workbook” depending on how broad your search should be.
• Search – choose “By Rows” or “By Columns.”
• Look in – choose “Formulas,” “Values,” or “Comments.”
• Match case – check this if you need exact capitalization matching.
• Match entire cell contents – check this if you only want to replace cells that contain exactly that text, not partial matches within a larger string.

Step 5: Click Replace All (or Replace One at a Time)

• Replace All – instantly changes every matching instance across your selected scope.
• Find Next then Replace – lets you review and confirm each change individually, which is safer for sensitive data.

Step 6: Review the Confirmation

Excel shows a message telling you how many replacements were made, so you can verify the scale of the change.

Using Wildcards for Advanced Searches

Excel’s Find and Replace supports two wildcard characters:

• Asterisk (*) – represents any number of characters. For example, searching Invoice* finds “Invoice001,” “Invoice_March,” and “InvoiceFinal.”
• Question mark (?) – represents exactly one character. For example, searching 20?4 finds “2014,” “2024,” and “2034,” but not “20214.”

This is incredibly useful when you need to find patterns rather than exact text matches — for example, finding all cells that start with a certain prefix regardless of what follows.

Finding and Replacing Formatting

Find and Replace isn’t limited to text. You can also search and replace based on cell formatting:

1. Open Ctrl+H, then click Options.
2. Next to “Find what,” click Format and choose the formatting you’re searching for (like a specific fill color).
3. Next to “Replace with,” click Format and choose the new formatting you want applied.
4. Click Replace All.

This is especially handy when cleaning up inconsistent formatting across a large, messy spreadsheet inherited from someone else.

Practical Example

Imagine you’re cleaning a customer database where phone numbers were entered inconsistently — some with dashes (555-123-4567), some with dots (555.123.4567), and some with spaces (555 123 4567). You could run three quick Find and Replace passes:

1. Find . replace with -
2. Find (space) replace with - (within the phone number column only, to avoid affecting names or addresses)

Within seconds, every phone number follows the same consistent format.

Another example: replacing an outdated company name throughout an entire workbook after a rebrand. Using “Within: Workbook” ensures every sheet gets updated in one pass, rather than repeating the process sheet by sheet.

Common Mistakes to Avoid

1. Not limiting the search scope – running “Replace All” across an entire workbook when you only meant to change one sheet can cause unintended changes elsewhere. Always double-check the “Within” setting.
2. Ignoring “Match entire cell contents” – without this checked, searching for “10” could also match “100,” “1000,” or “210,” causing unwanted replacements.
3. Forgetting formulas can be affected – if “Look in” is set to “Formulas,” Find and Replace can accidentally alter cell references within formulas, not just plain text values.
4. Not previewing before a mass replace – for sensitive or important data, use “Find Next” and manually confirm each replacement instead of blindly clicking “Replace All.”
5. Overlooking case sensitivity when it matters – if your data distinguishes between “CA” (California) and “ca” (a lowercase abbreviation used elsewhere), forgetting to check “Match case” could cause incorrect replacements.

Troubleshooting Tips

• Replace All says “0 replacements made”? Double-check for extra spaces, hidden characters, or that you’re searching the correct sheet/workbook scope.
• Formulas broke after a replace? If you accidentally replaced text within formula syntax (like replacing a comma or equals sign), undo immediately with Ctrl+Z and adjust your search scope to “Values” instead of “Formulas.”
• Wildcard search not working as expected? Remember that * and ? are wildcards only when “Match entire cell contents” is unchecked, or when used within a Find field specifically expecting patterns — test on a small sample first.
• Formatting replace not applying? Make sure you clicked into the correct “Format” button (Find what vs. Replace with) and that the format criteria closely match what’s actually in your cells.

Real-World Use Cases

• Data cleaning – standardizing inconsistent text entries like “N/A,” “TBD,” or abbreviations.
• Rebranding updates – replacing an old company name, product name, or URL across an entire workbook.
• Formula auditing – locating every instance of a specific cell reference or function across a large model.
• Formatting cleanup – replacing inconsistent fill colors or fonts inherited from merged spreadsheets.
• Bulk corrections – fixing a common typo or misspelling that appears throughout a large dataset.

Best Practices

• Always work on a copy of your file (or use version history) before running a large “Replace All,” especially across an entire workbook.
• Use “Match entire cell contents” whenever you want precision over partial matches.
• Test wildcard searches on a small range first to confirm they behave as expected before applying broadly.
• Combine Find and Replace with filters — filter your data first, then search only within the visible rows for targeted cleanup.
• Document major bulk replacements (especially rebranding or formula changes) so team members understand what changed and why.

Final Thoughts

Find and Replace is deceptively simple on the surface but incredibly powerful once you dig into its full feature set — wildcards, formatting searches, and workbook-wide scope. It’s one of the fastest ways to clean up messy data, standardize formatting, and make sweeping updates without touching a single cell manually. Once you get comfortable with Ctrl+H, you’ll reach for it constantly.