DAY 7: Excel - Pivot Tables & Charts

Guru Charan Pathalla

DAY 7: Excel - Pivot Tables & Charts

Learning Objectives: - Create and configure Pivot Tables - Use grouping and calculated fields - Create Pivot Charts - Add slicers for interactivity

Topics Covered (60 min):

1. Pivot Table Basics - Insert → PivotTable - Four areas: Rows, Columns, Values, Filters - Drag fields to appropriate areas - Values: Sum, Count, Average, Max, Min

2. Grouping - Date grouping: By day, month, quarter, year - Numeric grouping: Create ranges - Right-click → Group - Custom groups

3. Calculated Fields - Analyze → Fields, Items & Sets → Calculated Field - Create formulas: Profit Margin = Profit / Sales - Use existing field names in formulas

4. Slicers - Visual filters for easy interaction - Insert → Slicer - Connect to multiple Pivot Tables - Timelines for date filtering

5. Pivot Charts - Visualize Pivot Table data - Dynamic charts that update with Pivot Table - Insert → Pivot Chart

Video Resources:

  1. Excel Pivot Tables Tutorial (18:01)
    https://www.youtube.com/watch?v=Jx89DRlKe7E

  2. Pivot Table Excel | Step-by-Step Tutorial (8:25)
    https://www.youtube.com/watch?v=dvbLrwD2SpA

  3. Excel Pivot Tables Tutorial (27:59)
    https://www.youtube.com/watch?v=BWWaMqfPGGI

AI Learning Prompts (Concept Understanding Only):

Understanding Pivot Tables:

Explain Excel Pivot Tables:

1. What is a Pivot Table and why is it useful?
2. What are Rows, Columns, Values, and Filters areas?
3. How do I decide what goes in each area?
4. What's the difference between Count, Sum, Average in the Values area?
5. Show me examples using survey data (responses by age group, region, etc.)
6. How are Pivot Tables different from regular formulas?

Use survey or census data for examples (not sales).

Understanding Grouping:

Explain Pivot Table grouping:

1. What does grouping mean in Pivot Tables?
2. How do I group dates by month, quarter, or year?
3. How do I group numbers into ranges (0-10, 11-20, etc.)?
4. Show me examples using age groups or salary ranges
5. Can I create custom groups?

Use demographic or financial data for examples.

Understanding Calculated Fields:

Explain Pivot Table calculated fields:

1. What is a calculated field in a Pivot Table?
2. How is it different from calculated columns in my data?
3. How do I create formulas within Pivot Tables?
4. Show me examples: Profit Margin = Profit / Revenue
5. Can I use IF statements in calculated fields?

Use financial or performance data for examples.

Understanding Slicers:

Explain Pivot Table slicers:

1. What are slicers and why use them?
2. How do slicers differ from filters?
3. Can one slicer control multiple Pivot Tables?
4. Show me how to create an interactive dashboard with slicers
5. What are timelines and when to use them?

Use dashboard and reporting examples.

Guided Practice (60 min):

📝 How to approach Guided Practice:

  • Follow step-by-step: Work through each exercise in order
  • Use provided resources: Reference the video tutorials and AI prompts if you get stuck
  • Verify your work: Check your results match expected outcomes
  • Take notes: Document key learnings and shortcuts you discover
  • Ask for help: Use AI assistants to clarify concepts you don't understand

Using Superstore Sales dataset - Excel Practice

📝 Setup Instructions:

  1. Open your Superstore Sales dataset from Day 1
  2. Ensure you have columns: OrderDate, Region, Category, Product, Sales, Quantity, Profit
  3. Your data should be in a continuous range (no blank rows/columns)
  4. Save as "Day07_Practice.xlsx"

💡 What are Pivot Tables? Pivot Tables are Excel's most powerful data analysis tool. They let you quickly summarize, analyze, and explore large datasets by dragging and dropping fields. Think of them as interactive, dynamic summary reports!

Exercise 1: Create Your First Pivot Table - Sales by Region

Step-by-Step Instructions:

  1. Click any cell in your data range
  2. Go to Insert tab → Click PivotTable
  3. In the dialog:
    • "Table/Range" should auto-select your data
    • Choose "New Worksheet"
    • Click OK
  4. You'll see a blank Pivot Table with a Field List on the right
  5. Build the Pivot Table:
    • Drag "Region" to the Rows area
    • Drag "Sales" to the Values area
  6. You now see total sales by region!

💡 Understanding Pivot Table Areas:

  • Rows: Categories to group by (appears as row labels)
  • Columns: Additional grouping (appears as column headers)
  • Values: Numbers to calculate (SUM, COUNT, AVERAGE, etc.)
  • Filters: Data filters at the top of the pivot table

✅ Expected Result: A simple table showing 4 regions with their total sales

Exercise 2: Add Two Dimensions - Region AND Category

Step-by-Step Instructions:

  1. Using your existing Pivot Table from Exercise 1
  2. Drag "Category" to the Rows area (below Region)
  3. You now see sales broken down by both Region AND Category!
  4. Click the collapse/expand buttons (+ / -) next to regions to hide/show categories
  5. Try rearranging: Drag Category above Region in Rows - notice how the grouping changes!
  6. Add to Columns instead: Drag Category from Rows to Columns area - creates a matrix view!

💡 Pivot Table Flexibility:

  • Drag fields between areas to instantly reorganize data
  • No formulas needed - Excel recalculates automatically
  • Experiment with different layouts to find insights

✅ Expected Result: Sales broken down by Region and Category in various layouts

Exercise 3: Group Dates to Show Monthly Trends

Step-by-Step Instructions:

  1. Create a new Pivot Table (or clear existing one)
  2. Drag "OrderDate" to Rows
  3. Drag "Sales" to Values
  4. Group dates by month:
    • Right-click on any date in the Pivot Table
    • Select "Group"
    • In the dialog, select "Months" (and optionally "Years")
    • Click OK
  5. Now you see monthly sales totals!
  6. Try different groupings: Quarters, Years, or combinations

💡 Date Grouping Options:

  • Seconds, Minutes, Hours (for time data)
  • Days, Months, Quarters, Years
  • Can select multiple: Months + Years for multi-year analysis
  • Custom date ranges possible

✅ Expected Result: Sales summarized by month, showing trends over time

Exercise 4: Add Calculated Fields - Profit Margin %

Step-by-Step Instructions:

  1. Click anywhere in your Pivot Table
  2. Go to PivotTable Analyze tab (or PivotTable Tools) → Fields, Items, & Sets → Calculated Field
  3. In the dialog:
    • Name: "Profit Margin %"
    • Formula: =Profit/Sales*100
    • Click Add then OK
  4. The new calculated field appears in your Values area!
  5. Format as percentage:
    • Right-click the Profit Margin % values
    • Value Field Settings → Number Format → Percentage

💡 Calculated Fields:

  • Create custom calculations using existing fields
  • Formula applies to the entire Pivot Table
  • Updates automatically when data refreshes
  • Examples: Margins, growth rates, ratios

✅ Expected Result: Pivot Table showing both sales AND profit margin percentage

Exercise 5: Create a Pivot Chart for Visual Analysis

Step-by-Step Instructions:

  1. Create a simple Pivot Table: Region in Rows, Sales in Values
  2. Click anywhere in the Pivot Table
  3. Go to PivotTable Analyze tab → Click PivotChart
  4. Choose a chart type (Column chart recommended)
  5. Click OK
  6. You now have an interactive chart!
  7. Try this: Add "Category" to Rows in your Pivot Table - watch the chart update automatically!
  8. Filter the chart: Click the filter button on the chart to show only certain regions

🎯 Pivot Chart Benefits:

  • Automatically updates when Pivot Table changes
  • Interactive filters built-in
  • Perfect for presentations and dashboards
  • Drill down by clicking chart elements

✅ Expected Result: Interactive chart that visualizes your Pivot Table data

🎯 Practice Goal Checkpoint:

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

  • Create Pivot Tables from raw data
  • Drag fields to Rows, Columns, Values, and Filters
  • Group dates by months, quarters, or years
  • Add calculated fields for custom metrics
  • Create interactive Pivot Charts
  • Reorganize data instantly by dragging fields

Key Insight: Pivot Tables eliminate the need for complex formulas! Instead of writing hundreds of SUMIFs and formulas, you drag and drop to analyze data from any angle.

Common Use Cases:

  • Sales reports by region, product, time period
  • Inventory analysis
  • Budget vs. actual comparisons
  • Customer analysis and segmentation
  • Survey data analysis

💡 Pro Tips:

  • Right-click field buttons to access more options
  • Use slicers (Insert → Slicer) for better filtering
  • Refresh data: Right-click Pivot Table → Refresh
  • Format with Pivot Table Styles for professional look

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

Independent Practice (60 min):

💡 Hints for Independent Practice:

  • Start simple: Begin with basic variations before attempting complex modifications
  • Break it down: Divide complex problems into smaller, manageable steps
  • Test incrementally: Verify each step works before moving to the next
  • Use documentation: Refer to official docs or tutorials when needed
  • Experiment: Try different approaches to find what works best
  1. Sales by Product Category (Pivot Table)
  2. Quarterly sales comparison
  3. Top 10 customers by total revenue
  4. Sales by Region with % of Grand Total
  5. Product performance by Category
  6. Monthly sales trend with slicers for Region
  7. Multi-level grouping: Region → State → City
  8. Profit analysis by Category with Profit Margin %
  9. Year-over-year comparison (2022 vs 2023)
  10. Interactive dashboard: 3 Pivot Tables + Slicers

Daily Assignment (60 min):

Expected Outcomes:

✓ Create and configure Pivot Tables
✓ Use grouping and calculated fields
✓ Build interactive dashboards with slicers
✓ Submit: Day07_Excel_Practice.xlsx

📝 Day 7 Quick Quiz - Test Your Knowledge!

Q1. What is a Pivot Table used for?

Q2. Which Pivot Table area determines the summary calculation?

Q3. What happens when you drag a field to the Rows area?

Q4. Can you have multiple fields in the Values area?

Q5. What's the benefit of using Slicers with Pivot Tables?

💻 SQL Playground: Pivot Table Equivalents in SQL

Learn how Pivot Table operations translate to SQL queries! Below are interactive SQL playgrounds showing how to achieve Pivot Table-like results using GROUP BY and aggregations.

Exercise 1: Sales by Region (Basic Grouping)

Excel Pivot Table: Drag Region to Rows, Sales to Values

SQL Equivalent: GROUP BY with SUM aggregate

Exercise 2: Average Profit by Category

Excel Pivot Table: Category in Rows, Profit in Values (Average)

SQL Equivalent: GROUP BY with AVG aggregate

Exercise 3: Two-Dimensional Analysis (Region + Category)

Excel Pivot Table: Region AND Category in Rows, Sales in Values

SQL Equivalent: GROUP BY multiple columns

Exercise 4: Count of Orders by Segment

Excel Pivot Table: Segment in Rows, any field in Values (Count)

SQL Equivalent: GROUP BY with COUNT

Exercise 5: Multiple Metrics (Sales + Profit) with Sorting

Excel Pivot Table: Region in Rows, Sales + Profit in Values, Sorted by Sales

SQL Equivalent: GROUP BY with multiple aggregates and ORDER BY

Have feedback or questions?