HR Excel daily practice video tutorial

HR's daily practice of Excel

As an HR professional, do you have any of the following questions?

The data is disorganized and in a confusing format, making it impossible to effectively complete data analysis and processing;

Faced with various analysis requirements from leaders and various information investigations from related departments, I don’t know where to start;

Without mastering the relevant methods, I am tired of dealing with the preparation of various reports, which is time-consuming and laborious;

After analyzing the data and statistics, report submission often requires appropriate charts to present them, and there is a lack of chart knowledge and composition skills.

Course Catalog

00Courseware.zip

Section 00: What are the characteristics of this course?

Chapter 0 Introduction.mp4

Chapter 1 Establish a complete employee information data sheet – Content introduction.mp4

No. 2 Efficient HR Daily Data Statistics – Content Introduction.mp4

No. 3 Efficient labor contract and probation period data management – ​​Content introduction.mp4

Chapter 4 Batch production of HR related documents – Content introduction.mp4

Chapter 5 HR Common Functions and Practical Examples – Content Introduction.mp4

The 6th Comprehensive Human Resources Status Data Report – Content Introduction.mp4

Chapter 7 Training and Development Data Management – ​​Content Introduction.mp4

Chapter 8 Performance Appraisal Data Management – ​​Content Introduction.mp4

Chapter 9 Recruitment Data Management – ​​Content Introduction.mp4

Chapter 01: Establishing a complete employee information data table

Chapter 1-001-How to establish a complete personnel data table.mp4

Chapter 1-002-Creating a Complete Personnel Data Table (1).mp4

Chapter 1-002-Creating a Complete Personnel Data Table (2).mp4

Chapter 1-002-Creating a Complete Personnel Data Table (3).mp4

Chapter 02: Efficient HR Daily Data Statistics

Chapter 2-001-Quickly complete employee information related statistics (1).mp4

Chapter 2-002-Quickly complete employee information related statistics (2).mp4

Chapter 2-003-Quickly complete the personnel and labor cost statistics of each organization in a manufacturing department.mp4

Chapter 2-004-Quickly complete the personnel turnover statistics of each department from 2012 to 2013.mp4

Chapter 2-005-Quickly count employees' performance in the last 12 months.mp4

Chapter 2-006-Quickly create a bonus payment report for a specific date.mp4

Chapter 2-007-Quickly calculate the distribution ratio of employee performance.mp4

Chapter 2-008-Quickly calculate the performance appraisal ranking of employees within the department.mp4

Chapter 2-009-Creating a series of data statistics templates based on employee information tables.mp4

Chapter 03: Efficient Labor Contract and Probationary Period Data Management

Chapter 3-001-Trial period data management and extraction of birthday employee list.mp4

Chapter 3-002-Efficient Labor Contract Data Management (1).mp4

Chapter 3-003-Efficient Labor Contract Data Management (2).mp4

Chapter 3-004- Labor Contract Renewal Management and Creation of Renewal Contact Department Printing Template.mp4

Chapter 04: Batch production of HR related documents

Chapter 4-001-Batch production of appointment letters and splitting into independent documents.mp4

Chapter 4-002-Batch Printing of Labor Contract Termination Notices.mp4

Chapter 4-003-Mass production of work permits with photos.mp4

Chapter 4-004-Use of documents after mail merge.mp4

Chapter 05: Common HR functions and practical cases

Chapter 5-001-Understanding Cell References.mp4

Chapter 5-002-Calculation of special post allowances and seniority allowances (logical functions).mp4

Chapter 5-003-Common rounding problems in social security and provident fund calculations (mathematical functions).mp4

Chapter 5-004-Common Text Functions (Modify the Work ID Format, Extract Date of Birth, Split ID Number, Merge Multiple Cell Contents).mp4

Chapter 5-005-Vlookup function accurately finds employee information.mp4

Chapter 5-006-Vlookup function error causes and solutions.mp4

Chapter 5-007-Usage of Vlookup function fuzzy search (taking age segments, calculating seniority allowance, merging cells and filling blank data).mp4

Chapter 5-008-Match function precise search usage.mp4

Chapter 5-009-Extract the last level department (Index) according to the organizational structure based on the matching coefficient of grade and corresponding salary grade.mp4

Chapter 5-010-Multiple Conditions Matching Salary Adjustment Coefficient.mp4

Chapter 5-011-Job Coefficient Matching in Complex Situations.mp4

Chapter 5-012-Offset function (dynamic reference area to create a secondary drop-down menu).mp4

Chapter 5-013-HR related commonly used date functions.mp4

Chapter 5-014-Calculate the total number of salary months, employee length of service and years of service before joining the company.mp4

Chapter 5-015-Calculate the number of working days based on the start and end dates of the business trip.mp4

Chapter 5-016-Multi-condition counting and summing (Ifs type function) application-Statistical employee basic data.mp4

Chapter 5-017-How to add summary values ​​to the merged cell data report.mp4

Chapter 06: Comprehensive Human Resources Status Data Report

Chapter 6-001-Creating HR Monthly Report Data Statistics Template (1).mp4

Chapter 6-002-Creating HR Monthly Report Data Statistics Template (2).mp4

Chapter 6-003-Human Resources Status Inventory Data Report Template.mp4

Chapter 6-004-Create monthly resignation reason data report template.mp4

Chapter 6-005-Create a multi-angle dynamic data report template for each department's monthly resignation (1).mp4

Chapter 6-006-Creating a multi-angle dynamic data report template for monthly resignations in each department (2).mp4

Chapter 6-007-Create a multi-angle dynamic data report template for each department's monthly resignation (3).mp4

Chapter 6-008-Creating a company-wide monthly resignation multi-angle dynamic data report template (1).mp4

Chapter 6-009-Creating a company-wide monthly resignation multi-angle dynamic data report template (2).mp4

Chapter 07: Training and Development Data Management

Chapter 7-001-Rationally create a detailed list of employee training records and complete relevant analysis based on it.mp4

Chapter 7-002-Rationally create a detailed list of employee training records and complete relevant analysis based on it (data sorting of incorrect detailed lists and creation of correct detailed lists).mp4

Chapter 7-003-Rationally create a detailed list of employee training records and complete relevant analysis based on it (create an associative training name drop-down option).mp4

Chapter 7-004-Rationally create a detailed list of employee training records and complete relevant analysis based on it (list of employee training projects and statistics of the number of trainees at each level).mp4

Chapter 7-005-Rationally create a detailed list of employee training records and complete relevant analysis based on it (annual training project implementation data statistics).mp4

Chapter 7-006-Rationally create a detailed list of employee training records and complete relevant analysis based on it (quickly create an annual employee training file).mp4

Chapter 7-007-Automatically generate training sign-in sheet based on training registration form (pivot table modeling).mp4

Chapter 7-008-Automatically generate training sign-in sheet based on training registration form (function modeling).mp4

Chapter 7-009-Return the employee's skill level appraisal results in the past three years.mp4

Chapter 7-010-Extracting corresponding knowledge and skills based on job responsibilities and creating an evaluation model case sharing (background introduction).mp4

Chapter 7-011-Extracting corresponding knowledge and skills based on job responsibilities and creating an evaluation model case sharing (ideas for building a knowledge and skills summary table).mp4

Chapter 7-012-Extracting corresponding knowledge and skills based on job responsibilities and creating an evaluation model case sharing (smart borders and automatic numbering).mp4

Chapter 7-013-Extracting corresponding knowledge and skills based on job responsibilities and creating an evaluation model case sharing (extracting selected skills and automatically generating evaluation tables).mp4

Chapter 7-014-Extracting corresponding knowledge and skills based on job responsibilities analysis and creating an evaluation model case sharing (evaluation result setting).mp4

Chapter 7-015-Extracting corresponding knowledge and skills based on job responsibilities analysis and creating an evaluation model case sharing (generating an evaluation report).mp4

Chapter 08: Performance Appraisal Data Management

Chapter 8-001-Performance Appraisal Results Data Table Organization and Routine Statistics.mp4

Chapter 8-002-Creating a monthly performance appraisal data visualization report (overall ideas and basic table construction).mp4

Chapter 8-003-Creating a monthly performance appraisal data visualization report (data preparation).mp4

Chapter 8-004-Creating a monthly performance appraisal data visualization report (data visualization).mp4

Chapter 8-005-Creating a monthly performance appraisal data visualization report (report integration and improvement).mp4

Chapter 8 - 006-2017 Annual Performance Routine Data Visualization Report (Case Background and Data Format Conversion).mp4

Chapter 8 - 007-2017 Annual Performance Routine Data Visualization Report (Data Preparation 1).mp4

Chapter 8-008-2017 Annual Performance Routine Data Visualization Report (Data Preparation 2).mp4

Chapter 8 - 009-2017 Annual Performance Routine Data Visualization Report (Visualization Report-High Performance Output Ratio).mp4

Chapter 8 - 010-2017 Annual Performance Routine Data Visualization Report (Visualization Report - Low Performance Output Ratio and Performance Rate Ranking Performance Range Percentage).mp4

Chapter 8 - 011-2017 Annual Performance Regular Data Visualization Report (Visualization Report - Monthly Average Performance Comparison of Each Department and Total Data Report).mp4

Chapter 09: Recruitment Data Management

Chapter 9-001- Comprehensive recruitment data management and data reporting (overall management ideas).mp4

Chapter 9-002-All-round recruitment data management and data reporting (recruitment application summary table construction and subsequent application).mp4

Chapter 9-003-All-round recruitment data management and data reporting (construction of candidate information table and subsequent application during the interview stage).mp4

Chapter 9-004-All-round recruitment data management and data reporting (recruitment cost table organization and data report integration).mp4

Chapter 9-005-Thoughts on recruiting quality analysis based on 2017 performance data.mp4

No. 10: Free live class

20190326 Live-1-Establish a complete employee information table and correct data organization method.mp4

20190326 Live-2-Practical Exchange-Discussion on Full-Process HR Personnel Information Management.mp4

20190409 Live-Create Human Resources Status Inventory and Monthly Resignation Reasons Data Report.mp4

20190409 Live Broadcast - Layoff-related Practices.mp4

20190416 Live-Discussion on the Practical Rescission and Termination of Labor Contracts.mp4

20190416 Live-Typical Application of Mail Merge in HR.mp4

20190423 Live-Things to pay attention to when formulating HR rules and regulations.mp4

20190423 Live-Various uses of Vlookup function and causes of errors.mp4

20190611 Live-Making training schedule, extracting cross-year performance data and allocating labor costs.mp4

20191021 Live-Employee Information Management and Recruitment Module Data Analysis Should Be Done This way.mp4

20191118 Live-Efficient Labor Contract Management, Allowance Calculation and Matching Employee Salary Coefficients.mp4