Find and replace in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales assistant at a retail company.

📋 Request: Your manager wants you to update the product names in the sales data because some product names have changed.

📊 Data: You have a sales data table with columns: Order ID, Product Name, Quantity, and Price. Some product names are outdated and need to be replaced with new names.

🎯 Deliverable: An updated sales data table where all old product names are replaced with the new product names.

Sample Data

Order ID	Product Name	Quantity	Price
1001	Blue T-shirt	3	15
1002	Red T-shirt	2	15
1003	Green T-shirt	5	15
1004	Blue T-shirt	1	15
1005	Yellow T-shirt	4	15
1006	Red T-shirt	3	15
1007	Green T-shirt	2	15
1008	Yellow T-shirt	1	15

1

Step 1: Open the Google Sheets file containing the sales data.

Expected Result

You see the sales data table with Order ID, Product Name, Quantity, and Price columns.

2

Step 2: Click on the Edit menu, then select Find and replace.

Expected Result

The Find and replace dialog box opens.

3

Step 3: In the Find field, type 'Blue T-shirt'. In the Replace with field, type 'Navy T-shirt'.

Expected Result

The dialog is ready to replace all 'Blue T-shirt' entries with 'Navy T-shirt'.

4

Step 4: Make sure 'Search within formulas' is unchecked and 'Match entire cell contents' is checked to avoid partial replacements.

Expected Result

Settings are correct to replace only exact matches.

5

Step 5: Click Replace all to update all 'Blue T-shirt' entries to 'Navy T-shirt'.

Expected Result

All 'Blue T-shirt' product names are replaced with 'Navy T-shirt'.

6

Step 6: Repeat steps 3 to 5 for 'Red T-shirt' to 'Crimson T-shirt'.

Expected Result

All 'Red T-shirt' product names are replaced with 'Crimson T-shirt'.

7

Step 7: Repeat steps 3 to 5 for 'Green T-shirt' to 'Emerald T-shirt'.

Expected Result

All 'Green T-shirt' product names are replaced with 'Emerald T-shirt'.

8

Step 8: Review the updated Product Name column to confirm all replacements are correct.

Expected Result

Product names now show 'Navy T-shirt', 'Crimson T-shirt', 'Emerald T-shirt', and 'Yellow T-shirt' as expected.

Final Result

Order ID | Product Name    | Quantity | Price
---------------------------------------------
1001     | Navy T-shirt    | 3        | 15
1002     | Crimson T-shirt | 2        | 15
1003     | Emerald T-shirt | 5        | 15
1004     | Navy T-shirt    | 1        | 15
1005     | Yellow T-shirt  | 4        | 15
1006     | Crimson T-shirt | 3        | 15
1007     | Emerald T-shirt | 2        | 15
1008     | Yellow T-shirt  | 1        | 15

✓All old product names have been updated to the new names.

✓The sales data is now consistent with the current product catalog.

✓No partial or incorrect replacements occurred.

Bonus Challenge

Use a formula to create a new column that shows the total price per order by multiplying Quantity and Price.

Show Hint

In a new column, enter =C2*D2 and drag the formula down for all rows.