Course Overview

HR Professionals will learn the tips that save hours to resolve the difficulties they face on a daily basis in doing employee compensations, salary, leave, benefits etc. In this practical training, participants will learn formatting techniques to convert data from different sources, how to make the best use of most important formulas for HR. You will able to manipulate charts and diagrams, use logical functions, and link their spreadsheet to other worksheets and applications. Also learn how to automate some common Excel tasks, apply advanced analysis techniques to more complex data sets, troubleshoot errors, collaborate on worksheets, and share Excel data with other applications.
Software tools enable us to be more productive and efficient. Learn how to leverage the powerful features of Excel for HR, auditing and other HR tasks.  Explore the use of Excel as an audit and analysis tool, as well as, selected features that help prepare accurate reports. 

Course Methodology

This course is extremely practical with multitude of hands on exercises that HR Executives will learn to apply in routine HR work and present meaningful information to management.

Course Objectives

  • Display first name, last name with prefix in one single column from three different Column.
  • Find years of service of employees.
  • Find the total salary expenses in different regions, different departments.
  • Total number of employees in the company in different departments.
  • Find the total salary given to employees of a particular department.
  • Find number of employees joined on particular date/month in any department in any country.
  • Calculate the bonus based on overall performance of an employee.
  • Find the number of leaves taken by any employee.
  • Count number of directors or any designation in a company.
  • Display the records according to department, by hire date, by salary,...etc.
  • Calculate the total overtime hours for employees for any month.
  • View summarized salary for all the different countries in different departments.
  • Set the status, department of an employee through data validation.
  • Validate the hire date upon data entry.
  • Calculate salary based on overtime hours.
  • Calculate the net pay of an employee from his/her daily pay and leaves taken.
  • Consolidate overtime hours and rate for employees.
  • Compare employee's involvement in department from the working hours using charts.
  • Find total number of employees working in specific department from specific dates.

Target Audience

New and Experienced HR professionals from Admin Professionals, Compensation and Benefits Professionals, HR Reward and Payroll Professionals, HR Managers, Anyone who perform HR functions in their organizations on daily basis.

Target Competencies

  • HR - Reporting, techniques.
  • Analyzing HR Data.
  • Data modeling. 
  • Integration with external data sources.
  • Report structuring techniques.
  • Automation and macros.
  • Charting and visualization techniques. 
  • Utilizing Excel 2013 / 2016.
  • Practicing pivot table.

Course Outline

HR Reports

  • Listing reports - custom views
  • Consolidating reports - pivot Tables

Cells and Name Ranges

  • Create a name ranges for HR Data
  • Use Name Ranges for HR Report Calculations
  • Create Names from Selection Command
  • Creating a 3-D Formula
  • Update Name Ranges
  • Edit Name Ranges

HR Data Formatting

  • Formatting Numbers - Earnings
  • Formatting Dates - Hire Dates
  • Conditional Formatting
  • Avoid Duplication of Employee
  • Data Bars for Performance Appraisal
  • Icon Sets for Job Level
  • Color Scale for Employees Grades
  • Greater or Less Than for Earnings
  • Based on a Formula
  • Set Warnings on Passport Expiry Date

Automate HR Repetitive Tasks

  • Create Macros for HR Reports
  • Using Macros for Monthly Reports
  • Saving Macros in Personnel macros Workbook
  • Assign Macros to Quick Access Toolbar
  • Update Macros for HR Reports Changes

Data Calculations

  • Date and time functions for attendance
  • Working Days
  • Years of Service

Logical and Statistical Functions

  • Employee Pay Increase & Bonus
  • Implement HR Policies
  • HR Benefits
  • Statistical functions for HR Data

Lookup Functions


Creating Masters with Data Validation

  • Employee status/Locations/Grades
  • Designations/Nationality/Departments
  • Gender/Cost Center/Text validation
  • Date Validation/Number validation
  • List validation/Custom validation

Leave Records and Calculations

  • Is the employee eligible for Leave? IF Function
  • Leave Accured
  • LeaveTaken 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 End cashable Leave

HR Charts and Graphs

  • Gender Score
  • Nationality Diversity Score Card

Comprehensive Exercises

  • Calculating Gratuity
  • Salary Increment Calculator
  • Performance Review Scores
  • Man-Power Planning
Location & Dates
Call us at +971 4 430 8394