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
9
4August 27, 2026 2.5K 7
