Excel Skills for HR and Admin Professionals

• HR reports
  • Listing reports with custom views
  • Preparing reports using pivot tables
  • Consolidation of data

• Cells and name ranges
  • Creating name ranges for data
  • Using name ranges for values calculations
  • Creating names from selection command
  • Creating a 3-D formula
  • Updating name ranges
  • Editing name ranges

• HR data formatting
  • Formatting numbers
  • Formatting dates
  • Conditional formatting
  • Avoiding duplication of employee records
  • 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
  • Setting warnings on residency expiry date

• Automate HR repetitive tasks
  • Creating macros for HR reports
  • Using macros for monthly reports
  • Saving macros in personnel macros workbook
  • Assigning macros to quick access toolbar

• Data calculations
  • Date and time functions for attendance
  • Working days
  • Years of service

• Logical and statistical functions
  • Employee pay increase and bonus (paste special)
  • Implement HR policies
  • Statistical functions for HR data
  • Sumif / sumifs / count / countif / countifs / subtotal
  • Average / averageif / averageifs

• Lookup functions
  • Vlookup and hlookup
  • Approximate and exact match
  • Index and match functions

• Creating masters with data validation
  • Employee status, locations and grades
  • Employee designations, nationalities and departments
  • Date and number validation
  • Custom validation

• Leave records and calculations
  • Is the employee eligible for leave or bonus? If function
  • Accrued leave
  • Leave taken records
  • Leave balance

• Payroll and benefits
  • Payroll sheet and salary slips
  • Using attendance data to calculate payroll

​​

• End of service calculations
  • Computing gratuity
  • Computing cashable leave

• HR charts and graphs
  • Speedometer chart
  • Performance against range
  • Spinner

• Comprehensive exercises
  • Calculating gratuity
  • Salary increment calculator
  • Performance review scores