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
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
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
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
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
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
- 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
Dianne is without doubt the most highly regarded and popular Excel for Reward expert in the world. Well respected and distinguished in the compensation field and Excel, her skills and expertise are in high demand.
Based in Cape Town, South Africa, she travels worldwide showing reward professionals how to harness all the power of Excel. Dianne has developed a series of Excel and dashboard courses for compensation professionals. Dianne is also a faculty member at WorldatWork and a reviewer for the WorldatWork Journal. Since 2012, she has presented each year at the Worldatwork US Total Rewards conference.
Dianne owns a consulting practice, Auld Compensation Consulting, in South Africa. She consults to a wide range of organisations across Africa and the Middle East in all areas of total rewards: reward strategy, base pay management, remuneration surveys, job evaluation, broadbanding, pay structuring, incentive design and sales compensation. She has developed and taught courses in Africa, the Middle East, the Far East, Europe, and the United States.
Pricing excludes 5% VAT, which will be charged where applicable