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

Transforming, Analysing, Connecting & Visualising Reward Data


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

Dates: 5-7 November 2024.

Location: Your PC via Zoom.

Duration: Three days, 9am-1pm (UK) ***Changed from a two-day course in 2022***

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

Designed for reward professionals who are using Excel on a day-to-day basis in their reward work but who want to gain a deeper understanding of the more sophisticated facilities offered in the compensation data area. This hands-on practical diploma 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.

Diploma

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

Criteria for attending

  • A good basic to intermediate understanding of Excel is required before attending the workshop. To get the most out of this diploma, Dianne recommends that:
  • Students are working with remuneration data in Excel on a regular basis in their work.
  • Students should have good navigational skills in Excel, and be able to select, copy, move, paste, edit and sort data with ease.
  • Students should be comfortable with working with large amounts of data and entering formulas.

Dianne says:

‘The ideal candidate for this diploma would already be familiar with some of the information covered on the agenda. But this course will greatly enhance their ability to analyse mountains of data in seconds with professional looking charts and Pivot Tables. Anyone with a good understanding of Excel will dramatically increase their productivity levels when working with remuneration data in Excel. This diploma is designed for compensation professionals who need to use Excel to perform complex job tasks such as compiling and analysing compensation data and performing calculations.’

Documentation

Delegates will receive:

  • PDF workbook with detailed (click-by-click) instructions on how to carry out every function demonstrated by Dianne during the class.
  • Excel files with all the training and exercise spreadsheets used during the class. These Excel files will be emailed to you in advance of the course.

Requirements

This diploma requires you use a Windows PC laptop (not Mac) with Microsoft Excel 2010, ideally Excel 2013 or later. We recommend that you use a mouse during the course to help with extensive on-screen navigation.

With this class you will:

  • Learn skills specific to compensation work not taught in any standard Excel course.
  • Save hours of work when you import, merge and format large amounts of data for analysis.
  • Learn how to analyse mountains of data in seconds with professional-looking charts and Pivot Tables.
  • Face your fear of regression analysis and easily create a pay policy line and salary predictions.
  • Perform complex calculations in minutes using the SUBTOTAL function.
  • Harness the power of VLOOKUP, HLOOKUP, IF, AND, OR and IFERROR functions.

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

After you complete this course, you will have the confidence and expertise in Excel to support your organisation’s pay analytics needs. The course will serve as a powerful refresher for the seasoned Excel professional who is already knowledgeable and will provide a solid foundation for the professional who is new to the job.


‘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 use Control E in Excel 2013 to separate first name and surname for the whole column at one time?

Find out other quick ways to separate columns, combine columns, and change case for the whole column at one time.

2. Did you know that you can press the F11 key to create a chart?

Find out other time-saving tips for creating great looking column charts, bar charts, line charts and pie charts.

3. Did you know that you can use the Pivot Table Report Filter function to create one pivot table per division or manager at the click of a button?

Find out how to harness the power of pivot tables for analysing and reporting on data at this course.

4. Did you know that you can enter today’s date in a worksheet by pressing Control and semi-colon or typing =TODAY()?

Find out how to master tricky dates in Excel, how to format them to sort correctly, and how to calculate age, length of service or time in job using one simple date formula at this course.

5. Did you know that you can prevent Mail Merge showing lots of decimals like this – 485.6789 – by using a simple ROUND formula in the Excel source data?

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 1: Transforming, Analysing, Connecting & Visualising Reward Data



SECTION A / B: CUSTOMISING QUICK ACCESS TOOLBAR / TRANSFORMING 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

Using Left, Mid and Right Formulas to extract data

Converting data from upper to proper case

Using Flash Fill

Formatting as currency or accounting format

Exercise A / B


SECTION C: ROUNDING NUMBERS, CUSTOM FORMATTING AND WORKING WITH DATES

Feedback on Exercise A / B

Rounding numbers using two different rounding formulas

Using Dollar formula for use with mail merge

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 Data, Text to Columns to create formatted dates

Using Networkdays to calculate working days between dates

Exercise C


SECTION D: USING SUM, LOOKUP AND IF FORMULAS TO VALUE PAY AND BENEFITS

Using Autosum formula to total costs

Using Vlookup / Hlookup formulas to value benefits – exact match

Using Vlookup formula to merge data from different sheets – exact match

Using Iferror formula together with Vlookup

Using Vlookup formula to look up merit increases – closest match

Using If formula for pay calculations

Moving /copying sheets

Exercise D


SECTION E: STATISTICS FOR REMUNERATION DATA

Feedback on Exercise D

Naming ranges

Using formulas to calculate:

  • Count / CountA
  • Countif
  • Mean / Average
  • Median
  • Mode
  • Lower quartile
  • Upper quartile
  • Percentile
  • Minimum
  • Maximum
  • Measures of variance

Exercise E


SECTION F: AGEING MARKET DATA, COMPARING TO MARKET DATA AND CONDITIONAL FORMATTING

Ageing market data

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

Exercise F


SECTION G: GRAPHING / CHARTING REMUNERATION DATA

Feedback on Exercise F

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 chart

Time-saving charting tips

Exercise G


SECTION H: GOAL SEEK

Using Goal Seek to achieve a desired result


SECTION I: PIVOT TABLES

Creating different types of pivot table using Count, Sum, Min, Max and Average

Using a pivot table to conduct an employment equity analysis on remuneration

Grouping data in a pivot table

Creating report filter pages in a pivot table

Formatting and revising the pivot table

Adding slicers to a pivot table

Creating and editing a pivot chart


SECTION J: SCATTER CHARTS AND REGRESSION ANALYSIS

Feedback on Exercise I

Creating an exponential scatter chart of employee and market data

Adding exponential trend lines to the scatter chart

Understanding significance of R-squared value

Writing the exponential trend line formulas

Creating a proposed pay midpoint line

Creating pay range minimums and maximums and adding to chart

Creating a linear scatter chart

Adding a linear trend line to the scatter chart

Predicting pay using linear trend line formula

Exercise J (Optional)


Virtual classroom 05/11/2024 - 07/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