Find and replace in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the Find and Replace feature in Excel?

It helps you quickly locate specific text or numbers in your spreadsheet and replace them with new content if needed.

beginner

How do you open the Find and Replace dialog box in Excel using the keyboard?

Press Ctrl + H to open the Replace tab directly, or Ctrl + F to open the Find tab.

beginner

What happens if you click 'Replace All' in the Find and Replace dialog?

Excel will replace every instance of the found text or number in the selected range or entire sheet with the new text you entered.

intermediate

Can Find and Replace be used to change formatting in Excel?

Yes, you can use Find and Replace to find cells with specific formatting and replace that formatting with new formatting without changing the cell content.

beginner

What option should you check if you want Find and Replace to match the exact case of letters?

Check the Match case option to find only text that exactly matches the uppercase and lowercase letters you typed.

Which keyboard shortcut opens the Replace tab in Excel's Find and Replace dialog?

A Ctrl + P

B Ctrl + F

C Ctrl + R

D Ctrl + H

Ctrl + H opens the Replace tab directly, while Ctrl + F opens the Find tab.

What does the 'Replace All' button do?

A Replaces all found instances in the selected area

B Replaces the first found instance only

C Finds all instances without replacing

D Deletes all found instances

'Replace All' changes every found instance in the selected range or sheet.

If you want to find text exactly as typed, including uppercase and lowercase letters, which option should you select?

A Match case

B Match entire cell contents

C Search by rows

D Use wildcards

The 'Match case' option ensures the search respects letter case.

Can Find and Replace change only the formatting of cells without changing their content?

A Only in Excel Online

B Yes

C No

D Only with VBA

You can find cells by formatting and replace their formatting without altering content.

Where does Find and Replace search by default when no range is selected?

A Only the active cell

B Only the selected column

C The entire worksheet

D Only the selected row

If no range is selected, Excel searches the entire active worksheet.

Explain how to use Find and Replace to change all occurrences of a word in a worksheet.

Think about the steps from opening the tool to completing the replacement.

Describe how you can use Find and Replace to update formatting without changing cell values.

Focus on the formatting options inside Find and Replace.

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