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 


Enter Job classification fields (Job Type, Job Category and Job Level).


Enter list of Job Titles



Enter list of Exit reasons you want to capture from employees who are leaving the company.


Enter recruitment attributes - Recruitment Stages (up to 10 including Hiring), Decline Reasons (reasons why application was not hired) and Recruiter names


Enter list of Performance Review periods.

Enter list of Skills.


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 Leave Types.

 

Enter list of Onboarding activities in your company.



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.


Skill Profiles

Enter list of Skill Profiles.





Salary Grades

Enter the list of Salary Grades in your company. Grade name, Minimum , Midpoint and Maximum Salaries are required.


By Default, you can enter up to 50 Salary Grades.


Enter the names and definitions of boxes in 9-Box Grid.

 

Jobs

click on image to see in full size


Applications

click on image to see in full size



List of Employees

Enter list of all employees in your company.

click on image to see in 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.
  • Salary and Grade at the time of starting are entered.  

 

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.

click on image to see in full size


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, gets a salary increase, 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 – Onboarding

To track the employees’ completion of Onboarding steps, please enter the information in the Onboarding sheet.

Employees will be automatically populated and onboarding steps will be displayed automatically.


You have to mark “Yes” if the employee completed a specific step. Then, Enter the Onboarding Status (Completed or In Progress or Not Started) and enter the completed date if completed the whole onboarding process.

 


Employee Skill Levels


Enter actual skill level for each skill for each employee


Enter Leave data

 

Enter list of trainings

click on image to see in full size


Training Enrollments

Enter training enrollments by employees



Performance Review

Enter performance review data


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
  • Ensure that any new info is added to the appropriate tables (ONBOARDING, TRAINING, ENROLLMENTS, PERFORMANCE, SKILLS, JOBS, APPLICATIONS, LEAVE)


For next step on how to set parameters in Power BI, please visit this article


Setting up Parameters and connection in Power BI