DAY 23: Excel - Advanced Conditional Formatting & Data Validation

Exercise 1: Create Formula-Based Conditional Formatting

Goal: Learn to use custom formulas in conditional formatting to highlight data based on complex conditions.

Step-by-Step Instructions:

1. Open Excel and create a sample sales data table:

   A1: Name    B1: Region   C1: Sales   D1: Target   E1: Quarter
   A2: John    B2: East     C2: 45000   D2: 40000    E2: Q1
   A3: Sarah   B3: West     C3: 38000   D3: 40000    E3: Q1
   A4: Mike    B4: East     C4: 52000   D4: 45000    E4: Q2
   A5: Lisa    B5: West     C5: 41000   D5: 40000    E5: Q2
   A6: Tom     B6: East     C6: 37000   D6: 40000    E6: Q1

2. Goal: Highlight entire rows where Sales exceed Target
3. Select the entire data range (A2:E6)
4. Go to Home → Conditional Formatting → New Rule
5. Choose "Use a formula to determine which cells to format"
6. Enter the formula: =$C2>$D2 - The $ before column letter makes it absolute (always check column C and D) - No $ before row number makes it relative (checks current row)
7. Click Format button → Choose green fill color → Click OK twice
8. Observe: Entire rows where sales exceed target are highlighted green!
9. Add another rule for underperformers (Sales < 90% of Target): - Formula: =$C2<($D2*0.9) - Format: Red fill

💡 Tips:

• Use $ carefully: Column letters should have $ (absolute), row numbers shouldn't (relative)
• Formula applies to first row: Write formula for row 2, Excel adjusts for other rows automatically
• Test your formula first: Write it in a cell to verify it works before using in conditional formatting
• Manage rules: Use "Manage Rules" to edit, delete, or reorder multiple rules
• Rule order matters: Rules are evaluated top to bottom; use "Stop If True" to prevent overlapping

✅ Expected Result: Rows automatically highlighted based on performance: green for overachievers, red for underperformers

🔄 Connection to Previous Learning: More advanced than basic conditional formatting (Day 1), using formulas for complex logic

Exercise 2: Highlight Alternating Rows with Custom Patterns

Goal: Use formulas to create alternating row colors and highlight specific patterns like weekends or specific text.

Step-by-Step Instructions:

1. Create a calendar-style dataset:

   A1: Date        B1: Day        C1: Sales    D1: Notes
   A2: 1/1/2024    B2: Monday     C2: 5000     D2: Normal
   A3: 1/2/2024    B3: Tuesday    C3: 5200     D3: Normal
   A4: 1/3/2024    B4: Wednesday  C4: 4800     D4: Normal
   A5: 1/4/2024    B5: Thursday   C5: 5100     D5: Normal
   A6: 1/5/2024    B6: Friday     C6: 6200     D6: Peak
   A7: 1/6/2024    B7: Saturday   C7: 7500     D7: Weekend
   A8: 1/7/2024    B8: Sunday     C8: 7000     D8: Weekend

2. Highlight alternating rows: - Select A2:D20 - New Rule → Use formula: =MOD(ROW(),2)=0 - Format: Light gray fill - This highlights all even rows
3. Highlight weekends (Saturday/Sunday): - Select A2:D20 - New Rule → Use formula: =OR(B2="Saturday",B2="Sunday") - Format: Light blue fill
4. Highlight cells containing "Peak" in Notes column: - Select D2:D20 - New Rule → Use formula: =ISNUMBER(SEARCH("Peak",$D2)) - Format: Yellow fill with bold text
5. Manage rule priority: Go to Manage Rules and arrange so weekend rule is above alternating rows

💡 Tips:

• MOD(ROW(),2): Returns 0 for even rows, 1 for odd rows - perfect for alternating patterns
• SEARCH is case-insensitive: Use SEARCH for flexible text matching, FIND for exact case
• ISNUMBER(SEARCH()): Common pattern to check if text contains a substring
• Rule priority is crucial: More specific rules (weekends) should be above general rules (alternating)
• Use named ranges: For complex formulas, name your columns for better readability

✅ Expected Result: Professional-looking table with alternating rows, weekends highlighted distinctly, and special notes emphasized

🔄 Connection to Previous Learning: Combines text functions (Day 3) with conditional formatting for visual data organization

Exercise 3: Create Data Validation with Custom Dropdowns

Goal: Master data validation by creating dropdown lists with custom rules and error messages.

Step-by-Step Instructions:

1. Set up reference lists on a separate sheet (or off to the side):

   Sheet2 (or columns G:J):
   G1: Regions     H1: Categories    I1: Status      J1: Priority
   G2: East        H2: Electronics   I2: Pending     J2: High
   G3: West        H3: Furniture     I3: Approved    J3: Medium
   G4: North       H4: Clothing      I4: Completed   J4: Low
   G5: South       H5: Food          I5: Cancelled

2. Create dropdown for Region (B2:B20): - Select cells B2:B20 - Go to Data → Data Validation - Allow: List - Source: =Sheet2!$G$2:$G$5 (or =G2:G5 if on same sheet) - Input Message: "Select a region from the dropdown" - Error Alert: "Invalid region. Please select from the list." - Click OK
3. Create dropdown for Category (C2:C20): - Select C2:C20 - Data Validation → List - Source: =Sheet2!$H$2:$H$5 - Add custom input message and error alert
4. Create date validation (D2:D20): - Select D2:D20 - Data Validation → Allow: Date - Data: between - Start date: =TODAY() - End date: =TODAY()+365 - Error: "Date must be within the next year"
5. Create number validation for Sales Amount (E2:E20): - Select E2:E20 - Allow: Decimal - Data: between - Minimum: 0 - Maximum: 100000 - Error: "Sales amount must be between $0 and $100,000"

💡 Tips:

• Always use absolute references ($): For source lists so they don't shift
• Name your ranges: Name G2:G5 as "Regions" then use =Regions in validation
• Customize messages: Input messages guide users; error alerts prevent bad data
• Three error styles: Stop (blocks entry), Warning (allows override), Information (just notifies)
• Circle invalid data: Use Data → Data Validation → Circle Invalid Data to find problems

✅ Expected Result: Professional data entry form with dropdown selections and validation preventing incorrect data

🔄 Connection to Previous Learning: Enhances basic data entry (Day 1) with validation and user-friendly dropdowns

Exercise 4: Build Dependent Dropdowns (Cascading Lists)

Goal: Create dependent dropdown lists where the second dropdown options change based on the first dropdown selection.

Step-by-Step Instructions:

1. Set up your category and product data:

   Sheet2:
   A1: Electronics    B1: Furniture     C1: Clothing
   A2: Laptop         B2: Desk          C2: Shirt
   A3: Phone          B3: Chair         C3: Pants
   A4: Tablet         B4: Cabinet       C4: Jacket
   A5: Monitor        B5: Shelf         C5: Shoes

2. Create named ranges for each category: - Select A2:A5 → Name Box (top left) → Type "Electronics" → Enter - Select B2:B5 → Name as "Furniture" - Select C2:C5 → Name as "Clothing"
3. Create the Category dropdown (A2 on main sheet): - In main data sheet, cell A1: "Category", B1: "Product" - Select A2 - Data Validation → List - Source: Electronics,Furniture,Clothing - Click OK
4. Create the dependent Product dropdown (B2): - Select B2 - Data Validation → List - Source: =INDIRECT($A2) - Click OK
5. Test it: - Select "Electronics" in A2 → B2 dropdown shows: Laptop, Phone, Tablet, Monitor - Select "Furniture" in A2 → B2 dropdown changes to: Desk, Chair, Cabinet, Shelf
6. Copy validation down: Copy A2:B2 down to A3:B20 to create multiple entry rows

💡 Tips:

• INDIRECT is the key: It converts text to a reference, linking to named ranges dynamically
• Named ranges must match: Category values must exactly match named range names (case-sensitive)
• No spaces in names: Use underscores (North_America) or camelCase (NorthAmerica) instead
• Alternative method: Use tables and FILTER function for more dynamic approach
• Three-level cascading: Can extend to Region → Country → City using same technique

✅ Expected Result: Dynamic dropdown where product options automatically update based on category selection

🔄 Connection to Previous Learning: Uses INDIRECT function concept and builds on named ranges from earlier days

Exercise 5: Prevent Duplicates with Custom Validation

Goal: Create custom validation rules to prevent duplicate entries and ensure data uniqueness.

Step-by-Step Instructions:

1. Create a sample order entry form:

   A1: Order ID    B1: Customer    C1: Product     D1: Amount
   A2: 1001        B2: ABC Corp    C2: Laptop      D2: 1200
   A3: 1002        B3: XYZ Inc     C3: Phone       D3: 800
   (Leave A4:D10 empty for new entries)

2. Prevent duplicate Order IDs (A4:A20): - Select A4:A20 - Data Validation → Allow: Custom - Formula: =COUNTIF($A$2:$A$20,A4)<=1 - Error Alert: "This Order ID already exists. Please enter a unique ID." - Click OK
3. Test it: Try entering "1001" in A4 → Should get error message
4. Prevent duplicate Customer-Product combinations (entire row): - Select B4:B20 - Data Validation → Custom - Formula: =COUNTIFS($B$2:$B$20,B4,$C$2:$C$20,C4)<=1 - Error: "This customer has already ordered this product. Use a different combination."
5. Add email validation (if you have email column E): - Select E4:E20 - Data Validation → Custom - Formula: =AND(ISNUMBER(FIND("@",E4)),ISNUMBER(FIND(".",E4)),LEN(E4)>5) - Error: "Please enter a valid email address"
6. Highlight duplicates with conditional formatting: - Select A2:A20 - Conditional Formatting → New Rule → Formula - Formula: =COUNTIF($A$2:$A$20,A2)>1 - Format: Red fill - This visually highlights any duplicates that slip through

💡 Tips:

• COUNTIF for single column: Counts how many times value appears in range
• COUNTIFS for multiple columns: Checks combinations across multiple columns
• <=1 allows current cell: The formula counts the current cell as 1, so <=1 means no other duplicates
• Combine with formatting: Use both validation (prevention) and conditional formatting (detection)
• Validation isn't bulletproof: Users can paste data to bypass validation; use formatting as backup
• Clear validation: Data → Data Validation → Clear All to remove rules

✅ Expected Result: Robust data entry system that prevents duplicates and maintains data integrity

🔄 Connection to Previous Learning: Combines COUNTIF/COUNTIFS (Day 9) with data validation for data quality control