Using Excel Power Query to Transform your Reward Work
Course Information

EXCEL FOR REWARD – MASTERCLASS #3

Using Excel Power Query to Transform your Reward Work

Part of our Spring 2021 Excel for Reward Masterclass series: click here for more details about the five afternoon sessions.

Date: Thursday 20th May 2021
Time: 1pm to 3pm (UK)
Duration: Two hours including five-minute break at approx. 2pm
Venue: Your PC

Watch Dianne demonstrate one of the smartest and most powerful tools ever developed for Excel. Power Query comes standard with Excel 2016 / 2019 / Office 365, and is available as an add-in to Excel 2010 / 2013.

Dianne will show you how it can automate routine data transformation (e.g. merging / splitting / removing columns, filtering data, replacing data, rounding, sorting, converting text to numbers and dates). She will demonstrate how you can achieve this without using macros and VBA, and with hardly any formulas.

Almost all the transformation is done through the simple use of a menu and a mouse.

Dianne will show you how to merge and transform multiple files in a folder, then how to add / transform another file from the folder simply by clicking Refresh. You can scrape data from web tables, transform the data for use in an Excel file, then update it with a single click.

Dianne will demonstrate:

  • How to merge and transform three monthly files of labour turnover data for use in a pivot table, then load / transform the next three monthly files and update the pivot table with a single click.
  • How to calculate medians per gender / job title.
  • How to scrape exchange rate data from the web, use the data in a formula to convert multi-country salaries to a single currency, and update data with a single click.
  • How to pivot employee salary data into different business units for use in scatter charts and pay structure design.
  • How to unpivot problem sales commission data for use in analysis.
  • Power Query’s AI capability of adding a column based on examples.

Dates

:: 22nd April 2021: 1pm-3pm (UK)

Grading Maps

:: 6th May 2021: 1pm-3pm (UK)

Macros

:: 20th May 2021: 1pm-3pm (UK)

Excel Power Query

:: 3rd June 2021: 1pm-4pm (UK)

Excel Dashboards

:: 17th June 2021: 1pm-3pm (UK)

Regression Analysis and Dynamic Arrays

Live-streamed masterclasses. Attend from your home or any location. No travel required. Recordings of each masterclass available for 90 days after the virtual class.

Dianne Auld is an international guru who makes tricky reward-related Excel tasks transform into solutions in a blink of an eye. Dianne will show reward professionals a range of Excel time-saving skills. Her five masterclasses will dramatically increase your productivity and present spectacular ways to analyse mountains of reward data in seconds with professional looking charts, dashboards and Pivot Tables.

Fees

Per masterclass: GBP £195 + VAT per delegate. Attend all five masterclasses: GBP £795 + VAT per delegate.

To ensure an effective learning experience, there are just 50 seats available for each masterclass.

Cancellations: Please note that delegates will be held liable for the full fee if registration is not cancelled by 22nd March 2021 or if the delegate is a 'no show'.

Dianne Auld, GRP, CCP, CSCP
Dianne Auld, GRP, CCP, CSCP
Senior Consultant

Dianne is the founding member of Auld Compensation Consulting, operating out of Cape Town, South Africa. Her consulting work around the world covers all areas of total rewards, including reward strategy, base pay management, job evaluation, pay structuring, incentive design and sales compensation. She has developed and taught Excel and rewards courses in Africa, the Middle East, Asia, Europe, and the United States.

As a WorldatWork faculty member, Dianne teaches the GRP reward qualification courses in Southern Africa. She is the author of Dianne Auld’s Excel Tips, 3rd edition. Dianne has developed and recorded several e-learning products for WorldatWork, including Excel Skills, Advanced Excel Skills and Pay Structures. She is an honorary life member of the South African Reward Association (SARA) and an accredited Master Reward Specialist.

EXCEL FOR REWARD – MASTERCLASS #3

Using Excel Power Query to Transform your Reward Work

Part of our Spring 2021 Excel for Reward Masterclass series: click here for more details about the five afternoon sessions.

Date: Thursday 20th May 2021
Time: 1pm to 3pm (UK)
Duration: Two hours including five-minute break at approx. 2pm
Venue: Your PC

Watch Dianne demonstrate one of the smartest and most powerful tools ever developed for Excel. Power Query comes standard with Excel 2016 / 2019 / Office 365, and is available as an add-in to Excel 2010 / 2013.

Dianne will show you how it can automate routine data transformation (e.g. merging / splitting / removing columns, filtering data, replacing data, rounding, sorting, converting text to numbers and dates). She will demonstrate how you can achieve this without using macros and VBA, and with hardly any formulas.

Almost all the transformation is done through the simple use of a menu and a mouse.

Dianne will show you how to merge and transform multiple files in a folder, then how to add / transform another file from the folder simply by clicking Refresh. You can scrape data from web tables, transform the data for use in an Excel file, then update it with a single click.

Dianne will demonstrate:

  • How to merge and transform three monthly files of labour turnover data for use in a pivot table, then load / transform the next three monthly files and update the pivot table with a single click.
  • How to calculate medians per gender / job title.
  • How to scrape exchange rate data from the web, use the data in a formula to convert multi-country salaries to a single currency, and update data with a single click.
  • How to pivot employee salary data into different business units for use in scatter charts and pay structure design.
  • How to unpivot problem sales commission data for use in analysis.
  • Power Query’s AI capability of adding a column based on examples.
Virtual classroom: Thursday afternoon 20/05/2021 - 20/05/2021