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.

• 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.