MS Excel for HR

Introduction

This is a 3-hour 3-day webinar focused on Human Resource requirements, to be completed using MS Excel (2010-365 version). All exercises are instructor-led exercises, with supporting MS PowerPoint guides, providing details of the means and methodology of accomplishing the desired output for this webinar.

All training exercises have been designed to conform to generally accepted standard data structures of a Human Resource Information System (HRIS) using MS Excel as the mode of development

Data Structure

The facilitator shall provide participants with data structures conforming to standard practice capturing data, such data capture covering the following:

  • Employee Master File Table
  • Time In Time Out (TITO) Transaction Table
  • Capacity Building Tables of data
  • Licenses and Certificates schedules
  • Attended Training
  • Prior Work Experiences
  • Dependents Listing
  • Assigned Supervisors
  • Compensation Data
  • Employee On-Boarded Records
  • Employee Off-Boarded Records
  • Recruitment Transaction Records
  • Leaves Availment Transaction Records
  •  

Expected Capabilities

The following are the salient features of Microsoft Excel which will be learned and applied to achieve the required reports.

  • Feature #01: Creating tabular data using the format as table
  • Feature #02: Use of dynamic and stop static naming for faster and more English like formula creation
  • Feature #03: Excel 365 application of advanced features such as Xlookup, filter, Xmatch, Vlookup, Lookup, Index-Match, and Hlookup.
  • Feature #04: Connecting Microsoft Excel to web-based data sources, to simulate data capture of time and attendance data (time in and time out data).
  • Feature #05: Creating navigable and hyperlinked menus to provide a convenient means of traversing data visualization reports as well as named data capture areas of the workbook.
  • Feature #06: Where applicable (depending on the Excel version used), participants would be able to create geographical maps, auto-data consolidation capability.
  •  

Reports to be Discussed

The following are the subjects to be taken up during the workshop proper

  • Report #01: Employee Master Data Visualization 
  • Report #02: Time and Attendance Data Visualization 
  • Report #03: Schedule of Training Undertaken Per Employee Data Visualization 
  • Report #04: Schedule of Dependents Per Employee Data Visualization 
  • Report #05: Schedule of On-Boarded Employees Data Visualization