Course Details
Course Code: HRL6211; Instructor-led
Audience
- Human Resource Professionals and Executive who do
- Create HR reports
- Deal with various types of data
- Handle bonus and salary computation
- Perform “What If” analysis
- Those keen to understand and utilize Microsoft Excel in human resource management
Prerequisites
Basic Microsoft Windows knowledge is essential with the following pre-requisites:
- Able to switch between task applications
- Able to “Create”, “Save”, “Open” and “Print” spreadsheet
- Able to use “Undo” or “Redo” function
- Able to print a spreadsheet with headers and footers added
- Able to create simple formulas (additional, subtraction, multiplication and division)
- Able to create simple functions (Sum, Average, Count, Max & Min)
Methodology
- This program will be conducted with interactive lectures and hands-on application for each participant.
- The Instructor will demonstrate all techniques using simple but realistic HR examples that enable participants apply what they learnt into their work.
- Sessions will include discussions, practical class work and Q & A session.
- Each participant will be assigned to a PC.
Course Objectives
Upon completion of this program, participants will:
- Have a better comprehension on how functions work in Excel
- Be better able to handle text data
- Maximize the benefits of filters
- Be more confident in creating Excel reports
- Know how to use validation to ensure database integrity
- Be able to make fast decision with the use of the logical function
- Learn how to extract relevant data with LOOKUP functions
- Ensure they obtain data consistency with functions
- Analyzing Data with Statistical and Math Functions
- Work with Date Functions
- Know how to explore various possibilities with “What IF analysis”?
- Merging Data from Multiple Worksheets
- Know how to analyzing data with pivot table
- Identify the differences between custom formatting vs. conditional formatting
- Gain confidence and learn valuable tips
Outlines
Module 1: Formula Guidelines
- Where To Place $ Sign In Formulas
- Relative Addressing
- Absolute Addressing
- Mixed Addressing
Module 2: Eliminate Unwanted Data
- Working with Tables
- Data Sorting
- Sorting Data in a Table
- Working with Filters
- What is an AutoFilter?
- Adding a Total Row
- Removing Duplicate Records/Data
- Cleaning up Duplicate Records
Module 3: Extract Relevant Data With LOOKUP Functions
- What Are Lookup Functions?
- VLOOKUP or HLOOKUP Function
- Index & Match Function
- Match Function
- Index Function
Module 4: Making Fast Decision With Logical Function
- Using Logical Function
- IF Function
- IFERROR Function
Module 5: Ensuring Data consistency With Functions
- Using Text Function
- UPPER Function
- LOWER Function
- PROPER Function
Module 6: Using Validation To Ensure Database Integrity
- What Is Data Validation?
- Validating Your Data
- Types of Validation
- Error Alert
Module 7: Analyzing Data With Statistical And Math Functions
- Averagea Function
- Countif vs. Countifs
- COUNTIF Function
- COUNTIFS Function
- Averageif vs. Averageifs
- AVERAGEIF Function
- AVERAGEIFS Function
- Sumif vs. Sumifs
- SUMIF Function
- SUMIFS Function
Module 8: Working With Date Functions
- Calculation Between Dates
- Date Calculation
- Use DATE Function
- Calculate a Period of Time between Two Dates
Module 9: Exploring Various Possibilities With “What IF Analysis”?
- What Is “What IF Analysis”?
- Working with Scenarios
- Working with Data Table
- Working with Goal Seek
- Working with Solver
Module 10: Merging Data From Multiple
- What is Data Consolidation?
- Ways To Consolidate Data
Module 11: Analyzing Data With Pivot Table
- What is Pivot Table?
- Common Pivot Table Practice
- Creating Pivot Table
Module 12: Working With Text Files
- Split Names By Using The Convert Text To Columns Wizard
- Working Text Function
- Remove Extra Spaces with the TRIM Function
- Remove Non-Printing Characters with the Clean Function
- Combining Text using Concatenate Function
Module 13: Useful Tips
- Paste Special Options
- Paste Value
- Paste Link
- Paste Transpose
- Be a Pro with Effective Formatting
- Conditional Formatting
