SUBMISSION: Module 2

📥 Starter File ⬇ Download Starter File (.xlsx)

The file contains two sheets: Raw Sales (messy data) and Product Catalog (reference table). An Instructions sheet is also included.

📋 Assignment Instructions

Part 1: Data Cleaning (Raw Sales sheet) Fix inconsistent customer names — standardize all names to Title Case (e.g., "john smith" → "John Smith"). Use PROPER() or Flash Fill. Split the City-State column — use Text to Columns (delimiter: hyphen) to separate City and State into two columns. Standardize dates — ensure all dates in column B are in a consistent format (DD-MMM-YYYY). Fix any text-formatted dates. Handle missing values — find rows with blank Customer Name or City (rows 8, 23, 37). Fill them with "Unknown" or remove the rows. Document your choice. Remove duplicates — identify duplicate customer entries (same email, different name casing) and clean them up.

Part 2: Lookup Functions (add new columns to Raw Sales) Add "Product Name" — use VLOOKUP or XLOOKUP to pull the product name from the Product Catalog sheet using the Product Code. Add "Category" — use another lookup to pull the product category. Add "Total Amount" — calculate Quantity × Unit Price for each row. Add "Price Check" — use IF() to compare the Unit Price with the Standard Price from the catalog: Within 10% → "OK" More than 10% above → "OVERPRICED" More than 10% below → "DISCOUNTED"

Part 3: Summary Create a summary section — on a new sheet or below the data, show total revenue grouped by Category using SUMIF or a small Pivot Table.

✅ Submission Checklist All customer names standardized to Title Case City and State in separate columns Dates in consistent format Missing values handled Product Name and Category columns added via lookup Total Amount and Price Check columns added Summary by category included Upload your completed .xlsx file using the form below.