Add slicer for Region → Clickable North/South/East/West → PivotTable updates. Easier for non-technical users.

1️⃣7️⃣ Multiple Slicers

Add Region, Category, Year slicers → User selects Region: North, Category: Electronics, Year: 2026 → Shows only relevant info. Foundation of interactive dashboard.

1️⃣8️⃣ PivotCharts

A chart connected to a PivotTable.

📈 Line Chart for sales by month,

📊 Column Chart for sales by region.

Automatically responds to filters and slicers.

1️⃣9️⃣ Choosing the Right Chart

Compare categories → Bar/Column Chart

Show trends over time → Line Chart

Show contribution → Bar or Pie/Donut for small categories

Analyze relationships → Scatter Plot

2️⃣0️⃣ Drill Down

Year → Quarter → Month → Day. Move from high-level view to detailed view.

2️⃣1️⃣ Drill Through to Source Data

Double-click a value to see underlying records contributing to that value. Useful for investigating unexpected numbers.

2️⃣2️⃣ Refreshing PivotTables

PivotTables don't auto-update. Right-click → Refresh or Data → Refresh All. Using Excel Table as source makes refresh easier.

2️⃣3️⃣ PivotTable Best Practice

Source data should have:

✅ Headers

✅ No blank rows

✅ Consistent data types

✅ One record per row

✅ One field per column

✅ No manually inserted totals.

🧪 Practical Interview Challenge

Q1. Total sales by region → Region → Rows, Sales → Values

Q2. Average profit by category → Category → Rows, Profit → Values → Average

Q3. Monthly sales trend → Order Date → Rows, Sales → Values, Group by Months

Q4. Top 10 products by sales → Product → Rows, Sales → Values, Value Filters → Top 10

Q5. Interactive regional report → PivotTable + PivotChart + Region Slicer

🎯 Mini Project: Build an Excel Sales Analysis Dashboard

KPIs: Total Sales, Total Profit, Total Orders, Average Order Value

Analysis:

📊 Sales by Region,

📈 Monthly Trend,

📊 Sales by Category,

🏆 Top 10 Products,

📊 Profit by Region

Interactive Controls: Slicers for Region, Category, Year

Double Tap ❤️ For Part-10