Practice 6: Data Validation & Conditional Analysis

Key Topics & Core Concepts

Data Validation Rules: Restricting cell input using list dropdowns, whole number bounds, and custom error alerts.
External Data Ingestion: Importing delimited text datasets (customer_data.csv) into existing worksheets.
Conditional Formatting: Applying highlight cell rules, data bars, and formula-based formatting triggers.
Complex Logical Evaluation: Combining IF, AND, and OR functions to audit business data.

Assignment Overview & Deliverables

In this practice lab, you will configure data validation dropdowns, error stop alerts, conditional formatting highlights, and ingest external customer account data for Courtyard Medical.

Starter Workbook: Download CourtyardMedical-02.xlsx
Supplemental CSV: Download customer_data.csv
Completed File Name: [your initials] Practice06_Validation.xlsx

Step-by-Step Instructions

Step 1: Open Starter & Setup Input Range

Open CourtyardMedical-02.xlsx and save it as [your initials] Practice06_Validation.xlsx.
Click the Patient Entry tab. Inspect the input table spanning columns A through F.

Step 2: Enforce Dropdown List Validation

Select the Department column cells (C4:C30).
On the Ribbon, go to Data > Data Validation.
Under Allow, select List.
In the Source box, type: Cardiology, Pediatrics, Oncology, Neurology, Orthopedics.
On the Error Alert tab, set Style to Stop, Title to Invalid Department, and Error message to: Please select a valid department from the dropdown list.
Click OK. Verify that cell C4 now displays an active drop-down arrow.

Step 3: Restrict Numeric Ranges & Copay Rules

Select the Copay Amount cells (D4:D30).
Open Data Validation, set Allow to Decimal, and set Between 0.00 and 250.00.
Under the Input Message tab, check Show input message when cell is selected and type: Enter standard insurance copay ($0 - $250).
Click OK.

Step 4: Import External Customer Data

Click the Imported Records worksheet tab.
Go to Data > From Text/CSV. Select your downloaded customer_data.csv file.
In the preview window, confirm delimiter is set to Comma, then click Load To... > Existing Worksheet starting at cell A1.

Step 5: Apply Conditional Formatting Highlights

Return to the Patient Entry worksheet and select the Outstanding Balance column (E4:E30).
Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than....
Enter 500 and choose Light Red Fill with Dark Red Text.
Apply a second rule: Highlight Cells Rules > Equal To... with 0 and format with Light Green Fill with Dark Green Text.
Save and close the completed workbook.

Practice exercises are formative and prepare you for homework submissions.

Key Skills Evaluated

Skill Area	Verification Target
List Validation	In-cell dropdowns with custom Stop error messaging.
Data Bounds	Decimal bounds check between $0.00 and $250.00 with input guidance tooltip.
CSV Ingestion	Delimited text import rendered on secondary tab without row truncation.
Conditional Formatting	Threshold highlights applied to balance metrics.