← Back to ISMG-2050 Course Hub

Skills Covered

• Flash Fill (Ctrl+E) for automatic pattern recognition and data extraction
• Relative cell references (default behavior)
• Absolute cell references using the $ operator
• Mixed cell references ($A1 vs A$1)
• Toggle absolute reference mode with F4

Part 1: Flash Fill — Name Parsing (40 pts)

1. Open starter_data_wk3.xlsx in Excel. Sheet1 contains a column A with full names in the format "FirstName LastName" (rows 2\u201315).
2. In cell B1, type the header Last Name.
3. In cell B2, manually type the last name from A2 (e.g., if A2 is "John Smith", type "Smith"). Press Enter.
4. Select cell B3. Press Ctrl+E. Excel will auto-detect the pattern and fill B3:B15 with all last names extracted from column A.
5. Click the flash-filled cell if prompted: review the preview. If correct, confirm. If not, correct one example cell and press Ctrl+E again.
6. In cell C1, type Middle Initial. In C2, manually type the middle initial from A2 (e.g., "M"). Select C3, press Ctrl+E.
7. In cell D1, type Email Address. In D2, manually type a constructed email: john.smith@university.edu (first.last format). Select D3, press Ctrl+E. Verify the pattern applied consistently.

Part 2: Absolute vs. Relative References — Currency Conversion (35 pts)

7. In a new sheet, rename it "Conversion".
8. A1: type Currency, B1: type Amount (USD), C1: type Converted (EUR)
9. In cell D1, enter the exchange rate as a constant: 0.92 (label cell D1 above with "EUR/USD Rate" in C1).
10. In cells A3:A8, enter these countries/currencies: Euro, British Pound, Japanese Yen, Canadian Dollar, Swiss Franc, Australian Dollar.
11. In cells B3:B8, enter these USD amounts: 100, 250, 1000, 500, 750, 2000.
12. In cell C3, type the formula: =B3*$D$1. Note the $ locking row and column on D1. This ensures that when you copy this formula down, it always references the single exchange rate cell.
13. Copy C3 down to C4:C8. Verify each uses the correct amount multiplied by 0.92.
14. Now change the exchange rate in D1 to 0.85. Confirm all converted values update automatically (proving the absolute reference works).

Part 3: Mixed References — Tax Rate Table (25 pts)

14. In a third sheet, rename it "TaxTable".
15. A1: type Tax Rate. In B1, enter the tax rate: 0.0825 (8.25% sales tax).
16. In A3, type Item, B3 type Price, C3 type Tax Amount, D3 type Total with Tax
17. In A4:A9, enter: Laptop, Mouse, Keyboard, Monitor, Headset, Webcam.
18. In B4:B9, enter prices: 899, 25, 45, 350, 79, 49.
19. In C4, type the mixed reference formula: =B4*$B$1. Copy down to C5:C9.
20. In D4, type: =B4+C4. Copy down to D5:D9.
21. Select range C4:C9. Apply Currency ($#,##0.00) format.
22. Select range D4:D9. Apply Currency ($#,##0.00) format.

Submission Criteria

• File named exactly [your initials]Practice04.xlsx
• Sheet1: Name parsing with Flash Fill (columns for Last Name, Middle Initial, Email)
• "Conversion" sheet: Currency conversion using absolute references ($D$1)
• "TaxTable" sheet: Sales tax calculation using mixed/absolute references
• Changing D1 (exchange rate) must update all converted values automatically
• All number formatting applied correctly (currency, decimal places)
• AI Disclosure statement in a note below the TaxTable sheet

→ Back to Week 3\u20134 Homework Portal