Best-practice multiple-workbook structure

• Hear the pros and cons of multi-workbook models.
• Structure file-links into Source and Destination areas.
• Understand the importance of using range names in your file-links.

Implement an employee performance application

• Adapt this professional balanced-scorecard Excel application for your organisation.
• See how data is stored in an Access file, which allows multi-user access. No knowledge of Access is needed.
• Learn how to manage logons that allow employees to see only their own or subordinate data.
• Discover Excel's MS Query to extract selections of the data for reporting.
• Use the Excel 2010/2013 Power Query add-in to extract data for reporting.

Analyse leave data with advanced PivotTable techniques

• Create a PivotTable on employee performance from an external data source.
• Stop formats disappearing when you refresh your PivotTables.
• Learn to group PivotTable data by division, region, month or year. Add subtotals to the new groups.
• Gain further insights with a PivotChart.
• Split a PivotTable across multiple sheets to create a custom report for each team leader.
• Perform more powerful analysis by adding calculations to PivotTables.
• Use Slicers in Excel 2010/2013 to interact with a PivotTable.

Create succinct, readable dashboard reports

• Learn the rules of good dashboard design.
• Report on key HR metrics by combining tables and charts in one concise report.
• Interact with a dashboard report using dynamic drop-down lists.
• Create an HR scorecard using conditional formatting, on-sheet indicators, and Sparklines.
• Highlight KPI performance with a traffic light chart.
• Create a map sheet to automatically identify new or missing data items.

Use a form to make it easy to fill in leave requests

• Create a basic online leave application form that feeds directly into an Excel Table.
• Create comprehensive well-designed, easy-to follow on-sheet forms for leave requests.
• Create inputs, option buttons and check boxes.
• Learn to create a template master for your forms.
• Apply protection to your spreadsheet to avoid mistakes from end users.
• Consolidate the results from many forms into a single workbook.

Plan for future staffing requirements with forecasting

• Add a linear trendline to a chart to estimate future staff turnover.
• Use an R2 test to determine how well a trendline on absenteeism correlates with the data.
• Apply a non-linear trendline to data that doesn't show a steady increase or decrease.
• Generate forecasts and R2 values with the LINEST function.

Prerequisites

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

While the two-day Essential spreadsheets for Human Resources course is not a prerequisite for More essential spreadsheets for Human Resources, you should be familiar with the material outlined in the agenda for the two-day course if you plan to attend More essentials.

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