Excel Skills & Dashboards for HR & Compensation Professionals (Online Training)

Overview

Timings: 10:30 to 16:00 GST
Duration: Three sessions of 90 minutes each with two 30-minute breaks

This course is designed for reward professionals who use Excel on a day-to-day basis in their reward work and want to gain a deeper understanding of the more sophisticated facilities offered in the compensation data area.

Reward professionals today require a high level of proficiency in Excel in order to effectively analyse remuneration data. They need to know how to use pivot tables, charts, advanced data and formatting techniques, LOOKUP, IF and statistical formulas. Having the required proficiency in these areas can make the difference between taking three minutes to do a job or three days. Gaining this understanding can enable reward practitioners to concentrate on extracting insights from the data rather than wasting time crunching numbers.

Our hands-on practical course covers the essential Excel skills required to analyse market and organisation remuneration information. It has been created for reward, HR and payroll professionals who would like to enhance their Excel skills, and perform complex job tasks such as compiling and analysing data and performing calculations. For those who already have a good working knowledge in Excel, this course will provide you with the expertise and skills to use Excel at a higher level.

Who Should Attend

Compensation and HR professionals who work with Excel on a regular basis but would like to enhance their skills to improve their productivity in Excel and learn how to master time saving formulas and techniques to assist with their HR or compensation work. Delegates should use Excel for their day-to-day work, be able to navigate comfortably in Excel and know how to enter and edit formulas.

Benefits of Attending

• Add useful icons to the quick access toolbar
• Custom format dates and use date formulas to calculate age or length of service
• Use the VLOOKUP and IF formulas to improve accuracy and productivity
• Use conditional formatting to highlight anomalies or duplicate data
• Apply conditional formatting data bars, colour scales and icon sets
• Create, edit and format column charts, bar charts, line charts and pie charts; time saving tips when working with charts
• Use pivot tables to sum, count, min, max or average data
• Understand the principles of dashboard design and review a formula-driven rewards dashboard
• Create a pivot chart HR statistics dashboard, and make your dashboard interactive with slicers

Course Outline

DAY ONE

Customising Quick Access Toolbar/Importing & Formatting Data

• Adding and removing icons from the quick access toolbar
• Setting column width for the whole worksheet
• Converting text to numbers
• Converting data from one to two or more columns
• Combining data from two or more columns into one column
• Converting data from upper to proper case
  
• Using Flash Fill
• Formatting as currency or accounting format

Exercise

Custom Formatting & Working With Dates

• Wrapping text in headings
• Custom formatting of dates
• Custom formatting of numbers
• Calculating age/length of service using Datedif
• Calculating age/length of service using Yearfrac

Exercise

Using Sum, Lookup & If Formulas To Value Pay Benefits

• Using Autosum formula to total costs
• Using Vlookup formula to value benefits
• Using If formula to calculate pay
• Using Vlookup formula to merge data from different sheets
• Using Iferror formula together with Vlookup
• Using Vlookup formula to look up merit increases

Exercise

DAY TWO

Projecting/Comparing To Market Data & Conditional Formatting

• Projecting market data using Datedif
• Calculating comparative ratios / percentages for market comparison
• Using the format painter brush to apply formatting
• Using conditional formatting rules
• Using conditional formatting data bars, colour scales and icon sets
• Managing, editing and clearing conditional formatting rules

Exercise

Graphing/Charting Remuneration Data

• Using column/bar/line charts for market comparison
• Graphing actual salaries/comparative ratios
• Creating and formatting pie charts
• Formatting and editing charts (style, data series, titles, axes, gridlines, legends, data labels, data table)
• Adding data to a graph

Exercise

Dashboards Presentation

• What are Excel dashboards?
• How to create Excel dashboards?
• Examples of HR and Rewards Excel dashboards

Illustration Of A Formula Driven Dashboard

• Quick review of a formula driven dashboard to illustrate some of the Excel techniques that can be used to create dashboards?

Introduction To Pivot Tables

• Creating different types of pivot table using Count, Sum, Min, Max and Average
• Formatting and revising the pivot table
• Creating report filter pages in a pivot table

Creating A Pivot Chart Dashboard

Creating An HR Statistics Pivot Chart Dashboard

• Grouping data in a pivot table
• Creating and sizing charts to fit the dashboard
• Making the dashboard interactive with slicers
• Protecting the dashboard