HR Professionals will learn the tips that save hours to resolve the diﬃculties they face on a daily basis in doing employee compensations, salary, leave, beneﬁts etc. In this practical training, participants will learn formatting techniques to convert data from diﬀerent 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 eﬃcient. 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.
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.
- 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.
New and Experienced HR professionals from Admin Professionals, Compensation and Beneﬁts Professionals, HR Reward and Payroll Professionals, HR Managers, Anyone who perform HR functions in their organizations on daily basis.
- 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.
- 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
- 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
- SUMIF / SUMIFS / COUNTIF / COUNTIFS
- AVERAGEIF / AVERAGEIFS
- CLOSET MATCH
- EXACT MATCH
- INDEX & MATCH FUNCTIONS
Creating Masters with Data Validation
- Employee status/Locations/Grades
- 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
- Calculating Gratuity
- Salary Increment Calculator
- Performance Review Scores
- Man-Power Planning