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

Locations

Grades

Designations

Nationality

Departments

Gender

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

VLOOKUP

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

Percentages

Location Wise

Actual Numbers

Percentages

Cost Center Wise Head Count

Actual Numbers

Percentages

Gender Score Card Head Count

Actual Numbers

Percentages

Nationality Score Card

Actual Numbers

Percentages

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