Find and replace in Excel - Step-by-Step Guide

Introduction

Find and replace helps you quickly locate specific text or numbers in your spreadsheet and change them all at once. This saves time when you need to fix mistakes or update data without searching cell by cell.

When you want to correct a misspelled word throughout your sheet, like changing 'Jhon' to 'John'.

When you need to update a product code that has changed in many rows.

When you want to replace all instances of a date format from 'MM/DD/YYYY' to 'DD/MM/YYYY'.

When you want to remove extra spaces by finding double spaces and replacing them with single spaces.

When you want to quickly change all occurrences of a placeholder text like 'TBD' to actual values.

Steps

Step 1: Click

- Home tab, Editing group, Find & Select button

A menu appears with options including Find and Replace

Step 2: Select

- Replace from the Find & Select menu

The Find and Replace dialog box opens with two input boxes: Find what and Replace with

Step 3: Type

- Find what box

The text or number you want to find is entered

Step 4: Type

- Replace with box

The new text or number you want to use as replacement is entered

Step 5: Click

- Find Next button

Excel highlights the first cell that matches the Find what text

Step 6: Click

- Replace button

The highlighted cell's content changes to the Replace with text and the next match is highlighted

Step 7: Click

- Replace All button

All instances of the Find what text in the sheet are replaced at once, and a message shows how many replacements were made

Step 8: Click

- Close button

The Find and Replace dialog box closes

Before vs After

Before

Column A contains product codes like 'ABC123', 'ABC124', 'ABC125' and you want to update 'ABC' to 'XYZ'.

After

Column A now shows 'XYZ123', 'XYZ124', 'XYZ125' after replacing all 'ABC' with 'XYZ'.

Settings Reference

Match case

📍 Find and Replace dialog box, Options button

Makes the search match uppercase and lowercase letters exactly

Default: Unchecked

Match entire cell contents

📍 Find and Replace dialog box, Options button

Finds only cells where the whole cell matches the Find what text

Default: Unchecked

Within

📍 Find and Replace dialog box, Options button

Chooses whether to search only the current sheet or all sheets in the workbook

Default: Sheet

Search

📍 Find and Replace dialog box, Options button

Determines the order in which Excel searches cells

Default: By Rows

Common Mistakes

Not clicking Replace All when wanting to change all instances.

Only the first found instance changes, so others remain unchanged.

Click Replace All to update every matching cell at once.

Leaving Match case checked when you want to replace text regardless of letter case.

Excel will only find text that exactly matches the letter case, missing others.

Uncheck Match case to find and replace text regardless of uppercase or lowercase.

Typing partial text in Find what but expecting whole cell matches to change.

If Match entire cell contents is checked, partial matches won't be found.

Uncheck Match entire cell contents to find partial matches or type the full cell content.

Summary

Find and replace lets you quickly locate and change text or numbers in your spreadsheet.

You can replace one instance at a time or all at once using Replace or Replace All buttons.

Use options like Match case and Match entire cell contents to control how Excel searches.

Practice

1. What does the Find and Replace feature in Excel primarily help you do?

easy

A. Quickly update many cells by changing specific text or values

B. Create new formulas automatically

C. Sort data alphabetically

D. Protect cells from editing

Solution

1. Step 1: Understand the purpose of Find and Replace

   Find and Replace is used to locate specific text or values and change them across many cells quickly.

2. Step 2: Compare with other options

   Creating formulas, sorting data, or protecting cells are different Excel features unrelated to Find and Replace.

3. Final Answer:

   Quickly update many cells by changing specific text or values -> Option A

4. Quick Check:

   Find and Replace = Update many cells fast [OK]

Hint: Remember: Find and Replace changes existing content fast [OK]

Common Mistakes:

• Confusing Find and Replace with sorting or filtering
• Thinking it creates formulas
• Assuming it protects cells

2. Which keyboard shortcut opens the Find and Replace dialog in Excel?

easy

A. Ctrl + F

B. Ctrl + P

C. Ctrl + H

D. Ctrl + R

Solution

1. Step 1: Recall Excel shortcuts for Find and Replace

   Ctrl + F opens the Find dialog, but Ctrl + H opens the Find and Replace dialog.

2. Step 2: Verify other shortcuts

   Ctrl + R is for fill right, Ctrl + P is for print, so they are incorrect here.

3. Final Answer:

   Ctrl + H -> Option C

4. Quick Check:

   Find and Replace shortcut = Ctrl + H [OK]

Hint: Use Ctrl + H to open Replace directly [OK]

Common Mistakes:

• Using Ctrl + F which only finds but doesn't replace
• Confusing Ctrl + R or Ctrl + P with Find and Replace
• Trying to open Replace from menus only

3. You have a column with values: Apple, Banana, Apple, Cherry. Using Find and Replace to replace Apple with Orange and clicking Replace All will result in:

medium

A. Apple, Banana, Apple, Cherry

B. Orange, Banana, Orange, Cherry

C. Orange, Banana, Apple, Cherry

D. Apple, Banana, Orange, Cherry

Solution

1. Step 1: Identify all occurrences of 'Apple'

   The list has 'Apple' in the 1st and 3rd positions.

2. Step 2: Replace all 'Apple' with 'Orange'

   Using Replace All changes both occurrences to 'Orange'. The list becomes Orange, Banana, Orange, Cherry.

3. Final Answer:

   Orange, Banana, Orange, Cherry -> Option B

4. Quick Check:

   Replace All changes every match [OK]

Hint: Replace All changes every matching cell instantly [OK]

Common Mistakes:

• Replacing only the first occurrence
• Replacing wrong values
• Not understanding Replace All affects all matches

4. You tried to replace cat with dog in a list containing catalog, cat, and scatter. After Replace All, the results were dogalog, dog, and sdogter. How can you fix this?

medium

A. Change the font color instead of replacing text

B. Use Replace instead of Replace All

C. Use Find only without Replace

D. Use Match entire cell contents option before replacing

Solution

1. Step 1: Understand why partial matches changed

   Replacing 'cat' without restrictions changes parts of words like 'catalog' and 'scatter'.

2. Step 2: Use 'Match entire cell contents' option

   This option restricts replacement to cells exactly matching 'cat', avoiding partial word changes.

3. Final Answer:

   Use Match entire cell contents option before replacing -> Option D

4. Quick Check:

   Match entire cell contents avoids partial replacements [OK]

Hint: Check 'Match entire cell contents' to avoid partial word changes [OK]

Common Mistakes:

• Replacing without matching whole cell content
• Using Replace instead of Replace All unnecessarily
• Trying to fix by changing font color

5. You have a large sheet with product codes like AB123, AB124, AC123. You want to replace all codes starting with AB to start with XY instead, keeping the numbers intact. Which Find and Replace approach works best?

hard

A. Find 'AB' and replace with 'XY' without wildcards

B. Find 'AB*' and replace with 'XY*' using wildcards

C. Find 'AB' and replace with 'XY' using wildcards

D. Manually edit each code one by one

Solution

1. Step 1: Understand Excel's Find and Replace wildcard behavior

   Excel supports wildcards in Find but not in Replace. The '*' wildcard matches any characters in Find but cannot be used in Replace text.

2. Step 2: Replace 'AB' with 'XY' directly

   Finding 'AB' and replacing with 'XY' changes only the prefix, keeping the numbers intact, e.g., 'AB123' becomes 'XY123'.

3. Step 3: Why other options fail

   Find 'AB*' and replace with 'XY*' using wildcards tries to use '*' in Replace which Excel does not support. Find 'AB' and replace with 'XY' using wildcards is invalid syntax. Manually edit each code one by one is inefficient.

4. Final Answer:

   Find 'AB' and replace with 'XY' without wildcards -> Option A

5. Quick Check:

   Replace prefix by replacing 'AB' with 'XY' directly [OK]

Hint: Replace prefix by replacing 'AB' with 'XY' directly [OK]

Common Mistakes:

• Trying to use wildcards in Replace text
• Replacing manually instead of using Replace All
• Using incorrect wildcard syntax