DAY 28: Excel - Executive Dashboard Creation

Guru Charan Pathalla

DAY 28: Excel - Executive Dashboard Creation

Learning Objectives: - Design professional executive dashboards - Create KPI scorecards - Build dynamic visualizations - Apply advanced formatting - Tell stories with data

Topics Covered (60 min):

1. Dashboard Planning - Identify key metrics (KPIs) - Understand audience and purpose - Sketch layout before building - Data requirements and sources - Update frequency

2. KPI Design - Card-style metrics - Comparison to target (arrows, colors) - Sparklines for trends - Variance analysis (actual vs budget) - Year-over-year comparisons

3. Advanced Visualizations - Combination charts (targets + actuals) - Heat maps with conditional formatting - Bullet charts for performance - Small multiples for comparison - Geographical maps

4. Design Best Practices - Consistent color scheme - Minimize chart junk - Use white space effectively - Proper font hierarchy - Mobile/print friendly - Interactivity with slicers

Video Resources:

  1. Mastering Moving Averages in SQL: A Comprehensive Guide (3:05)
    https://www.youtube.com/watch?v=Yyv-t9mo-WM

  2. Moving & Rolling Average | SQL Key Data Analysis Concepts (2:44)
    https://www.youtube.com/watch?v=GzRyOsQsugk

  3. Calculating MOVING AVERAGE using WINDOW function (2:20)
    https://www.youtube.com/watch?v=wlvyawjOrkI

AI Learning Prompts (Concept Understanding Only):

Understanding Dashboard Design:

I'm learning executive dashboard design. Please explain:

1. What makes an effective executive dashboard?
2. How do I choose which KPIs to display?
3. Show me how to design KPI cards with variance indicators
4. What chart types work best for different data?
5. Provide examples of good vs bad dashboard designs

Use design principles and Excel techniques.

Understanding Data Storytelling:

Help me tell stories with data:

1. How do I guide the viewer through insights?
2. What is the proper hierarchy of information?
3. Show me how to use color effectively
4. How do I balance detail vs simplicity?
5. Provide examples of dashboards that tell clear stories

Explain design thinking for dashboards.

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

Exercise 1-5: Complete Executive Dashboard Creation

Goal: Build a professional executive dashboard combining all skills learned throughout the program.

Combined Comprehensive Approach:

  1. Set up data foundation: Import sales data using Power Query, create Power Pivot data model with relationships
  2. Design KPI Scorecard: Create 8-10 KPIs (Total Revenue, YoY Growth, Customer Count, Avg Order Value, etc.) with trend indicators
  3. Build visualizations: Add 5+ professional charts (Sales Trend, Regional Performance, Top Products, Category Mix, Monthly Targets vs Actuals)
  4. Apply professional formatting: Use consistent colors, remove gridlines, add slicers for Date/Region/Product, create print-ready layout
  5. Add interactivity: Connect all visualizations to slicers, add dynamic titles that update with filters, create drill-down capabilities

💡 Key Tips:

  • Design for executives: Clear, concise, actionable insights at a glance
  • Use white space: Don't overcrowd the dashboard
  • Consistent formatting: Same fonts, colors, sizing throughout
  • Tell a story: Arrange visualizations logically (overview → details)
  • Test with real users: Get feedback and iterate

✅ Expected Result: Professional executive dashboard showcasing mastery of Excel visualization and reporting skills

🔄 Connection to Previous Learning: Culmination of all Excel skills from Days 1-27

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

Apply the concepts learned to different datasets and scenarios.

Daily Assignment (60 min):

Assignment:

Expected Outcomes:

📝 Day 28 Quick Quiz - Test Your Knowledge!

Q1. As you near the end of the program, what does Day 28 emphasize?

Q2. How should you approach Day 28 exercises?

Q3. What makes Day 28 content valuable for your career?

Q4. By Day 28, what should you be comfortable with?

Q5. What's the best mindset for Day 28?