Excel Tutorial Series: Pivot Tables

Introduction

• The series will cover numerous Excel topics, both basic and advanced.
• Focus on practical uses since the presenter became a data analyst.
• Topics will include:
  • Basic tools: pivot tables, charts, VLOOKUPs
  • Advanced topics: handling missing or dirty data
• Shout-out to Udemy as the sponsor.
  • Presenter used several courses from Udemy as a beginner.
  • Links to specific courses will be provided.

Pivot Tables

• Importance: Most commonly used by data analysts to convey information.
• Use Cases: Useful for non-technical audiences like managers or higher-ups.
• Dataset: 'Bike store sales in Europe' provided for practice.

Dataset Overview

• Columns: Date, Day, Month, Year, Customer Age, Age Group, Gender, Country, State, Product Category, Subcategory, Product, Order Quantity, Unit Cost, Unit Price, Profit, Cost, Revenue
• Practical focus on columns like profit, cost, revenue, and geographical information

Creating a Pivot Table

Step-by-Step Guide

1. Download and open the dataset.
2. Go to Insert > Pivot Table.
3. Auto-select the range or use Ctrl + Shift + Right/Down Arrow to select data.
4. Choose to create a new worksheet.
5. Tables fields appear on the right side.
6. Drag & drop fields to Filters, Columns, Rows, and Values areas.

Example with Bike Sales Data

• Country & State:
  • Drag Country to Rows.
  • Drag State under Country in Rows.
  • Collapse all fields for simplicity.

Adding Values (Revenue)

• Drag Revenue to Values.
• Format to currency for better readability.
• Sort by values for better insight.
• Expand each country to see state-wise breakdown.

Advanced Features

Value Field Settings & Calculated Field

• Value Field Settings:
  • Customize field name and summary function (e.g., sum, count).
  • Example: Percentage of grand total to see contribution.
• Calculated Fields:
  • Go to Pivot Table Analyze > Fields, Items, & Sets > Calculated Field.
  • Example: Create Calculated Field Demo to verify profit by subtracting cost from revenue.
  • Demonstration of formula application.

Filters

• Example: Filter by a field not in the table (e.g., Gender).
• Quick comparison between filtered data.

Additional Pivot Table

Year-wise Performance

• Add a new pivot table for annual analysis.
• Use Country in Rows and Year in Columns.
• Add Revenue to Values.
• Format to currency.
• Analysis example: See year-over-year performance for each country.

Conclusion

• Pivot tables allow for grouping and summarizing data easily.
• Useful for making data understandable to non-technical audiences.
• Encouragement to explore and use pivot tables for analysis.

Closing: Thanks and encouragement to like and subscribe for more tutorials.