Advanced Excel Skills & Dashboards for Compensation Professionals (Online Training) - Informa Connect Middle East
Contact for Queries: +971 4 407 2500 info-mea@informa.com
العربية
Select Page
×

*red highlight indicates mandatory fields

Country(+)
Area Code
Phone Number

For further information on our privacy and cookies policies, click here.
Advanced Excel Skills & Dashboards for Compensation Professionals (Online Training) - Informa Connect Middle East
Home > LIVE Online Training > HR > Advanced Excel Skills & Dashboards for Compensation Professionals (Online Training)
2For1 Offer
Advanced Excel Skills & Dashboards for Compensation Professionals (Online Training) Training Course | HR Training Course

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

14 December - 15 December 2020
Online Training

Level: Advanced

Brochure Download   In-Company Training   REGISTER FOR THE COURSE  

Course Director

Dianne Auld | Course Director

Dianne Auld


Course Schedule
  • 14 Dec 2020 Online

Overview

Timings: 10:30 to 16:00 GST
Duration: The online training will be in sessions of 90 minutes each with two 30-minute breaks

An advanced-level live streaming online course helping you utilise advanced Excel formulas for your rewards work, understand the principles behind creating dashboards and create interactive formula-driven and pivot chart dashboards.

Who Should Attend

Compensation professionals who already have intermediate Excel skills, and would like to enhance their skills to improve their productivity in Excel and learn how to create interactive Excel models and dashboards for their rewards work. Delegates should use Excel for their day-to-day rewards work, be able to write a VLOOKUP formula and be familiar with pivots and charts.

Benefits of Attending

  • How to use Form Controls to set up scroll bars, spin buttons and combo boxes to make your data models and dashboards interactive with a professional touch
  • How to use the OFFSET, INDEX and CHOOSE formulas to count, sum and average rewards data for different periods based on user choice
  • How to use named ranges and the INDIRECT formula together with the VLOOKUP formula to increase productivity and reduce errors when working with rewards data
  • Understanding the logic of the IF formula
  • How to use IF formulas, IF (AND) and IF (OR) formulas, Nested IF formulas, COUNTIF, SUMIF, AVERAGIF, COUNTIFS, SUMIFS and VERAGIFS formulas for rewards work
  • How to set up data validation for forms, models or dashboards
  • How to protect the sheet and the workbook
  • Understand the principles of dashboard design
  • Create an interactive formula driven market comparison dashboard using formulas, conditional formatting, charts and the camera tool
  • Create a pivot chart HR statistics dashboard, using the pivot functionality to group data, and making the dashboard interactive with slicers

Course Outline


Module 1: Using Advanced Excel Formulas
14 December 2020

Lookup And Reference Formulas

Using the following formulas to do pay and benefits calculations:

  • Indirect formula with Vlookup
  • Index formula
  • Offset formula
  • Choose formula
  • Inserting Form Controls – scroll bar, spin button and combo box

Exercise

If Formulas
Using the following formulas to do pay and benefits calculations:

  • If formula
  • If (And) ; If (Or) formulas
  • Nested If formula
  • Countif, Sumif, Averagif formulas
  • Countifs, Sumifs, Averagifs formulas

Exercise

Data Validation And Protection

  • Setting up a form / document with data validation and protection
  • Validating using a list and other criteria
  • Hiding sheets
  • Unprotecting cells and hiding formulas
  • Protecting the sheet and the workbook

Exercise

Module 2: Creating Powerful Excel Dashboards
15 December 2020

Discussion Of Previous Exercise

Dashboards Presentation

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

Creating A Dashboard Framework

Creating A Market Comparison Dashboard

  • Using the Offset, Match, Averageifs, Iferror, Datedif, Indirect, Array and other formulas and functions to create an interactive formula driven dashboard
  • Using conditional formatting icons and charts to add visuals to the dashboard
  • Using the camera tool to show tables in the dashboard
  • Protecting the dashboard

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

BW208
Advanced Excel Skills & Dashboards for Compensation Professionals (Online Training)