Exercise 1: Data Management, Validation & Custom Formatting

🎯Objective

Master data hygiene, custom number formatting, list validation, and sorting on transactional retail records.

Source Data

Path: 09_Source_Materials/Module 2/2-Module_2 Test Data.xlsx
Sheet: Dataset

Tasks & Challenges

**Level 1 (Recall)**: Identify the 4 primitive data types present in the dataset and format the `Revenue` column as Accounting currency (`$#,##0.00`). ✅ 2026-09-29

**Level 2 (Application)**: Apply custom number formatting to the `Phone Number` column so that 10 raw digits display as `(###) ###-####`. ✅ 2026-09-29

**Level 3 (Governance)**: Create a Data Validation dropdown list for the `Region` column restricting entry strictly to: `North, South, East, West`. Set error style to **Stop**. ✅ 2026-09-29

**Level 4 (Sorting)**: Perform a multi-level sort: Primary by `Region` (Ascending), Secondary by `Revenue` (Descending). ✅ 2026-09-29

**Level 5 (Deduplication)**: Identify and remove any duplicate customer transactions based on composite key `[Customer ID] + [Transaction Date]`. ✅ 2026-09-29

Solutions available in Ex01 Solutions.

Solutions: Exercise 1 (Data Management & Formatting)

Task 1: Accounting Format

Select Revenue column -> Ctrl + 1 -> Accounting -> Symbol $, 2 decimal places.

Task 2: Custom Phone Number Formatting

Select Phone column -> Ctrl + 1 -> Custom -> Type: (###) ###-####.

Task 3: Data Validation Dropdown

Select Region cells -> Alt + A + V + V -> Allow: List -> Source: North, South, East, West. Error alert: Style = Stop.

Task 4: Multi-Level Sort

Data > Sort -> Level 1: Region (A to Z) -> Add Level -> Level 2: Revenue (Largest to Smallest).