Dianne Auld Diploma: Excel for Reward Pro – Level 2
Course Information

Advanced Analysis of Reward Data Using Formulas /Creating Excel HR & Reward Dashboards


Level: Providing comp & ben professionals with the knowledge and skills to use Excel at a highly advanced level in their reward work.

Date: 25-27 November 2024.

Location: Your PC via Zoom.

Duration: Three days, 9am-1pm (UK)

Format: Highly-practical course designed to help you master Excel skills specific to comp & ben work not taught in general Excel course. Very interactive with lots of practical exercises. Includes sleek visual & audio demos of tasks reward pros need to perform in their job function. During these demos, students follow the instructions being highlighted by Dianne, then immediately afterwards undertake a parallel exercise on their own, with teacher guidance to cement the learning.

Fees: £1,095 + VAT per delegate.

Download brochure: Click here


Download report: Click here

Dianne Auld's Top Tips


Overview

Reward professionals enrolling in this two-day course should already have strong Excel skills. It is designed for students who would like to learn advanced Excel techniques, and how to apply these to create powerful interactive Excel dashboards for viewing and analysing reward data.

Excel dashboards have enormous application in HR and compensation work. Dashboards enable the consolidation of huge amounts of remuneration data into an executive summary view. They use interactive fields to allow different views of the data and modelling of data. Dashboards can replace bulky and cumbersome reports, show management only what they want to see and allow powerful ‘what-if’ analyses for incentive modelling.

Diploma

Once you have successfully completed the training exercises, you will receive the certificate: Dianne Auld Diploma: Excel for Reward Pro – Level 2. You will also need to have attended all two days of the diploma course.

Criteria for attending

  • An intermediate knowledge of Excel is required before attending the workshop.
  • Ideally, delegates would already have attended the Level 1 diploma or a similar course or have equivalent knowledge.
  • Delegates should be comfortable using Excel formulas, creating charts and pivot tables.
  • Delegates should be able to write Vlookup and If formulas – only a brief review of these will be provided.

Dianne says:

‘Students enrolling in this diploma should already have a thorough understanding of Excel but want to study more about the advanced facilities offered in this area. I have created this diploma to look further into Excel and help you master the more highly advanced Excel skills specific to reward work. You will learn how to use these to create powerful interactive Excel dashboards for viewing and analysing remuneration data.’

Documentation

Delegates will receive:

  • E-book: Dianne Auld's Excel Tips: Featuring compensation & benefits formulas, 500 pages, 3rd Edition published October 2020.
  • Excel files complete with all the exercises, spreadsheets and dashboards undertaken during class.

Requirements

This diploma requires you use a Windows PC laptop (not Mac) with Microsoft Excel 2010, ideally Excel 2013 or later (plus a charger).

We recommend that you use a mouse during the course to help with extensive on-screen navigation.

With this class you will:

  • Learn advanced Excel functions, including complex Lookup and If formulas, data validation and protection, array formulas and macros to do pay and benefits calculations and pay and commissions calculations.
  • Learn about data validation and protection as well as macros and put what you learned into action as you create an Excel dashboard that will illustrate how pay structures compare competitively to the market.
  • Learn advanced Excel formulas to streamline your compensation tasks.
  • Create an interactive Excel dashboard using a real-world scenario that illustrates how pay structures compare competitively to the market.
  • Transform vast amounts of data and display analysis and key findings in a condensed space where it is easy to access, interact with and understand.
  • Learn how to gain critical information and insights from dashboards in order to make quick and effective business decisions.

Ask questions and interact with Excel for Reward expert, Dianne Auld.


‘Power Query was the most useful course for me. It helped me to complete our gender pay analysis much faster than with the previous manual method. The course was easy to follow, Dianne explained everything and talked us through step by step. She is really good at explaining complex formulas in a simple way. The measures feature is a God send!!! I don’t know how I ever managed without it . . . Thanks to Dianne’s course I am more productive as I have more time to spend on the narrative and answering the why added value questions that companies so need to understand. Thanks Dianne.’ – Samantha Reid, former Chevron employee.
‘Dianne’s Excel reward training is fantastic. It gave me practical excel skills for use in reward as well as great theory on how to set up and update pay structures. So many benefits from attending! It’s all fantastic!’ – Susan Maclean, Micro Focus.
‘I attended two masterclasses, Excel Power Query and Powerful Excel Dashboards. I really enjoyed both courses, but the Excel Power Query has been the most useful to me. I use Excel all the time in my role and the ability to format, build and refresh data files in Power Query has already saved time and reduced some of my workload. The ability to merge the different data files in to one has just been so useful. I only wish I had done the course years ago! I enjoyed Dianne’s friendly presentation style. She is extremely knowledgeable and explained the techniques in a clear but comprehensive way. Having all the documentation to refer back to has also helped me so I was able to refer back to it when I wanted to try it for myself.’ – Anne Farquhar, Sanctuary Housing.
‘All of Dianne’s courses are so useful. Each one gave me something practical that I could see myself using in my role. Dianne showed us so many tricks and tools in Excel which have helped me to work more efficiently. She uses real reward examples so you can apply what you have learnt in your day job. It’s saved me so much time – previously I was manipulating data to get the output I wanted. Dianne’s courses have really given me the tools and confidence to try new things within Excel. In reward, we work with data constantly, so being able to optimise how I use Excel to really get the most out of it and avoid the risks that come with manual data manipulation is really fantastic.’ – Susie Godfrey, Allen Overy.


TEASERS

1. Did you know that you can write a macro which will do eight hours of Excel work in a minute?

Find out how to record, edit and work with Excel macros, to automate repetitive work and save days of time, by attending this course.

2. Did you know that you can use Goal Seek to get to a desired merit budget at the click of a button?

Find out how to use Goal Seek, Array formulas and other time-saving tips, to speed up your rewards work by attending this course.

3. Did you know that you can use the Iferror formula to eliminate all those unsightly #N/A and #DIV/0! errors in your Excel worksheets without deleting your formulas?

Master the most important and time-saving lookup, reference and if formulas in Excel: Vlookup, Hlookup, Index, Match, Choose, Offset, Indirect, If, If(And), If(Or), Iferror, Countif, Sumif and Averageif, by attending this course.

4. Did you know that you can use the camera tool to put an entire table into one cell in an Excel dashboard, and make the table interactive based on a field chosen by a user?

Find out how to design and create powerful, interactive Excel dashboards by attending this course.

5. Did you know that you can set up user controlled slicers with a pivot table or chart to slice and dice the data, and that you can link one set of slicers to all the pivot tables and charts in a dashboard?

TESTIMONIALS

Dianne has taught many of E-reward’s clients in the past. Her reviews are OUTSTANDING . . .
‘I thought that the Excel masterclasses were insightful as always and the combination of watching Dianne in action, then going through the step-by-step instructions after with the video of the session to fall back on was a great way to teach what is a complicated process. You do need to spend some time on this after the session to properly understand it all, but it is already paying off as I’ve started applying it in my work!’
‘Just wanted to say how excellent the class was. Dianne was brilliant!! Exactly what I was after.’
‘Dianne is an amazing instructor. I am glad I found your comp organisation so I could take the class.’
‘That was an excellent class, well worth my company’s money. It’s exceptional finding such courses dedicated to compensation. Dianne’s teaching style is spot on for the subject.’
‘Dianne’s teaching style is second to none. I’ve never regretted attending her courses. The time to concentrate on building skills you know you will use is so valuable.’
‘I just wanted to let you know that the Excel course was great – I loved it and Diane was a brilliant teacher.’
‘Dianne was brilliant, very engaging. Just what was needed. The time went so quickly.’
‘The Excel stuff you need to know as a comp professional. Very helpful and presented in an easily accessible manner.’
‘Great course, full of tips and techniques.’
‘Valuable course, fantastic knowledge of the trainer. Learnt lots of quick short cuts and extremely valuable time-saving techniques.’
‘Dianne was worldly and informative.’
‘Dianne was really engaging, positive and interesting.’
‘Really interesting and useful course with a great instructor.’
‘Dianne was very good, informative and engaging.’
‘Very high level of knowledge.’
‘Excellent instructor.’
‘Very good with loads of useful examples. Dianne has an excellent skill in getting information across.’
‘The practical examples and the way Dianne built up the content was very good.’
‘Very good – I haven’t been on one of Dianne’s courses before, but I will certainly do some more.’
‘A really excellent course, one of the best I have been on.’
‘The course was fantastic! Dianne is an expert in reward and a great teacher. She covered everything that I expected and more – it was comprehensive, and I came away with a strong idea of how I can adapt and put the learning into practice.’
‘Excellent course that enables you to apply what has been learned immediately and Dianne is a fantastic trainer and clearly an expert in her field.’
‘It exceeded my expectations . . . the course was fantastic. I hope to be able to attend more of Dianne’s E-reward courses in future.’
‘Absolutely fabulous course. I learned so much. Dianne was so knowledgeable and shared so well. I’ve saved days and days now!’
‘Very informative course, explaining the possibilities of Excel. My life will be so much easier now I understand the capability of Excel in terms of compensation and reward.’
‘Dianne was great and extremely knowledgeable.’
‘Excellent instructor, very clear, good pace, always ensuring everyone is up to speed and keeping up with the rest of the class.’
‘Course content was well put together, very relevant to work and was made interesting by Dianne. I would highly recommend the course to anyone wanting to save time and understand Excel functions. Really pleased I attended.’
‘The instructor was very friendly and informative, no improvements needed!’


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

Dianne Auld, GRP, CCP, CSCP, 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.

Location

Live streamed from Dianne's office in Cape Town, via Zoom

DIANNE AULD DIPLOMA: EXCEL FOR REWARD PRO – LEVEL 2: Advanced Analysis of Reward Data Using Formulas /Creating Excel HR & Reward Dashboards



SECTION A: LOOKUP & REFERENCE FORMULAS PART 1

Using the following formulas to do pay and benefits calculations:

  • Vlookup formula
  • Hlookup formula
  • Iferror, IFNA with Vlookup
  • Using named ranges with Vlookup
  • Using Vlookup with the Columns formula
  • Xlookup formula
  • Looking up a Merit Matrix using Vlookup and Match

Exercise A


SECTION B: LOOKUP & REFERENCE FORMULAS PART 2

Feedback on Exercise A

Using the following formulas to do pay and commission calculations:

  • Indirect formula with Vlookup
  • Index formula
  • Offset formula
  • Choose formula

Inserting Form Controls – scroll bar, spin button and combo box

Exercise B


SECTION C: SUITE OF IF FORMULAS

Feedback on Exercise B

Using the following formulas to do pay and benefits calculations:

  • If formula
  • If (And) ; If (Or) formulas
  • Nested If formula
  • Countif, Sumif, Averageif formulas
  • Countifs, Sumifs, Averageifs formulas
  • Minifs, Maxifs formulas

Exercise C


SECTION D: ARRAY FORMULAS

Understanding the purpose and rules of array formulas

Understanding dynamic arrays in Microsoft 365

Writing multi-cell array formulas

Writing single-cell array formulas

Using array formulas for measures of location

Using an array formula with Indirect

Using Unique, Sort and Filter formulas

Exercise D


SECTION E: 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: Data validation and protection

DASHBOARDS PRESENTATION
CREATING A FORMULA DRIVEN MARKET COMPARISON DASHBOARD
CREATING A PIVOT CHART HR STATS DASHBOARD
ADDING ICONS, TEXT BOXES AND CUSTOMISED SLICERS TO AN INCREASE DASHBOARD




Virtual classroom 25/11/2024 - 27/11/2024
THE FOLLOWING TERMS AND CONDITIONS APPLY TO THE DELIVERY OF EDUCATION COURSES BY E-REWARD.CO.UK LTD (INCLUDING MASTERING REWARD COURSES; WORLDATWORK GLOBAL REMUNERATION PROFESSIONAL CERTIFICATION [GRP] COURSES AND EXAMINATIONS [PROGRAM]; IN-HOUSE AND CUSTOMISED TRAINING COURSES).

These terms and conditions apply between the person, firm, company or other entity specified on your booking form and E-reward.co.uk Ltd (company number: 4281768). Registered in England and Wales. Registered office 33 Denby Lane, Heaton Chapel, Stockport, Cheshire SK4 2RA, United Kingdom for delegate registrations for the course (including Mastering Reward courses, GRP courses with exam and in-house training courses) specified on your booking form (‘Course’).

Please read these terms and conditions carefully as they contain important information.

By submitting your registration to attend the Course you agree to be bound by these terms to the exclusion of all other terms. If you do not agree to be bound by these terms E-reward.co.uk Ltd will be unable to accept your registration.

Delegate cancellations

Delegates who advise E-reward.co.uk Ltd of their cancellation in writing via email (to paul@e-reward.co.uk) 30 calendar days before the start of the Course will have their fees refunded – less an administration charge of 20% of the Course fees.

No refund will be made for cancellations received less than 30 calendar days before the start of the Course.

Failure to attend the Course will be subject to the same terms.

  • GRP courses featuring pre-recorded online videos – If your GRP course includes access to pre-recorded video training, once this access has been provided to you (at least four weeks prior to the accompanying virtual classroom day), no refund of delegate fees will be payable.
  • In-house training – You will not have any right to cancel a Contract for the booking of any customised/in-house training courses. For the avoidance of doubt, no refund will be payable

Delegate transfers

Transfers may be made to any other Course in the UK organised by E-reward.co.uk Ltd subject to space availability. All transfer requests must be made in writing via email (to paul@e-reward.co.uk) 30 calendar days or more before the start of the Course.

There will be no fee charged for such transfers. One free transfer can be made for each course booking after which there will be a £100 administration charge for each further request.

Please note: We are unable to accept any transfer requests received less than 30 calendar days before the start of the course and no refunds will be made.

We are unable to accept any transfer requests once you have received access to GRP pre-recorded learning videos.

Substitutions with employees from your organisation are welcome at any time at no extra charge. You must email any substitutions (to paul@e-reward.co.uk) prior to the date of the Course.

Please note: We are unable to accept any ‘substitutions’ once you have received access to GRP pre-recorded learning videos.

Payment

You will pay E-reward.co.uk Ltd any fees specified in your booking form for the Course. Payment to E-reward.co.uk Ltd in GBP British Pounds is by BACS, wire transfer, cheque or credit card. It is a condition of booking that payment must be received by E-reward.co.uk Ltd before the start of the Course or no later than 30 days from the date of invoice, whichever is sooner.

If payment of your fees in full is not received before the Course, E-reward.co.uk Ltd may (at its sole discretion) either require such payment as a condition of your entry to the Course or refuse you entry to the Course.

Prices for each Course, where applicable, are correct at the time of publication. We reserve the right to change the prices at any time but changes will not affect registrations which have already been confirmed by E-reward.co.uk Ltd.

Each ticket covers attendance for one person only for the duration of the Course, and includes coffees, teas and light snacks during the lunch hour, as well as course materials.

All bookings are subject to value added tax (VAT) at 20%. You shall, on receipt of a VAT invoice from E-reward.co.uk Ltd, pay to E-reward.co.uk Ltd such additional amounts in respect of VAT as are chargeable on the supply of the Course.

Late payments

We understand and will exercise our statutory right to interest and compensation for debt recovery costs under the late payment legislation if we are not paid according to agreed credit terms. Under the Late Payment of Commercial Debts [Interest] Act 1998 – as amended and supplemented by the Late Payment of Commercial Debts Regulations 2002 (to incorporate the features of European Directive 2000/35/EC) – we are entitled to charge:

  • Debt recovery costs – £40 on debt up to £999.99; £70 on debt between £1,000 and £9,999.99.
  • Statutory interest – 8% above the Bank of England reference rate (fixed for the six-month period within which date the invoices became overdue).

Joining instructions

Final Course details will be emailed to delegates at least three weeks before the Course.

Course documentation

You will receive your course materials by email ahead of the Course.

Hotel accommodation

Delegates are responsible for their own accommodation.

Course cancellations and amendments

The Course programme is correct at the time of going to press.

E-reward.co.uk Ltd reserves the right to change the format, tutors, venue location and programme or any other aspect of the Course at any time and for any reason, whether or not due to a Force Majeure event, in each case without liability/refund.

‘Force Majeure Course’ means any event arising that is beyond the reasonable control of E-reward.co.uk Ltd including (without limitation) speaker or participant cancellation or withdrawal, supplier or contractor failure, venue damage or cancellation, health scares, industrial dispute, governmental regulations or action, military action, fire, flood, disaster, civil riot, acts of terrorism or war.

E-reward.co.uk Ltd reserves the right to change the date or cancel a Course at any time and for any reason without notice. Where circumstances force E-reward.co.uk to cancel a Course, E-reward.co.uk Ltd shall offer you the option of attending any rearranged Course that E-reward.co.uk Ltd chooses to organise.

If you do not wish to accept this offer, then you will (as your sole remedy) be entitled, at your discretion, to receive either a credit note or a refund in respect of your fees received by E-reward.co.uk Ltd for that particular Course.

However, where the Course is postponed for reasons due to a Force Majeure event, any of your fees received by E-reward.co.uk Ltd shall be applied to any rearranged or rescheduled Course and all these Terms and Conditions shall apply to any such transferred booking.

To the fullest extent permitted by the applicable law, E-reward.co.uk Ltd shall not be liable to you for any loss, delay, damage or other liability incurred resulting from or arising in connection with the cancellation or date change of the Course howsoever arising or any venue change.

For the avoidance of doubt, you acknowledge and agree that should the Course date be changed or cancelled, E-reward.co.uk Ltd is not liable for any travel or accommodation costs you may have incurred.

How we use your registration information

E-reward.co.uk Limited is a registered data controller under number Z7317541.

Your Course registration information will be added to our marketing database but will not be given to third parties without your prior consent.

E-reward.co.uk Ltd uses email addresses for its own purposes of keeping customers informed about Courses and providing product, service and information announcements.

If you supply us with your postal address online, you may receive periodic mailings from E-reward.co.uk Ltd with information on new products and services and upcoming Courses.

If you do not wish to receive information about products and services, please let us know by email (to paul@e-reward.co.uk).

Disclaimers

To the fullest extent permitted by the applicable law, E-reward.co.uk Ltd excludes:

  • all liability for loss, injury or damage to persons or property at the Course;
  • all indemnities, warranties, representations, terms and conditions (whether express or implied); and
  • any actual or alleged indirect loss or consequential loss howsoever arising suffered by you or any loss of profits, anticipated profits, savings, loss of business revenue, loss of business, loss of opportunity, loss of goodwill, or any other type of economic loss (whether direct or indirect).

If E-reward.co.uk Ltd is liable to you for any reason, its total liability to you in relation to the Courses (whether under these terms or conditions or otherwise) is limited to the amount of your fees received by E-reward.co.uk Ltd.

Views expressed by the Course instructors are their own. All advice provided at this course is for general guidance only. Any course delegates relying on information or advice given in the course of such training do so at their own risk.

E-reward.co.uk reserves the right to amend these terms and conditions from time to time. However, you will be subject to the terms and conditions in force at the time you submit your registration.

Further information

If you have any questions, please email us at paul@e-reward.co.uk