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

Download the file: Pivot tables and advanced filtering (XLSX)
Open the file in LibreOffice Calc
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:

Sort the data:
- Select columns A-F (including headers)
- Go to Data → Sort
- Sort by "Surname" (Column A)
Count episodes manually:
- For each surname, count how many rows appear
- Write this in a new column or separate sheet
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

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

Task 3B: Recreate Pivot Table 1B

Create another new pivot table
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

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

Challenge 4B: Detailed Session Analysis

Create another pivot table from Data 2
Configure:
- Rows: Subject
- Columns: Session Type
- Data: Count - Session Type
- Data: Count - Completed
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)