CFC1003: Training on Advanced Excel for Human Resources (HR) Management:

Onsite Price

Ksh: 112,000.00

$: 1,120.00

Online Price

Ksh: 64,000.00

$: 640.00

Duration:

5 Days

Description

Day 1: Advanced Data Management

Lesson 1: Data Validation and Conditional Formatting

Creating drop-down lists with Data Validation.
Setting up input messages and error alerts.
Applying Conditional Formatting rules.
Using formulas in Conditional Formatting.

Lesson 2: Advanced Filtering and Sorting

Using Advanced Filter to create complex criteria.
Custom sorting options (by color, by custom list).
Using the Subtotal function to summarize filtered data.

Lesson 3: Excel Tables and Structured References

Creating and managing Excel Tables.
Using structured references in formulas.
Leveraging Table features for dynamic data ranges.

Day 2: Advanced Formulas and Functions

Lesson 4: Lookup and Reference Functions

Using VLOOKUP, HLOOKUP, and XLOOKUP.
Understanding INDEX and MATCH for more flexible lookups.
Applying INDIRECT for dynamic references.
Using OFFSET for creating dynamic ranges.

Lesson 5: Date and Time Functions

Using DATE, YEAR, MONTH, DAY functions.
Calculating workdays with NETWORKDAYS and WORKDAY.
Using EDATE and EOMONTH for date calculations.
Calculating tenure and time periods.

Day 3: Pivot Tables and Dashboards

Lesson 6: Creating and Customizing Pivot Tables

Creating Pivot Tables from HR data.
Customizing fields and layouts.
Grouping data (by dates, numbers).
Using calculated fields and items.

Lesson 7: Pivot Charts and Dashboards

Creating Pivot Charts.
Designing interactive HR dashboards.
Using slicers and timelines for dynamic data filtering.
Best practices for dashboard design.

Day 4: Power Query and Data Analysis

Lesson 8: Introduction to Power Query

Accessing Power Query in Excel.
Importing data from various sources (Excel, CSV, databases).
Basic data transformations (filtering, sorting, merging).
Loading transformed data into Excel.

Lesson 9: Advanced Data Analysis with Power Query

Combining queries (merge and append).
Creating custom columns and conditional logic.
Grouping data and performing aggregations.
Using Power Query formulas (M language).

Day 5: Automation with Macros and VBA

Lesson 10: Introduction to Macros

Recording basic macros.
Running and editing recorded macros.
Understanding the VBA Editor interface.

Lesson 11: Basic VBA Programming for HR

Writing simple VBA scripts.
Using loops and conditional statements.
Creating user-defined functions (UDFs).
Automating repetitive HR tasks (data entry, report generation).

Description

Learn to control data entry and highlight important information.
Master complex data filtering and sorting techniques.
Understand the benefits of using Excel Tables for HR data.
Learn to retrieve and reference data efficiently.
Master functions to handle dates and times effectively.
Summarize and analyze large datasets with Pivot Tables.
Visualize data and create interactive dashboards.
Understand Power Query for data extraction and transformation.
Perform advanced data analysis and transformations.
Learn to record and use macros to automate tasks.
Automate HR tasks with basic VBA programming.

General Notes

GENERAL NOTES
i. The clients have many options on where they would wish to have the training
ii. The client should communicate their preferred training venue at least seven days before the training date failure to which the training shall take place in Nairobi.
iii. This course is delivered by our seasoned trainers who have vast experience as expert professionals in the respective fields of practice. The course is taught through a mix of practical activities, theory, group works and case studies.
iii. Training manuals and additional reference materials are provided to the participants.
iv. Upon successful completion of this course, participants will be issued with a Chania finance consultancy certificate.
v. The onsite training fee covers tuition fees, training materials, tea at 10am, lunch, tea at 4pm, training venue and a certificate on completion. Accommodation and travel may be arranged for our participants upon request.
vi. The online training fee covers tuition fees, training materials, and a certificate on completion.