Excel for HRD Professionals

Data of an organization’s most important asset – PEOPLE – is managed by HR Professionals. From Recruitment to Retirement, the information is stored, retrieved, used, and analyzed. This “domain-focused” course introduces various techniques of Excel as used to manage information about Employees by Human Resource Professionals. It uses Intermediate to Advanced level skills in Excel. Delegates will learn to modify worksheets to meet the needs of your Organization or HR Department. It teaches you several concepts, tricks, and techniques like VLOOKUPS, pivot tables, subtotals, functions, shortcuts, data outlining, charts and numerous other powerful tools to audit data from the various systems housing your HR data.

The course does not use Excel VBA and is totally non-technical.

HR Data

HR Data Basics

Formatting HR Data

Organization Data

Employee Data

Working with Organization Data

Creating Masters

Employee status







Cost Center

Creating Named Ranges for Organization’s “Master” Information

Organizational Hierarchy Sorting with Custom Lists

Sorting on Designation getting “General Manager” Designation at top


Working with Employee Information

Types of Employees Information’s

Text (Name, Dependent Name, Education, Previous Employers)

Dates (Birthdates, Hire date)

Numbers (Salary, Benefits)

Numbers but not Numbers (Employee ID, Passport Number, Telephone Number, Other ID Number)

Web Enablers – Email-ID, SharePoint Home Page Formatting Columns for Various Types of Employee Information Getting Full Name in one column from First Name, Middle Name and Last Name in three columns using CONCATENATE Function Using Organization’s Master Information through:

Data Validation


Listing Reports with Custom Views

Dependent Wise Employee List

Location Wise Employee List

Telephone & Email Report

Cost Center wise employee list

Listing reports with Pivot Tables

Birthdays This Month Report

Joiners Report

Leavers Report

Consolidation reports with Pivot Tables

Department wise Head Count

Actual Numbers


Location Wise

Actual Numbers


Cost Center Wise Head Count

Actual Numbers


Gender Score Card Head Count

Actual Numbers


Nationality Score Card

Actual Numbers


Head Count Growth Report

Actual Numbers

Year on Year Growth

   Making Reports Dynamic With OFFSET and COUNTA Functions


Time and Attendance Data

Attendance Records

Standard Full day Attendance Records

Flexi Time Shift Record

Overnight Shift Record (Using IF Function)

Biometric Attendance Date

Over Time Calculations

Working With Project Time Sheet Using

Date Functions

Time Functions

IF Functions

Attendance Reports

Late Comers Report


Leave Records & Calculations

Is the employee eligible for Leave. IF Function

Leave Accrued

Leave Taken Records

Leave Balance

Leave Reports


Payroll and Benefits

Payroll sheet and Salary Slips

Using Attendance Data to Calculate Payroll

Calculating Air Ticket Allowance based on various policy criteria


End of Service Calculations

Compute Gratuity

Compute En cashable Leave


Macros for Standard HR Reports Format

Record a Macro

Modify a Macro

Run a Macro


Conditional Formatting

Avoid Duplication of Employee

Set Warnings on Passport Expiry Date


HR Charts and Graphs

Gender Score Card Using Pie Charts

Nationality Diversity Score Card Using Pie Charts


Comprehensive Exercises

Calculating Gratuity Liability for all Employees as on Date

Salary Increment Calculator using Performance Review Scores

Man Power Planning Module