The employee data upload tool allows to add multiple employees across multiple years if you have your data organized in a spreadsheet. The required fields in the spreadsheet are:
- Job Number
- Employee Number
- Year*
Optional Fields Include:
- First Name
- Last Name
- Gender
- Base Salary
- Base Hourly rate
- Total Variable Compensation
- Annual Hours
- Actual Base Earned
- Notes
- Job Class Title
Although Gender, Base Salary/hourly rate, Annual Hours and Job Class Title are not required fields each of these fields is highly beneficial and required if you want to fully utilize the compensation tool.
Once you have clicked the “Upload Employees” button you will be brought to a new page, on this page you will need to upload a spreadsheet from your local computer by selecting the Browse Button. The Compensation Tool automatically ingests the spreadsheet and makes assumptions on the matches based on the first Tab in the spreadsheet and headers located in the first row. You can change the tab being analyzed by selecting the appropriate tab in the drop-down menu under the heading “Select Sheet for Import” based on the name of the tab in your spreadsheet, ie “Sheet 1”. The header row, row that contains the name of the data in the column, can be adjusted if your header row is not the first row, likewise this can also be done for the row in which your first employee appears. Each header row which contains data required in the upload must have a heading (columns with empty header cells will not be analyzed), although your headings do not have to have the same wording as the Compensation Tool, we will explain...
Once the header row and data row is selected on the proper tab of your spreadsheet you can then match the columns of your spreadsheet to the fields in the employee data table. Each Compensation Tool field is listed on the left hand side of the page with the associated matched column on the right hand side. The Compensation tool will attempt to automatically match data from your spreadsheet, but if the match does not appear or is incorrect you can select the proper column by clicking the black drop-down triangle on the right side and selecting the header title to associate to the Compensation Tool field.
For example a spreadsheet could have the following format:
Organization Name-Payroll 2018-2020 | ||||||||||
Year | Employee # | Gender | Position | Position Code | Hourly Rate | Salary | YTD Earnings | Hours | Bonus | Manager |
2018 | 001 | Female | Job 1 | 20 | $60000 | 45000 | 1400 | $2000 | John Smith | |
2019 | 001 | Female | Job 2 | 20 | $65000 | 65000 | 1850 | Jane Dow | ||
2018 | 002 | Male | Job 3 | 43 | $35.00 | 70000 | 1850 | $5000 | Shen Yu |
"Sheet 1"
In this scenario:
Your "Select Sheet For Import" is Sheet 1 (highlighted in Yellow above)
Your Header Row (click the black adjust header row button) is row 3, the row in which your data begins is row 4
The Fields would be matched as follows:
Compensation Tool Field | Uploaded Spreadsheet Field |
Required Fields | |
Employee Number | Employee # |
Job # | Position Code |
Year | Year |
Optional Fields | |
First Name | |
Last Name | |
Gender | Gender |
Base Salary | Salary |
Base Hourly Rate | Hourly Rate |
Total Variable Compensation | Bonus |
Actual Hours | Hours |
Actual Base Earned | YTD Earnings |
Notes | |
Job Class Title | Position |
In your spreadsheet each row is treated as a distinct employee entry. For best results there should not be any blank rows in your spreadsheet. You can have additional data in your spreadsheet that is not used in the upload and Compensation Tool will ignore it, if it is not mapped to a specific Compensation Tool field (as shown in the manager field in the sample above).
If you do not have a “Year” column in your spreadsheet and all employee data is from the same year you can specify the year for the data for all the entries by selecting the “Specify value for all entries” in the drop down and manually entering the year appropriate year.
Once all required and optional fields have been associated you can click the green upload button to start the upload process. Once the upload starts your project will be locked as changes and additional uploads may impact the integrity of the upload. Once the upload is completed you will receive an email stating the success of the upload or highlight any rows with errors.