Excel Data Analysis for
Human Resources and Admin Professionals
Microsoft Excel as a savvy tool for enabling quantitative and research skills. HR data is either entered manually in Excel or downloaded from HRIS. For an evidence-based approach, it must be analyzed and summarized to reveal key indicators. Ordinarily, gleaning action-ready information from a large dataset could be challenging for many. This course focuses on Excel tools that will add to your perceptiveness and insight into data for effective decision making.
Learn to create and maintain well formatted data in Excel. Use the advance tools and complex criteria to filter and query the data. Create and apply custom criteria for sorts and nested sub-totals. Use PivotTable to create complex, well formatted summary reports effortlessly. Apply formula based conditional formatting to sort and filter visually. As well, create a dynamic HR dashboard in the class to reveal and monitor key indicators. In short, on completion of this course, you will be able to perform many analytical tasks with ease, speed, and a great level of confidence. For more details, please click the link below to view the course outline in PDF.
Prerequisites: Successful completion of our Formulas and Functions for HR course. Proficiency in Windows environment with excellent mouse skills.
Software Version: Microsoft Excel 365/2021/2019/2016 for Windows PC.
Course Duration: One Day.
Class Times-1: 9:00 AM to 5:00 PM Pacific Time (Los Angeles, Vancouver).
Class Times-2: 9:00 AM to 5:00 PM Eastern Time (New York, Toronto).
Course Pace and Style: Medium-to-Fast; Hands-on, Intensive, Interactive.
Course Delivery Format: Live Online Instructor-led. We do not train by recorded videos.
Course Fee Per Attendee: USD 385 (includes course material cost).
Course Outline Download Link: Excel Data Analysis for HR
Cancellation & Date Change Policy Link: Cancellation Policy
Technical Requirements for Taking Online Courses: OnLine Tech Requirements
This course is pre-approved for:
– 6.5 PDCs by SHRM towards SHRM-CP® or SHRM-SCP® recertification activities.
– 6.5 CPD hours by CPHR-BC & Yukon, and CPHR-Manitoba.
Course Outline
What is covered in the course?
- Excel as a List Database. Looking at a typical download from HRIS.
- Advantages of formatting HR data as an Excel Table.
- Filtering HR data with multiple options including colour.
- Employing Multi-Level and Horizontal sorts for analysis.
- Summarizing HR data with Subtotal command.
- Creating a Custom List for a Custom Sort.
- Extracting information from HR database using a complex query (Advanced Filter).
- Trend Analysis with forecasting for workforce planning.
- Visual analysis of training budget data using Sparklines.
- Employing Data Validation rules to reduce errors and frauds.
- Importing Data into Excel from other data sources.
- Using the TRIM Function to cleanup HR database.
- Using Conditional Formatting to monitor data for compliance.
- Using Advanced Conditional Formatting to sort and filter data visually.
- Using PivotTable to track key indicators and summarize HR data.
- Creating a PivotTable report from scratch.
- PivotTable-Drilling down to view details.
- PivotTable-Creating a Calculated Field.
- PivotTable-Displaying data as percentage of Column or Parent Total.
- PivotTable-Applying Ranking for analysis.
- PivotTable-Grouping numeric and date values for better insight into data.
- PivotTable-Creating a Pivot Chart.
- PivotTable-Formatting Reports.
- PivotTable-Creating Report Filter pages.
- Creating a Dynamic HR Dashboard for key indicators.
