Microsoft Excel specialists

Excel training courses

See dates of all courses
Contact us

  


Related Courses
More essential spreadsheets for HR




  WHAT YOU GET

Course manual
& Excel files

40  minutes’
hotline time

Excel expertise
28 years’
specialisation

Job-related courses

Essential spreadsheets for HR

     Duration: 2 days  
     Format: Interactive seminar with quizzes  
   Arrival and refreshments: 8:45am  
   Start 9:00am  
   Finish: 4:30pm  
  YOUR POWER-PACKED AGENDA

Import HR data from other systems

  • Import HR data from text files into Excel.
  • Solve common import issues such as keep leading zeroes and import dates correctly.
  • Keep the import settings for a repeat next month.
  • Append formula columns to the imported data.
  • Use text functions to fix common problems with imported HR data such as inconsistent capitalisation and extra spaces.
  • Split employee first and last names into separate columns.

Manipulate HR databases

  • Complete missing entries.
  • Quickly swap rows and columns.
  • Perform simultaneous mathematical operations on a data range.
  • Convert textual numbers into numbers.
  • Format long codes for easy reading.

Make HR workbooks easy for others to use

  • Use conditional formatting to highlight the employees that are running low on sick leave.
  • Use Data Validation to minimise errors when completing staff leave requests.
  • Create a cell drop-down list of employees.
  • Use worksheet protection to prevent users from entering data into formula cells on a timesheet.

Use lookup and SUMIF functions

  • Use VLOOKUP to report on staff quality rating.
  • Find the correct bonus rate with a banded lookup.
  • Solve common problems with lookup functions.
  • Discover a useful function that eliminates monthly formulas editing.
  • Use INDEX and MATCH to return entries to the left of the search column.
  • Report on remuneration data with a two dimensional lookup.
  • Use SUMIF to aggregate employee leave data.
  • Extend SUMIF to summarise remuneration data that requires multiple criteria.

Adopt best practices for workbook and sheet layout

  • Discover how to arrange the top, side and sheet titles on every sheet.
  • Format quickly with styles.
  • Clearly identify inputs, processes and outputs.
  • Keep the settings for a repeat next month.
  • Apply sheet name, colour and flow conventions.
  • Discover the six data-sheet types.
  • Add the standard Guide and Params sheets to every workbook and understand their benefits.

Work with tables of data

  • Filter staff data to show records for a particular employee or department.
  • Put filters to work applying formats and deleting unwanted data.
  • Subtotal leave data by country and city.
  • Locate and remove duplicates from staff training data.

Build formula and PivotTable staff remuneration reports

  • Use formulas to build a standard report in any layout.
  • Quickly build ad-hoc reports with a PivotTable.

Calculate with date & time

  • Total hours and minutes worked.
  • Multiply time worked by a rate.
  • Calculate the number of working days required to complete a project.
  • Create a list of working days in a month.
  • Calculate age and tenure accurately.
  • Calculate completed years of employment to determine long service leave.

Mail-merge with Word

  • Create standardised salary confirmation letters with Word from an Excel table of employees.
  • Solve formatting problems with merged numbers and dates.

Build organisation charts

  • Discover the inbuilt organisation chart feature.
  • Modify an existing organisational chart when an employee¡¯s position changes.

The above agenda covers many different functions. They include: INDEX, SUMIF, COUNTIF, IF, VLOOKUP, LEFT, MID, TRIM, SEARCH, NETWORKDAYS, DAY, MONTH, YEAR, TEXT and many more. It also covers many shortcut tips and tricks that can be used in everyday spreadsheet work.


Prerequisites

Those who have been using spreadsheets for some time and are quite familiar with the basics of formula construction.

While some sections of this course could fairly be described as 'advanced' we don't assume you know any of it. There will be other delegates with just the same level of ability as you have.


  LEARN IN A POSITIVE ENVIRONMENT
 
  • Be assured the advice you get is well-proven and utilised.
  • Feel free to ask questions.
  • Get time away from the office and constant interruptions.
  • Enjoy a friendly environment where you don’t feel you’re holding others up.
  • Instantly apply what you’ve learned on return to work.

Presenters

AbleOwl has a number of different presenters, all of whom are Excel specialists.

See dates of all courses  

  COURSE CANCELLATION POLICY
 
A course booking is considered final when received in writing (online, email, fax or post) by AbleOwl, and can be cancelled up to ten working days before the course. After that, payment is expected in full and no refund will be given. All cancellations must be notified in writing, i.e. post, fax or email. Another delegate may be substituted at any time.