Creating Self Updating Grading Matrices
Course Information

EXCEL FOR REWARD – MASTERCLASS #1

Creating Self Updating Grading Matrices

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

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

Once your jobs have been evaluated, using either a job content or market pricing approach, your reward team and management need to audit and view the grades by grade and by function (or job family, discipline, business unit, division, area, company).

To conduct a meaningful audit, the jobs need to be in a matrix form rather than in a long list. This enables an equity / consistency check:

  • horizontally – similar grades across different functions
  • vertically – grade differences within each function

This matrix also presents the job grade data in a user-friendly and accessible format for viewing of grades and determining of career paths, as shown in the screenshot.

macros image

All too often, these grading matrices are high maintenance and error prone, as:

  • jobs change constantly – new jobs, obsolete jobs, changes to job title or grade
  • organisation structures and function names often change
  • reward practitioners need to remember to update the matrix when jobs / grades / functions change
  • the update is tedious and time consuming, as each job needs to be placed in the right function and grade, old jobs need to be moved or deleted, checks need to be done for duplicates
  • the number of rows in the matrix constantly has to be adjusted to cater for more or less jobs in each grade; lots of blank rows left in the matrix make it difficult to read and print

During this Excel masterclass, Dianne will demonstrate how to transform this time-consuming and error-prone process into a streamlined, accurate, low-maintenance approach. She will show you how to:

  • Use Power Query to create the source data for the grading matrix from payroll / HRIS data
  • Create a grading matrix that updates automatically from a list of functions, jobs and grades
  • Add an extra dimension / drop-down to the grading matrix, so that the organisation can have a separate grading matrix for each company, geographical area or business unit, all reading from the same list of jobs
  • Record a macro that will adjust every grading matrix in the file to show the required number of rows per grade
  • Update all grading matrices from payroll / HRIS data with two clicks
  • Create the grading matrices using formulas (for legacy Excel users) or dynamic arrays (for Microsoft 365 users)

Attendees will be provided after the session with the Excel spreadsheets demonstrated and detailed instructions, so that they can replicate the functionality with their own grading data.

Who should attend?

Reward and HR practitioners who work with grades and would like to know how to transform data from a list to a matrix.

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 #1

Creating Self Updating Grading Matrices

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

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

Once your jobs have been evaluated, using either a job content or market pricing approach, your reward team and management need to audit and view the grades by grade and by function (or job family, discipline, business unit, division, area, company).

To conduct a meaningful audit, the jobs need to be in a matrix form rather than in a long list. This enables an equity / consistency check:

  • horizontally – similar grades across different functions
  • vertically – grade differences within each function

This matrix also presents the job grade data in a user-friendly and accessible format for viewing of grades and determining of career paths, as shown in the screenshot.

macros image

All too often, these grading matrices are high maintenance and error prone, as:

  • jobs change constantly – new jobs, obsolete jobs, changes to job title or grade
  • organisation structures and function names often change
  • reward practitioners need to remember to update the matrix when jobs / grades / functions change
  • the update is tedious and time consuming, as each job needs to be placed in the right function and grade, old jobs need to be moved or deleted, checks need to be done for duplicates
  • the number of rows in the matrix constantly has to be adjusted to cater for more or less jobs in each grade; lots of blank rows left in the matrix make it difficult to read and print

During this Excel masterclass, Dianne will demonstrate how to transform this time-consuming and error-prone process into a streamlined, accurate, low-maintenance approach. She will show you how to:

  • Use Power Query to create the source data for the grading matrix from payroll / HRIS data
  • Create a grading matrix that updates automatically from a list of functions, jobs and grades
  • Add an extra dimension / drop-down to the grading matrix, so that the organisation can have a separate grading matrix for each company, geographical area or business unit, all reading from the same list of jobs
  • Record a macro that will adjust every grading matrix in the file to show the required number of rows per grade
  • Update all grading matrices from payroll / HRIS data with two clicks
  • Create the grading matrices using formulas (for legacy Excel users) or dynamic arrays (for Microsoft 365 users)

Attendees will be provided after the session with the Excel spreadsheets demonstrated and detailed instructions, so that they can replicate the functionality with their own grading data.

Who should attend?

Reward and HR practitioners who work with grades and would like to know how to transform data from a list to a matrix.

Virtual classroom: Thursday afternoon 22/04/2021 - 22/04/2021