Entering Data in Excel – Settings
For cleaner data, we need to define what are acceptable values in each of the fields. We do this in the Settings sheet.
DO NOT CHANGE COLUMN HEADERS OR FIELD NAMES IN ROW 6. YOU CAN CHANGE IN ROW 5 AND THEN MODIFY NAMES IN POWER BI. EMAIL [email protected] FOR ASSISTANCE.
CHANGING FIELD NAMES WILL BREAK THE POWER BI REPORT
Enter the data for Gender, Hire Source and Ethnicity fields.
Enter list of Office Locations and the City/State/Country information.
Enter list of Departments and Job classification fields (Job Type, Job Category and Job Level).
Enter list of Job Titles
Enter list of Exit types and Exit reasons you want to capture from employees who are leaving the company.
Enter Skill levels and Skills relevant for the company.
By Default, Skill levels are categorized as None (no skill), Low, Medium and High.
Enter which days of the week are weekends and which days are company holidays. For weekends, just enter 1 next to the day. 0 represents a working day and not a weekend.
Enter list of reasons why an employee’s profile will change. For example, when an employee gets promoted, gets a salary increase, or changes teams.
Entering Data in Excel – Skill profiles
Enter the list of Skill profiles in your company.
A skill profile is nothing but a combination of skills. An employee in a certain role would be expected to have a certain skill level in a set of skills. For example, Finance Analysts who are new or junior may be expected to have low level of skills while experienced Finance Analysts might be expected to have Medium or High level of skills.
Define a set of skill profiles for your company and enter them. For each Skill profile, Select None/Low/Medium/High for each skill in the list.
By Default, you can enter up to 50 Skill profiles.
Entering Data in Excel – List of Employees
Enter list of all employees in your company.
click on image to see full size
If you need historical reporting, please make sure that employees from the past are also entered.
- Each employee should have a unique Employee Number.
- Each Employee should have a start date (date when they joined the company) and Date of birth (used for reporting on age)
- Gender and Ethnicity are used for Diversity reporting
- Office Location and Department fields are used in reporting within specific departments and locations.
- Jobs can be classified in 3 levels – Job Type, Job Category, Job level. In addition Job Title defines the employee’s specific job title.
- Manager name will be one of the employees in the company.
Please do not enter more than one record for a single employee.
By Default, you can enter up to 600 employees.
Entering Data in Excel – Employee Updates
When an employee profile changes, please enter the new information in the EMPLOYEE UPDATES sheet.
Choose Employee, enter the effective date of profile change, and reason.
Choose a reason why the employee’s profile changed. For example, when an employee gets promoted or changes teams.
Please do not enter more than one record on the same date for a single employee.
By Default, you can enter up to 3000 employee update records.
Entering Data in Excel – Employee Exits
When an employee leaves the company, please enter in the EXITS sheet.
Please do not enter more than one record on the same date for a single employee.
By Default, you can enter up to 300 employee exits.
Entering Data in Excel – Skills
To track the employees’ actual skill level vs expected skill level, we use the SKILLS sheet.
Employees will be automatically populated, and skills will be displayed automatically.
For each Employee, choose the skill profile.
When a skill profile is chosen, the EXPECTED skill levels will automatically populate.
If an employee takes training and improve skill level, then you can update the information for that specific employee.
By Default, you can enter up to 600 records.
Entering Data in Excel – Training list
Enter the list of trainings in the TRAININGS sheet.
Each training will have a training ID (unique) and training name.
The Start date is when the training becomes available for employees to start training.
The End date is when the training will stop being available for employees.
For example, a training that is available only on one day on Jan 3, 2021 will be entered as Start date: Jan 3, 2021 and End Date: Jan 3, 2021.
For example, a training that can be taken anytime between Jan 3, 2021 and Jan 25th will be entered as Start date: Jan 3, 2021 and End Date: Jan 25, 2021.
- Enter the name of the Trainer
- Enter info on Source whether the training is being done internally within the company or externally by another party
- Enter the Mode of training - Online or In-person
- Categorize the trainings using Category and SubCategory columns
- Enter cost of signing up for the training
- Enter the number of hours the training will require
- Enter the number of seats available for the training
By Default, you can enter up to 250 trainings.
Entering Data in Excel – Training Enrollments
When an employee registers or enrolls in the training, please enter in the ENROLLMENTS sheet
- Choose the Employee name and Training ID
- Enter the date when the employee will be taking the training on.
- Enter the Status of enrollment. (Enrolled/Completed/Cancelled)
- After the employee completes training, enter the rating (0 to 10) and benefit (in financial benefit).
By Default, you can enter up to 2500 training enrollments.
Ongoing Data Entry
Now that we have covered all the input points in the Excel file, let’s take a look at when you should update the file with new data.
- When a new employee joins, please add a row to the EMPLOYEES table.
- When a change happens in employee profile (except exit or termination), enter a record in EMPLOYEE UPDATES table
- When an employee leaves, enter a new record in EXITS table
- When an employee starts, enter their skill profile and actual skill levels in SKILLS sheet
- When a new training is created or canceled, enter in TRAININGS sheet
- When an employee enrolls or cancels a training, enter in the ENROLLMENTS sheet
For next step on how to set parameters in Power BI, please visit this article
Setting up Parameters and connection in Power BI