📊 Day 6: Pivot Tables & Lookup Functions

Data Summarization & Analysis Mastery

Ghaziabad, Mohan Nagar

Drafted by: HR Prateek Sharma

🎯 Today's Learning Objectives

Master lookup functions, pivot tables, and advanced data analysis techniques

🔍 Lookup Functions

VLOOKUP

HLOOKUP

INDEX

MATCH

💡 These functions help you search and retrieve data from tables efficiently

📈 Pivot Tables - The Power Tool

What is a Pivot Table?

A powerful tool to summarize complex data into simpler, more understandable formats. It allows you to group data and perform aggregation calculations.

📊 Available Calculations

SUM

MIN

MAX

COUNT

AVERAGE

STD DEV

VARIANCE

⚡ Pivot Table vs IF Functions

✓ All IF Functions: COUNTIF, MINIFS, SUMIFS, AVGIFS, etc. - Require formulas
✓ Pivot Table: NO formulas needed! Drag and drop interface for instant analysis

🔧 Creating a Pivot Table

Step 1: Select complete table using Ctrl + A

Step 2: Go to Insert tab and select Pivot Table

Step 3: Configure using the 4 main components below

🎯 Four Key Components

📋
ROWS

📊
COLUMNS

🔢
VALUES

🔍
FILTERS

❓ Practice Questions & Solutions

HR Analytics Problems

Q1 Department-wise Employee Count

💡 Rows: Department | Values: Count(Employee_ID)

Q2 Department-wise Attrition Count or %

💡 Rows: Department | Values: Count(Attrition) | Filter: Attrition = "Yes"

Q3 Department-wise Average Salary

💡 Rows: Department | Values: Average(Monthly_Income)

Q4 Education-wise Employee Count

💡 Rows: Education_Field | Values: Count(Emp_ID)

Q5 City-wise Attrition and Employee Count

💡 Rows: City | Values: Count(Emp_ID), Count(Attrition) | Filter: Attrition = "Yes"

Q6 Gender-wise Attrition

💡 Rows: Gender | Values: Count(Attrition) | Filter: Attrition = "Yes"

Q7 Job Role-wise Attrition

💡 Rows: Job_Role | Values: Count(Attrition) | Filter: Attrition = "Yes"

Q8 Department-wise Average Job Satisfaction

💡 Rows: Department | Values: Average(Job_Satisfaction)

Q9 Department-wise Marital Status

💡 Rows: Department | Columns: Marital_Status | Values: Count(Emp_ID)

Q10 Department-wise Minimum Age of Employee

💡 Rows: Department | Values: Min(Age)

🎓 Master these questions to become proficient in HR data analysis!

📝 Additional Topics

• Q&A Session: Addressing doubts and clarifications
• Notes Making: Documenting key learnings and insights
• Practice: Hands-on exercises with real HR datasets

Ghaziabad, Mohan Nagar

Design & Content by HR Prateek Sharma

Day 6 - Pivot Tables & Lookup Functions Training

Data Summarization • Advanced Analysis • HR Insights