📊 Day 10: Excel Dashboard Mastery

🎓 Ghaziabad, Mohan Nagar

Drafted by: HR Prateek Sharma

DAY 10

📌 Overview

Day 10 focuses on creating professional HR Analytics dashboards using Microsoft Excel. Master pivot tables, pivot charts, slicers, and KPI design to transform raw HR data into actionable insights without any coding required.

🎯 Key Learning Objectives

• Data Understanding: Analyze HR dataset structure and quality
• Pivot Tables: Create dynamic data summaries and calculations
• Pivot Charts: Visualize data with multiple chart types
• Slicers & Filters: Enable interactive filtering for stakeholders
• Dashboard Assembly: Combine visuals into cohesive dashboard
• KPI Design: Display key metrics prominently

🔄 Complete Dashboard Creation Workflow

📊

Step 1

Understand Data

Analyze columns, data types, completeness

🎯

Step 2

Define Objective

Identify key questions to answer

🔧

Step 3

Create Pivot Tables

Summarize and aggregate data

📈

Step 4

Build Pivot Charts

Visualize insights effectively

🎛️

Step 5

Add Slicers

Enable interactive filtering

📤

Step 6

Share Dashboard

Export, protect, and distribute

📊 Step 1: Understanding Your Data

⚠️ Critical First Step: Never start building without understanding your data structure!

Data Quality Checklist:

• ✅ Complete Records: Check for blank cells, missing values
• ✅ Consistent Formatting: Dates in same format, numbers not stored as text
• ✅ No Duplicates: Unique Employee IDs
• ✅ Clean Headers: Single row headers without spaces or special characters
• ✅ Proper Data Types: Numbers as numbers, dates as dates

Indian HR Dataset Example:

Column Name	Data Type	Purpose
EmployeeID	Number/Text	Unique identifier
Name	Text	Employee name
Department	Text	HR, IT, Sales, Finance
MonthlyIncome	Number (₹)	Salary analysis
Attrition	Text (Yes/No)	Turnover tracking
City	Text	Location analysis

🎯 Step 2: Define Dashboard Objectives

Key Questions Your Dashboard Should Answer:

• 📊 Headcount: How many employees do we have?
• 📉 Attrition: What's our turnover rate? Which departments are affected?
• 💰 Compensation: What's the average salary? Any disparities?
• 👥 Demographics: Age distribution, gender ratio, education levels
• 📍 Location: Employee distribution across cities
• ⏱️ Tenure: How long do employees stay?
• ⭐ Satisfaction: Job satisfaction and work-life balance scores

📋 Essential KPIs for HR Dashboard

👥 Total Employee Count

=COUNTA(A2:A101)

Purpose: Current headcount

Indian Context: Track across multiple offices (Mumbai, Delhi, Bangalore)

🚪 Attrition Count

=COUNTIF(Attrition_Column,"Yes")

Purpose: Number of employees who left

Benchmark: Compare with industry average (IT: 15-20%)

📉 Attrition Rate (%)

=(Attrition Count / Total Employees) * 100

Purpose: Percentage of workforce turnover

Format: Display as percentage with 1 decimal place

💰 Average Salary

=AVERAGE(MonthlyIncome_Column)

Purpose: Mean compensation across organization

Indian Format: ₹85,000 per month or ₹10.2 LPA

🎂 Average Age

=AVERAGE(Age_Column)

Purpose: Workforce age demographics

Insight: Younger workforce may need different benefits

⏱️ Average Experience

=AVERAGE(YearsAtCompany_Column)

Purpose: Employee tenure tracking

Benchmark: Indian IT average: 3-4 years

⚖️ Gender Ratio

=COUNTIF(Gender,"Male")/COUNTIF(Gender,"Female")

Purpose: Diversity tracking

Target: Many Indian companies aiming for 50:50

⭐ Average Job Satisfaction

=AVERAGE(JobSatisfaction_Column)

Purpose: Employee happiness metric

Scale: Typically 1-5 (5 being highest)

🔧 Step 3: Creating Pivot Tables

1 Select Your Data: Click any cell in your dataset → Go to Insert → PivotTable

2 Choose Location: Select "New Worksheet" for better organization

3 Configure Fields: Drag and drop fields to appropriate areas

Common Pivot Table Configurations:

Configuration 1: Department-wise Headcount

• Rows: Department
• Values: Count of EmployeeID
• Insight: Which department has most employees?

Configuration 2: Attrition by Department

• Rows: Department
• Columns: Attrition (Yes/No)
• Values: Count of EmployeeID
• Insight: Which departments have highest turnover?

Configuration 3: City-wise Average Salary

• Rows: City
• Values: Average of MonthlyIncome
• Insight: Cost of living salary adjustments

Configuration 4: Age Group Distribution

• Rows: Age (Grouped by 10 years: 20-30, 31-40, 41-50, 51-60)
• Values: Count of EmployeeID
• Right-click Age → Group → Set intervals
• Insight: Workforce generation mix

Configuration 5: Job Role vs Average Satisfaction

• Rows: JobRole
• Values: Average of JobSatisfaction
• Insight: Which roles are most/least satisfied?

💡 Pro Tip: Name your pivot tables descriptively (e.g., "PT_Dept_Attrition") for easy reference when creating charts

📈 Step 4: Creating Pivot Charts

Chart Type Selection Guide:

📊 Bar/Column Charts - Use for:

• Department-wise headcount comparison
• City-wise employee distribution
• Job role frequency

Excel Path: Select Pivot Table → PivotTable Analyze → PivotChart → Choose Column

🥧 Pie/Doughnut Charts - Use for:

• Education field distribution
• Marital status breakdown
• Gender ratio visualization

Best Practice: Limit to 5-6 categories maximum

📉 Line Charts - Use for:

• Attrition trends over time (by joining month/year)
• Hiring trends
• Salary growth progression

Requirement: Need time-based data

🔥 Combo Charts - Use for:

• Department headcount (bars) + Attrition rate (line)
• Hiring count (bars) + Cost per hire (line)

Excel Path: Insert → Combo Chart → Custom Combination

Real Example: TCS Bangalore Dashboard

• Chart 1: Stacked Column - Attrition vs Active employees by Department
• Chart 2: Pie Chart - Employee distribution across 5 cities
• Chart 3: Line Chart - Monthly attrition trend (Jan-Dec 2024)
• Chart 4: Bar Chart - Average salary by job role
• Chart 5: Doughnut - Education qualification split

🎛️ Step 5: Adding Slicers for Interactivity

🎯 What are Slicers? Visual filter buttons that allow users to filter dashboard data with a single click

How to Add Slicers:

1 Click on Pivot Table → PivotTable Analyze → Insert Slicer

2 Select Fields: Choose Department, City, Gender, Education, Attrition

3 Connect to Multiple Pivot Tables: Right-click slicer → Report Connections → Select all relevant pivot tables

4 Format Slicers: Slicer Tools → Options → Choose columns, style, and size

Recommended Slicers for HR Dashboard:

• 🏢 Department: Filter by HR, IT, Sales, Finance, Marketing, Operations
• 📍 City: Mumbai, Delhi, Bangalore, Hyderabad, Chennai, Pune, Kolkata
• ⚖️ Gender: Male, Female
• 🎓 Education: Bachelors, Masters, PhD
• 📊 Job Role: Manager, Executive, Analyst, Senior Manager, Intern
• 🚪 Attrition Status: Yes, No (Active vs Left)

Use Case Example:

Scenario: HR Director wants to see IT Department data only

Action: Click "IT" in Department slicer

Result: All charts and KPIs update to show only IT department metrics

Benefit: No need to create separate dashboards for each department!

🎨 Dashboard Assembly Best Practices

Layout Principles:

• 📏 Top Section: Place KPI cards prominently (first thing users see)
• 🎛️ Left Sidebar: Position slicers for easy access
• 📊 Main Area: Arrange charts in logical groups (2x2 or 3x2 grid)
• 📋 Bottom Section: Include data table or notes if needed

Visual Design Tips:

• 🎨 Consistent Colors: Use company brand colors (e.g., TCS - Blue, Infosys - Blue/Orange)
• 📐 Alignment: Use Excel's Align tools to line up charts perfectly
• 🔤 Font Consistency: Same font family and sizes across all elements
• ⬜ White Space: Don't overcrowd - leave breathing room
• 🖼️ Borders: Add subtle borders to separate sections

💡 Excel Tip: Use View → Gridlines (uncheck) to remove gridlines for cleaner look

💡 Insights & Outcomes

What Insights Can You Derive?

Insight 1: High Attrition in Specific Department

Finding: Sales department has 25% attrition vs company average of 12%

Action