g8-m3/README.md

Pivot Tables Activities: Step-by-Step Guide

📋 Overview

This guide will walk you through completing the pivot table activities using LibreOffice Calc. You'll learn how to create pivot tables, analyze data, and troubleshoot common issues.

🎯 Activities Checklist

• Activity 1: Manual Data Analysis Challenge (10 minutes)
• Activity 2: Create Your First Pivot Table
• Activity 3: Recreate Example Pivot Tables (1C and 1B)
• Activity 4: Advanced Analysis with Data Set 2

🚀 Getting Started

Step 1: Download and Open the Spreadsheet

1. Download the file: Pivot tables and advanced filtering (XLSX)
2. Open the file in LibreOffice Calc
3. Make sure you're using LibreOffice version 6.0 or higher

📊 Activity 1: The Manual Challenge (Learning Hook)

What to Do:

• Go to the Data 1 worksheet tab
• DO NOT USE PIVOT TABLES for this activity
• Manually calculate for each actor:
  • How many episodes they appeared in
  • Their total earnings

Step-by-Step:

1. Sort the data:

   • Select columns A-F (including headers)
   • Go to Data → Sort
   • Sort by "Surname" (Column A)

2. Count episodes manually:

   • For each surname, count how many rows appear
   • Write this in a new column or separate sheet

3. Sum earnings manually:

   • For each actor, add up all values in the "Earnings" column
   • Double-check your calculations

⏱️ Time: 10 minutes

Reflection Questions:

• How many potential errors did you make?
• How tedious was this process?
• How would this scale with 1,000 rows of data?

🔧 Activity 2: Create Your First Pivot Table

Step-by-Step Instructions:

Step 1: Select Your Data

• Go to Data 1 sheet
• Select all data including headers (cells A1:F31)
• Click cell A1
• Hold Shift and click cell F31
• Or press Ctrl+A to select all

Step 2: Open Pivot Table Wizard

• Go to Data → Pivot Table → Create
• Select Current selection
• Click OK

Step 3: Configure Your Pivot Table

• Drag and drop fields in the layout area:

  • Drag "Surname" to ROW FIELDS
  • Drag "Episode" to DATA FIELDS
  • Drag "Earnings" to DATA FIELDS

• Configure calculations:

  • For "Episode": Double-click it → Select Count
  • For "Earnings": Double-click it → Select Sum

• Final layout should show:

  Rows: Surname
  Data: Count - Episode
  Data: Sum - Earnings
  

Step 4: Place the Pivot Table

• Choose New worksheet
• Click OK
• Your pivot table will appear on a new sheet

Step 5: Verify Results

• Compare your pivot table results with the example in Pivot table 1A

📈 Activity 3: Recreate Example Pivot Tables

Task 3A: Recreate Pivot Table 1C

1. Start fresh from Data 1
2. Create a new pivot table
3. Try this configuration:
   • Rows: First Name
   • Columns: Role
   • Data: Count - Episode
   • Data: Average - Earnings

Task 3B: Recreate Pivot Table 1B

1. Create another new pivot table
2. Try this configuration:
   • Rows: Role
   • Columns: Season
   • Data: Sum - Earnings
   • Filter: Surname (optional)

💡 Tips:

• Right-click on any cell in the pivot table → Refresh to update
• Double-click any field in the layout to change its calculation
• Drag fields between areas to experiment

🎓 Activity 4: Advanced Analysis (Data Set 2)

Step 1: Navigate to Data Set 2

• Go to Data 2 worksheet
• Familiarize yourself with the columns:
  • Subject, Session Type, Completed, etc.

Challenge 4A: Subject Completion Analysis

1. Select all data in Data 2
2. Create a new pivot table
3. Configure:
   • Rows: Subject
   • Columns: Completed (Yes/No)
   • Data: Count - Completed

Challenge 4B: Detailed Session Analysis

1. Create another pivot table from Data 2
2. Configure:
   • Rows: Subject
   • Columns: Session Type
   • Data: Count - Session Type
   • Data: Count - Completed
3. Hint: You might need to add "Session Type" twice to get both calculations

⚠️ Troubleshooting: Cells Not Auto-Recalculating

Problem: Pivot table doesn't update when source data changes

Solution 1: Manual Refresh

• Right-click anywhere in the pivot table
• Select Refresh or Recalculate Pivot Table
• Or use shortcut: F9 or Shift+F9

Solution 2: Check Calculation Settings

• Go to Tools → Options → LibreOffice Calc → Formula
• Ensure AutoCalculate is checked
• Under "Formula options", make sure:
  • ☑ Automatically find dependencies
  • ☑ Use OpenCL for formula calculation (if available)

Solution 3: Force Complete Recalculation

• Press Ctrl+Shift+F9 - This forces a complete recalculation of all formulas

Solution 4: Check Data Range

• Right-click the pivot table → Edit Layout
• Verify the Source data range is correct
• If you added new rows/columns to your source data:
  • Edit the data range to include them
  • Or recreate the pivot table with the new range

Solution 5: Pivot Table Corruption

• If nothing works:
  • Copy your source data to a new sheet
  • Create a brand new pivot table
  • Reconfigure it from scratch

💡 Pro Tips for Success

Tip 1: Naming Your Pivot Tables

• Right-click the sheet tab
• Rename it meaningfully:
  • "Pivot_Subject_Analysis"
  • "Pivot_Earnings_Summary"

Tip 2: Formatting for Readability

• Select the pivot table
• Use Format → AutoFormat for quick styling
• Adjust number formats:
  • Right-click currency values → Format Cells → Currency
  • Right-click counts → Format Cells → Number

Tip 3: Saving Your Work

• Save frequently as .ods (LibreOffice format)
• If sharing with Excel users, also Save As → .xlsx
• Consider saving pivot tables as separate files for different analyses

Tip 4: Documenting Your Process

• Create a "Notes" sheet in your workbook
• Document:
  • What each pivot table shows
  • Why you chose that configuration
  • Any insights discovered

🔍 Checking Your Work

Compare your results with the example sheets:

• Pivot table 1A: Basic actor summary
• Pivot table 1B: Alternative view
• Pivot table 1C: Different arrangement
• Pivot table 2A: Subject completion analysis
• Pivot table 2B: Detailed session analysis

Note: Your teacher may hide the example sheets. If they're hidden, focus on whether your pivot tables provide meaningful insights.

🆘 Getting Help

Common Issues and Solutions:

Problem	Solution
"Field not found" error	Check column names match exactly
Numbers showing as text	Use Data → Text to Columns on source data
Pivot table looks empty	Check data range includes all rows/columns
Can't drag fields	Click "More" button in Pivot Table dialog

When to Ask for Help:

• After trying all troubleshooting steps
• When you get consistent error messages
• If your results are significantly different from expected
• When you're spending more than 10 minutes stuck

📚 Learning Reflection

After completing all activities, ask yourself:

• What was the most valuable insight you gained from pivot tables?
• How much time did pivot tables save compared to manual analysis?
• What real-world data would you like to analyze with pivot tables?
• What configuration was most challenging to create?

✅ Completion Checklist

• Activity 1: Completed manual analysis (10 min challenge)
• Activity 2: Created basic pivot table from Data 1
• Activity 3: Recreated example pivot tables 1C and 1B
• Activity 4: Created pivot tables for Data Set 2 (Challenges A & B)
• Verified calculations match examples (or provide meaningful insights)
• Saved work in both .ods and .xlsx formats
• Documented process and insights in notes

Remember: Pivot tables are a skill that improves with practice. The more you experiment with different configurations, the more powerful insights you'll discover in your data!

---

Last Updated: [Date]
LibreOffice Version: 7.0+ recommended
File Format: .ods (LibreOffice) / .xlsx (Excel compatible)