Excel for HR and Administration Training Course

Duration 5 days 10 modules · 33 contact hours
Level Intermediate Certificate on completion
Who attends HR Administrator, HR Officer and others
Fee from USD 1,100 KSh 95,000 for Kenyan organisations
Where 52 cities + online See dates by city
Next start 16 Nov 2026 Course code SD-OA-040

Before you join: Participants should already be comfortable entering data, formatting worksheets, using basic formulas such as SUM and AVERAGE, and saving Excel files.

Dates & prices

Two start dates a month in every city, and live online on weekdays or weekends. Fees are per participant; classroom fees include tuition, materials, lunch and refreshments, and the certificate.

Suggest a date or city

Showing the next six months. Later dates, other cities and private deliveries for your team are available on request.

Course overview

HR and administration teams often manage employee lists, leave records, payroll changes, recruitment costs, supplier schedules and management reporting from spreadsheets assembled over time. When formulas are inconsistent, identifiers do not match, or access is poorly controlled, routine updates produce unreliable totals, missed deadlines and unnecessary exposure of personal data. The cost appears in delayed payroll corrections, weak workforce decisions and administration staff spending hours reconciling files rather than managing exceptions.

This course teaches participants to design controlled workbooks, structure HR and administrative data, and use intermediate Excel functions to calculate and reconcile key records. Participants build lookups, date logic, absence and leave trackers, payroll-change registers and recruitment cost analyses, then turn source data into PivotTable reports and decision-ready charts. The syllabus also addresses workbook controls, confidentiality, formula auditing and repeatable monthly reporting processes. Participants learn to explain KPI definitions, check exception lists and present clear results to managers.

Teaching combines instructor demonstrations, guided case work, group problem-solving and realistic HR and administration scenarios. Participants work with examples such as employee master data, leave balances, payroll amendments and monthly workforce reports. They leave with a reusable HR and administration workbook pack, including templates, formulas, controls and an application plan. The course is delivered over five days in a classroom in 50+ cities or live online, with a certificate of completion awarded to participants who complete the course.

The course is particularly suited to HR officers, HR administrators, office administrators and coordinators who already use Excel but need more reliable methods for managing records, producing reports and checking operational data.

What you will be able to do

  • Design controlled HR and administration workbooks using Excel Tables, named ranges and worksheet protection
  • Build validated employee and administrative data sets using Data Validation and cleaning functions
  • Construct intermediate Excel formulas for headcount, leave, payroll-support and cost calculations
  • Retrieve and reconcile employee records using XLOOKUP, INDEX and MATCH methods
  • Calculate absence, leave and service-date measures using date and working-day functions
  • Produce PivotTable reports and charts for workforce, recruitment and administrative reporting
  • Create repeatable monthly reporting processes using Power Query, refresh instructions and exception checks
  • Apply data confidentiality, formula auditing and change-control methods to HR spreadsheets

Benefits of attending

For you

  • Produce monthly headcount, absence and recruitment reports with less manual consolidation
  • Build leave and payroll-change trackers that expose missing approvals and exceptions
  • Reduce formula errors through named ranges, validation rules and audit checks
  • Present workforce measures using clear PivotTables, charts and KPI definitions
  • Create reusable Excel files that demonstrate stronger operational reporting capability

For your organisation

  • More accurate HR and administration records through controlled data entry and validation
  • Earlier detection of payroll, leave and employee-data exceptions before submission deadlines
  • Faster monthly reporting through structured source data, PivotTables and refresh procedures
  • Reduced dependence on individual spreadsheet owners through documented workbook design
  • Better protection of sensitive employee information through access and change controls

Course modules

10 modules over 5 days

Participants can design structured Excel workbooks that remain readable, maintainable and controlled.

  • Workbook purpose, users and decisions defined before building any worksheet
  • Structured tables, consistent headers and controlled input areas for maintainability
  • Apply Excel Tables for expanding ranges, filters and formula propagation
  • Freeze panes, grouping and sheet views for readable operational files
  • Create named ranges that make HR formulas auditable and reusable
  • Protect formula cells and distinguish input, calculation and output areas
  • File naming, version control and archive rules for administrative records

Participants can create dependable data-entry sheets and prepare source records for analysis.

  • Build employee master-data fields using unique identifiers and controlled categories
  • Data Validation lists restrict department, grade, location and employment-status entries
  • Configure input messages and error alerts for dependable record capture
  • Identify duplicates with conditional formatting and COUNTIFS exception checks
  • Clean imported records using TRIM, CLEAN, SUBSTITUTE and Text to Columns
  • Standardise dates, names and codes before reporting or payroll reconciliation
  • Document field definitions, source systems and refresh responsibilities in a data dictionary

Participants can build and audit formulas for routine HR and administrative calculations.

  • Relative, absolute and mixed references control copied formula behaviour
  • Build IF, IFS and AND logic for policy-based classifications
  • SUMIFS and COUNTIFS calculate headcount, absence and recruitment totals
  • Calculate tenure with DATEDIF and anniversary dates with EDATE
  • Handle missing values using IFERROR, ISBLANK and explicit exception labels
  • Use ROUND, ROUNDUP and MROUND for controlled payroll-support calculations

Participants can match records across files and derive useful information from text and dates.

  • XLOOKUP retrieves department, manager and grade details from master lists
  • INDEX and MATCH provide a compatible alternative for older Excel versions
  • Combine LEFT, RIGHT, MID and TEXT for employee-code parsing
  • Extract first and last names using TEXTBEFORE and TEXTAFTER
  • WORKDAY and NETWORKDAYS calculate service deadlines and working-day intervals
  • Audit lookup errors caused by unmatched keys, spaces and duplicate identifiers

Participants can build controlled trackers for leave, absence and attendance exceptions.

  • Design absence registers with employee identifiers, dates, types and approvals
  • Calculate leave balances from entitlement, carry-forward, taken and adjustments
  • Use overlapping-date logic to identify absences within reporting periods
  • Conditional formatting flags expiring leave, missing approvals and threshold breaches
  • Create holiday calendars for NETWORKDAYS calculations and staffing plans
  • Filter exception lists for follow-up with managers and payroll teams

Participants can prepare reconciled payroll-support records and administrative cost trackers.

  • Build payroll-change registers for starters, leavers, allowances and deductions
  • Reconcile HR input against payroll extracts using identifiers and variance checks
  • Calculate prorated salary changes using effective dates and working days
  • Apply reason codes and approval status to controlled change requests
  • Create recruitment cost trackers using SUMIFS, dates and budget categories
  • Produce supplier payment schedules from purchase orders, invoices and due dates

Participants can summarise large HR and administration data sets using PivotTables.

  • Create PivotTables from structured HR data without altering source records
  • Group PivotTable dates by month, quarter and year for trends
  • Configure counts, distinct counts and percentages for workforce measures
  • Build calculated fields carefully and validate results against source totals
  • Use slicers and timelines to filter department, location and reporting period
  • Refresh PivotTables and diagnose stale ranges, blanks and duplicate records

Participants can produce concise, accurate management reports from HR and administrative data.

  • Select charts that match headcount, absence, recruitment and budget questions
  • Build KPI cards for headcount, turnover, time-to-hire and absence rate
  • Calculate turnover and absence rates with documented numerator and denominator rules
  • Apply chart titles, data labels and axis scales that prevent misreading
  • Use conditional formatting to highlight variances against targets and prior periods
  • Design one-page management reports with clear hierarchy and refresh dates

Participants can protect sensitive workbook content and check data before distribution.

  • Classify HR workbook data by sensitivity, retention and permitted access
  • Apply worksheet protection, workbook passwords and restricted editing ranges
  • Maintain change logs for pay, leave and employee-record amendments
  • Separate source data, calculations and reports to reduce accidental changes
  • Conduct formula audits with trace precedents, trace dependents and error checking
  • Set review checkpoints before distributing people data to managers

Participants can improve recurring Excel reporting processes and plan workplace application.

  • Import recurring files through Power Query with documented transformation steps
  • Record simple macros for repeated formatting and report-preparation tasks
  • Create refresh instructions for recurring monthly HR and administration reports
  • Prioritise workbook improvements using impact, effort, risk and ownership
  • Draft a 30-day application plan for priority workplace spreadsheets
  • Define success measures, review dates and support needed for adoption

Exercises and examples are adapted to each cohort's sectors and roles.

What you take back to work

  1. HR and administration workbook design checklist
  2. Employee master-data validation template
  3. Leave and absence tracker model
  4. Payroll-change reconciliation checklist
  5. PivotTable management report template
  6. 30-day spreadsheet application plan

Who should attend

  • HR Administrator — needs reliable employee records, leave trackers and monthly reports
  • HR Officer — prepares workforce data, payroll changes and management information
  • Office Administrator — manages operational records, schedules, costs and administrative reporting
  • HR Coordinator — consolidates recruitment, onboarding and employee data from multiple sources
  • Payroll Liaison Officer — checks HR changes and reconciles information before payroll processing
  • Executive Assistant — prepares accurate administrative trackers and reports for senior managers

Skills and tools covered

HR spreadsheet designData validationExcel formula buildingWorkforce reportingPayroll reconciliationWorkbook controls

Tools and standards

Microsoft Excel, Excel Tables, PivotTables, Power Query

How the course runs

Training methodology

Short instructor briefings introduce each Excel method before participants apply it to HR and administration scenarios. Guided case work uses employee master data, leave records, payroll amendments, recruitment costs and management reports. Group exercises focus on checking assumptions, interpreting exceptions and agreeing report definitions. Simulations require participants to identify formula errors, unmatched records and reporting risks. Trainers provide feedback on workbook structure and reporting choices, followed by individual application planning for participants' own recurring spreadsheets.

Requirements and prerequisites

Participants should already be comfortable entering data, formatting worksheets, using basic formulas such as SUM and AVERAGE, and saving Excel files. Experience maintaining HR, payroll-support or administration records is useful but not essential. The course assumes access to a desktop version of Microsoft Excel, preferably Microsoft 365 or Excel 2021. No programming, VBA, data science, finance qualification or advanced statistical knowledge is required. Participants do not need prior experience with PivotTables, Power Query or HR analytics.

A typical training day

08:30 – 10:30First session
10:30 – 10:45Refreshment break
10:45 – 12:30Second session
12:30 – 13:30Lunch and networking
13:30 – 15:00Third session
15:00 – 15:15Refreshment break
15:15 – 16:30Workshop and daily review

Live online deliveries follow the same structure in East Africa Time, with shorter screen blocks and longer breaks.

What the fee includes

  • Instruction by a practitioner facilitator
  • Full course workbook and materials
  • Exercise files, templates and case studies
  • Refreshments and lunch on classroom deliveries
  • Certificate of completion
  • Post-course application plan

Frequently asked questions

You should be able to enter data, format a worksheet and use simple formulas such as SUM and AVERAGE. The course teaches PivotTables, lookup functions, validation, Power Query and workbook controls from an intermediate starting point.

Bring a laptop with the desktop version of Microsoft Excel, preferably Microsoft 365 or Excel 2021. A Windows laptop is preferred because Power Query and macro features receive attention during the course.

It is designed for HR administrators, HR officers, office administrators, coordinators, executive assistants and payroll liaison staff. It is most useful for people who regularly maintain records or prepare reports from HR and administrative spreadsheets.

The Excel methods are taught through HR and administration cases rather than generic sales or finance examples. Participants work with employee data, leave balances, payroll changes, recruitment costs, management reporting and confidentiality controls.

Participants can use the templates and methods to improve employee master files, absence trackers, payroll-change registers and recurring reports. The final module produces a 30-day application plan focused on priority workplace spreadsheets.

Training runs from 08:30 to 16:30 each day, providing about 6.5 contact hours daily. Time is divided across ten modules covering workbook design, formulas, HR trackers, reporting, controls and recurring-report improvements.

Questions about this course?

Ask about dates, fees, group bookings or running it for your team. Our training team replies by email.

Ask on WhatsApp info@skillsetdevelopment.com

More courses in Office Administration

Office Administration AI for Office Administration Training Course 5 days · from USD 1,100 · next 16 Nov Office Administration Administrative Operations and Coordination Training Course 5 days · from USD 1,100 · next 16 Nov Office Administration Administrative Organisation and Communication Training Course 5 days · from USD 1,100 · next 16 Nov Office Administration Advanced Administrative Skills Training Course 5 days · from USD 1,100 · next 16 Nov Office Administration Executive Assistant Masterclass Training Course 5 days · from USD 1,100 · next 16 Nov Office Administration Executive Secretarial Skills Training Course 5 days · from USD 1,100 · next 16 Nov Office Administration Executive Secretary and Personal Assistant Training Course 5 days · from USD 1,100 · next 16 Nov Office Administration Executive and Personal Assistant Skills Training Course 5 days · from USD 1,100 · next 16 Nov