Course Objectives
Excel Skills for HR and Admin Professionals - Virtual Learning
Course Objectives
By the end of the course, participants will be able to:
Apply Excel reporting expertise in HR and administration by enhancing data slicing and dicing, data massaging, and data analysis skills
Use pivot tables and pivot charts to efficiently perform automated report writing and analysis
Develop operational dashboards including business KPI’s
Repeat tasks and generate reports efficiently by recording, running and editing macros
Acquire numerous tips and tricks that will improve working efficiency
Target Audience
HR and administration professionals, compensation and bene?ts professionals, HR reward and payroll professionals, HR managers as well as anyone who perform HR and administration functions in their organizations on regular basis.
Target Competencies
HR reporting techniques
Analyzing HR data
Data modeling
Integration with external data sources
Report structuring techniques
Automation of routine tasks
Charting and visualization techniques
leveraging pivot table powers
Course Outline
?HR reportsListing reports with custom viewsPreparing reports using pivot tablesConsolidation of data
Cells and name rangesCreating name ranges for dataUsing name ranges for values calculationsCreating names from selection commandCreating a 3-D formulaUpdating name rangesEditing name ranges
HR data formattingFormatting numbersFormatting datesConditional formattingAvoiding duplication of employee recordsData bars for performance appraisalIcon sets for job levelColor scale for employees gradesGreater or less than for earningsBased on a formulaSetting warnings on residency expiry date
Data calculationsDate and time functions for attendanceWorking daysYears of service
Lookup functionsVlookup and hlookupApproximate and exact matchIndex and match functions
Leave records and calculationsIs the employee eligible for leave or bonus? If functionAccrued leaveLeave taken recordsLeave balance
End of service calculationsCompute gratuity