DAY 3: Excel - Filtering & Conditional Functions

📝 Setup Instructions:

1. Open your Superstore Sales dataset in Excel (from Day 1)
2. Make sure you have columns: OrderID, Product, Category, Region, Sales, Quantity, Discount, Profit
3. Ensure row 1 has column headers
4. Save as "Day03_Practice.xlsx"

Exercise 1: Apply AutoFilter to Find Specific Data

Step-by-Step Instructions:

1. Click on any cell in your data (e.g., A1)
2. Go to Data tab → Click Filter button
3. Notice dropdown arrows appear in each column header
4. Filter by Region:
   • Click the dropdown arrow in the "Region" column
   • Uncheck "Select All"
   • Check only "West"
   • Click OK
5. You should now see only West region orders!
6. Add another filter: In the "Category" dropdown, select only "Technology"
7. Now you see only West region + Technology category orders
8. Clear filters: Data tab → Click "Clear" button

💡 Tips:

• Keyboard shortcut for Filter: Ctrl + Shift + L
• Filtered rows show blue row numbers
• Status bar shows "X of Y records found"
• You can filter multiple columns simultaneously

✅ Expected Result: Data filtered to show only rows matching your criteria

🔄 SQL Equivalent: See how filtering works in SQL!

Exercise 2: Use IF Function for Conditional Logic

Step-by-Step Instructions:

1. Add a new column header "Performance" in column J (or next empty column)
2. Click on cell J2 (first data row)
3. Type this formula: =IF(I2>0, "Profit", "Loss") (assuming Profit is in column I)
4. Press Enter
5. Copy the formula down to all rows (double-click the fill handle)
6. You should see "Profit" for positive values, "Loss" for negative

Now try a nested IF for 3 categories:

=IF(I2>500, "High Profit", IF(I2>0, "Low Profit", "Loss"))

🔍 Understanding the Formula:

• IF(condition, value_if_true, value_if_false)
• First checks: Is profit > 500? If yes → "High Profit"
• If no, checks: Is profit > 0? If yes → "Low Profit"
• If both false → "Loss"

💡 Tips:

• Always close your parentheses - Excel will help highlight matching pairs
• Use nested IFs when you have 2-3 conditions; use IFS() for more (Excel 2019+)
• Test with both TRUE and FALSE scenarios

✅ Expected Result: A new column with text labels based on profit values

🔄 SQL Equivalent: See how conditional logic works in SQL!

Exercise 3: COUNTIF - Count Cells Meeting a Condition

Step-by-Step Instructions:

1. Find an empty area in your spreadsheet (e.g., cell L2)
2. Type a label "Orders > $500:" in K2
3. In L2, type: =COUNTIF(D:D, ">500") (assuming Sales in column D)
4. Press Enter - this counts how many sales exceed $500

Try these variations:

• Count West region orders: =COUNTIF(C:C, "West") (if Region is column C)
• Count discounted orders: =COUNTIF(F:F, ">0") (if Discount is column F)
• Count specific products: =COUNTIF(B:B, "Staples") (if Product is column B)

💡 Tips:

• Criteria with operators (>, <, =) must be in quotes: ">100"
• Text criteria need quotes: "West"
• You can reference a cell: =COUNTIF(C:C, K5) counts values equal to K5

✅ Expected Result: A count of cells matching your condition

🔄 SQL Equivalent: See how counting with conditions works in SQL!

Exercise 4: SUMIF - Sum Values Meeting a Condition

Step-by-Step Instructions:

1. In cell L4, type label "Total West Sales:" in K4
2. Type this formula: =SUMIF(C:C, "West", D:D)
   • C:C = Range to check (Region column)
   • "West" = Condition to match
   • D:D = Range to sum (Sales column)
3. Press Enter - shows total sales for West region only

Try these examples:

• Sum profits where sales > 1000: =SUMIF(D:D, ">1000", I:I)
• Sum sales for Technology category: =SUMIF(E:E, "Technology", D:D)
• Sum quantity for discounted orders: =SUMIF(F:F, ">0", G:G)

💡 Tips:

• SUMIF syntax: =SUMIF(range_to_check, criteria, sum_range)
• If sum_range is omitted, it sums the range_to_check
• Use absolute references ($C$2:$C$100) if you plan to copy the formula

✅ Expected Result: Sum of values where condition is TRUE

🔄 SQL Equivalent: See how conditional summing works in SQL!

Exercise 5: AVERAGEIF - Average Values Meeting a Condition

Step-by-Step Instructions:

1. In cell L6, type label "Avg Profit (Profitable Orders):" in K6
2. Type formula: =AVERAGEIF(I:I, ">0", I:I)
   • This calculates average of only positive profits
   • First I:I = range to check
   • ">0" = condition (greater than zero)
   • Second I:I = range to average
3. Press Enter
4. Compare this to a simple =AVERAGE(I:I) - notice the difference!

More examples:

• Average sales for East region: =AVERAGEIF(C:C, "East", D:D)
• Average quantity for high-value orders: =AVERAGEIF(D:D, ">1000", G:G)

💡 Tips:

• AVERAGEIF ignores cells that don't meet the criteria (different from simple AVERAGE)
• Great for calculating averages excluding outliers or specific categories
• Format result as currency/number as appropriate

✅ Expected Result: Average of only the values meeting your condition

🔄 SQL Equivalent: See how conditional averaging works in SQL!

🎯 Practice Goal Checkpoint:

By completing these 5 exercises, you should now be able to:

• Use AutoFilter to quickly filter data by multiple criteria
• Write IF functions for conditional logic (including nested IFs)
• Use COUNTIF to count cells meeting specific conditions
• Use SUMIF to sum values based on criteria
• Use AVERAGEIF to calculate conditional averages
• Understand the difference between regular and conditional functions

Key Insight: Conditional functions let you analyze subsets of your data without actually filtering it visually!

Take a 5-minute break, then move on to Independent Practice!