MS Excel for HR Professionals

Training Goals & Objectives

This course on MS Excel for HR Professionals Training in Dubai UAE focuses on the use of formulas and functions in various HR scenarios. With the help of several skills covered in this course, you will be able to perform many calculations with ease, speed and a great level of confidence.

This course will also show you how to set up your worksheet on auto-pilot, re-use it and re-purpose it for other applications. Essentially, after the course, you will stop using Excel just as a calculator and start using it as a system with far-reaching results.

Learning outcomes

This training will enable participants to understand the best in class, ways of using Microsoft Excel. Following are the key learning outcomes of this session:

  • Use spreadsheet skills for Human Resource Management Applications
  • Create, format and trouble shoot pivot tables
  • Analyze data accurately using pivot tables and slicers
  • Adopt logical methods to solve complex problems by analyzing data accurately using pivot tables
  • Gain advanced skills to perform the most complex calculations and functions you can find in a business situation
  • Understand how to design an effective spreadsheet and increase your ability to forecast outcomes accurately

Training Program

Course contents of the program are as under:

Pre Session Activities

  • Networking & Participant Introduction
  • Course introductions & Overview

Conditional Formatting of HR Data

  • Formatting Organization’s data
  • Highlight Duplicate & Unique Values
  • Finding top 5 / 10 items in the HR Data
  • Formatting Employee data using Data Bars & Icons
  • Clearing & Managing Department Data
  • Nested loops
  • Analyzing only total rows
  • Summarizing a data based on Person,
  • Department and Company
  • Adding data based on two attributes

Summarizing & Consolidating HR Data

  • Sorting Organization data with Multiple Level Sorts
  • Advance Filtering on HR Data with more than 1 criteria
  • Extracting Unique Values from Employee data
  • Filtering of Employee data with respect to
  • Status, Location, Grade, Nationality, Designation, Department etc.

HR Working on Pivot Tables & Slicer

  • Creating Pivot table & analyzing employee data
  • Creating Payroll sheets from Pivot table
  • Creating Dynamic Pivot of department information
  • Employee Table creation & text to column
  • Defining department & working with tables
  • Creating more than one HR pivot table on one sheet
  • Creating employee slicer with respect Name, Dependent name, Education, Previous employers, Birthdates, Hire date, Remuneration, etc.

Lookups and Data Table of Organization

  • Recruiting right candidate based on row criteria
  • Match and indexing department information
  • Department Data Tables single variable
  • Department Data Tables more than one variable
  • CV Data Entry Forms
  • Performance Measurement through Grade sheet / Result sheet
  • Excel speech on data entry
  • Creating potential candidate list in excel and Computing Gratuity

Charts & Validation of HR

  • Creating Column, Line & Pie Charts for leave eligibility
  • Shortcuts of Creating Charts of professionals hired/fired
  • Adding Target Line in organization chart and changing charts
  • Method of pasting chart into PowerPoint
  • Sharing HR Workbook of Department wise performance, employee leave taken, accrued, balance etc.
  • Hyperlinking

HR- Department wise Data Mining

  • Auditing department performance through spread sheet
  • Using Attendance data to calculate payroll
  • Calculating air ticket allowance based on various policy criteria
  • Identifying source cells payroll sheet and salary slips
  • Changing HR source data year on year
  • Tracing employees with dependent cell
  • Avoid duplication of employee
  • Set warnings on Passport expiry date
  • Quick summary of organizational data

Macros

  • Record and/or modify departments, employees and location macros for automating tasks
  • Run / Delete a macro

HR Data Security in MS Excel

  • Employee File Level Security
  • Department Sheet Level Security
  • Making the HR worksheets invisible
  • Preventing HR Sheets from Deletion
  • Cell / Row / Column Level Security
  • Limiting access to cell
  • Maintaining HR security for group of companies 

HR Charts and Graphs

  • Gender score card using pie charts
  • Nationality Diversity score card using pie charts

Certificate Distribution Ceremony