HRA
Module 3
Descriptive analytics in HR
• Key excel functions – VLOOKUP, INDEX, SUMIF, AVERAGEIF, COUNTIF,
etc. Creation of HR dashboards, storyboarding. Pivot table, charts and
tables, slicers. HR data visualization – Data mapping, Scatter chart, bar
charts, data merging and blending.
Data Management & Lookup
• VLOOKUP() – Searches for a value in a column and returns a
corresponding value from another column.
• HLOOKUP() – Similar to VLOOKUP but searches in rows instead of
columns.
• INDEX() – Returns the value of a cell within a specified range.
• MATCH() – Returns the relative position of a value in a row or column.
• XLOOKUP() – A modern replacement for VLOOKUP and HLOOKUP
(available in newer Excel versions).
Data Cleaning & Validation
• TRIM() – Removes extra spaces from text.
• CLEAN() – Removes non-printable characters from text.
• PROPER() – Capitalizes the first letter of each word.
• LOWER() / UPPER() – Converts text to lowercase or uppercase.
• LEN() – Counts the number of characters in a text string.
Attendance & Payroll
Calculations
• DATEDIF() – Calculates the difference between two dates (useful for
tenure calculation).
• NETWORKDAYS() – Calculates the number of working days between
two dates.
• TEXT() – Formats numbers and dates into specific formats.
• IF() – Performs logical tests (e.g., determining overtime eligibility).
• IFERROR() – Handles errors in formulas by returning a specified value.
• ROUND() / ROUNDUP() / ROUNDDOWN() – Rounds numbers to the
nearest whole number or decimal place.
Performance & Employee
Analysis
• COUNT() / COUNTA() – Counts the number of numerical or non-
empty cells.
• COUNTIF() / COUNTIFS() – Counts the number of cells meeting one or
multiple conditions (e.g., how many employees have exceeded their
targets).
• SUM() / SUMIF() / SUMIFS() – Sums values based on one or multiple
conditions (e.g., total salary of employees in a specific department).
• AVERAGE() / AVERAGEIF() / AVERAGEIFS() – Calculates the average
based on conditions (e.g., average performance rating in a
department)
HR Dashboards & Reports
• CONCATENATE() / CONCAT() – Joins multiple text strings into one
(useful for merging employee names and IDs).
• TEXTJOIN() – Joins multiple text strings with a delimiter.
• LEFT() / RIGHT() / MID() – Extracts parts of a text string (useful for
extracting employee ID prefixes).
• NOW() / TODAY() – Returns the current date and time.
HR Metrics & Forecasting
• TREND() – Predicts future values based on historical data.
• FORECAST() – Estimates future values based on existing trends.
• RANK() – Determines the rank of a value within a list (useful for
performance ranking).
• PIVOT TABLES – An essential tool for summarizing and analyzing large
datasets in HR.
Commonly used HR data
visualization
•Employee Demographics – Pie charts or bar graphs showing age, gender, and diversity metrics.
•Recruitment Analytics – Funnel charts for candidate pipelines, time-to-hire trends.
•Employee Performance – Heatmaps for skill assessments, performance scores.
•Turnover & Retention – Line graphs showing retention trends, reasons for attrition.
•Compensation & Benefits – Comparative bar charts for salary distribution.
•Workforce Productivity – Dashboards tracking attendance, project completion rates.
•Engagement & Satisfaction – Sentiment analysis with word clouds or survey scores.