Bird
Raised Fist0
Excelspreadsheet~10 mins

Find and replace in Excel - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Scenario Mode
👤 Your Role: You are an office assistant managing a product inventory list.
📋 Request: Your manager wants you to update the product names by replacing all occurrences of 'OldBrand' with 'NewBrand' in the product list.
📊 Data: You have a list of products with their IDs and names. Some product names contain the brand name 'OldBrand' which needs to be updated.
🎯 Deliverable: An updated product list where every 'OldBrand' in product names is replaced with 'NewBrand'.
Progress0 / 5 steps
Sample Data
Product IDProduct Name
101OldBrand Laptop
102OldBrand Smartphone
103OldBrand Headphones
104NewBrand Charger
105OldBrand Tablet
106Accessory Pack
1
Step 1: Select the column with product names to prepare for find and replace.
Click on the header of the 'Product Name' column to select all product names.
Expected Result
All product names are highlighted and ready for editing.
2
Step 2: Open the Find and Replace dialog box.
Press Ctrl + H on your keyboard to open the Find and Replace window.
Expected Result
The Find and Replace dialog box appears.
3
Step 3: Enter the text to find and the replacement text.
In the 'Find what' box, type 'OldBrand'. In the 'Replace with' box, type 'NewBrand'.
Expected Result
'OldBrand' is set to be replaced with 'NewBrand'.
4
Step 4: Replace all occurrences of 'OldBrand' with 'NewBrand'.
Click the 'Replace All' button in the Find and Replace dialog.
Expected Result
All 'OldBrand' texts in the product names are replaced with 'NewBrand'. A message shows how many replacements were made.
5
Step 5: Verify the changes in the product list.
Look through the 'Product Name' column to confirm all 'OldBrand' entries are now 'NewBrand'.
Expected Result
Product names like 'OldBrand Laptop' now read 'NewBrand Laptop'.
Final Result
Product ID | Product Name
-----------|-------------------
101        | NewBrand Laptop
102        | NewBrand Smartphone
103        | NewBrand Headphones
104        | NewBrand Charger
105        | NewBrand Tablet
106        | Accessory Pack
✓All product names previously containing 'OldBrand' have been updated to 'NewBrand'.
✓Products without 'OldBrand' in their names remain unchanged.
✓The inventory list is now consistent with the new branding.
Bonus Challenge

Use a formula to create a new column that automatically replaces 'OldBrand' with 'NewBrand' without changing the original data.

Show Hint
Use the SUBSTITUTE function like =SUBSTITUTE(B2, "OldBrand", "NewBrand") in a new column.

Practice

(1/5)
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