This Employee Database Manager is a simple excel template that will help you manage information of your employees easily. It is simple because the intention on creating this spreadsheet is to record all employee information in a simple format and map those information in a simple database which can be processed quickly and easily. There are summaries and charts built from those datas with common excel functions.
The outlook of Employee information worksheets might not fit your official employee data form, if you plan to make it as your form as well. It is designed as an interface to the employee database worksheet. I suggest you to use your official form and use this as a database to process and retrieve employees data.
The idea of creating this spreadsheet is to assign one worksheet to one employee and set one column to type all needed employee information. This one column data entry will ease grouping of those related data into a one row employee data in a big database record.
To accompany employee information form and database worksheet, I added setup worksheet. It is intended to help you writing/selecting correct names and titles to minimize any typos which could make them undetected in summary formulas.
If you don’t have many employees to be managed, you can insert employee images in each employee data form. But, it is not recommended if you manage hundreds of employees because it will make the size of the file becoming very big. For example, the size of 150 – 250 employee database manager spreadsheet (the Pro version) is between 13 – 21 Mb, and if one employee image has 200 Kb filesize, there will be an additional 30 – 50 Mb.
The lite version that you can download below is protected. And it can be used for 15 employees only. If you want to have a full control of this excel template, you can purchase the Pro version for USD 11.99
You can follow a short guidance below to use this template :
Go to SETUP worksheet
Filename : Change the filename with exactly the same name with the filename that you have saved as or modified by your computer
Starting Year : Type the starting year period where you want to record and track your employees. It can cover until 7 years period. You can record any employee information from years before starting year that you defined (hired and resign dates, allocated leaves, etc) in this employee database but you won’t see it in summary or report period. Pro version will allow you to modify any codes inside the spreadsheet where you can customize it to suit your specific needs.
You can find several tables that must be filled for names and titles similarity in Employee Data Entry Form worksheets.
1. Company Information
You can type your company information in this area.
2. Company Organization Structure
You can type names of your company organization structure in this area. It is divided into Division, Department, Title and Class.
3. Company Leaves Types
There 10 Paid and Unpaid Leaves types you can set in this area
4. Company Employment Types
There are 2 employment types you can set in this area based on their working hours and contract status.
5. Company Benefit Plan and Deduction
You can define your employees salary structure in this area.
6. Company Properties
You can set your company’s properties in this table
7. Salary Payment Details
You can set your salary payment frequency category in this area
8. Other Category Details
If you need other important information to be put in Employee Database, you can set its category in this table. It will be shown at the bottom of each employee database worksheets.
Go to DATABASE (Employee Data Entry Form) Worksheet
Each employee will have its own worksheet. You can start typing in the first database record. There are 17 parts that can be filled. All filled cells are grouped in one column, column E, as you can see in picture below.
1. Employee Data Entry
– Leave record number as it is. It is used as an anchor for database lookup.
– Type Employee ID if any
2. Personal Details
– Type personal detail information in this part.
3. Contact Details
– Type contact detail information in this part
4. Job Details
In this part, you can type
> Hired Date : The date can be any dates. It doesn’t need to be the date within or after the starting year you filled in setup worksheet. For example, you can put starting year as 2015 and type hired date as 5th June 2013. It will be the case if you want to move your old database to this one.
> Resign Date : The same with hired date, you can put any dates in this cell.
> Position : This is the first position when the employee join your company
You will find 7 positions record that needs to be filled based on year. The first position, for example 2015, could be the same with hired position, so you can copy the same contents and paste it to this one. This data will be used in yearly summary report and chart.
Next, if your employees workday is based on days, you can type their salary and leave information in form 5-7. If it is based on hours, you can fill respective information in form 8-10.
5. Salary Structure (Day Based)
Salary structure is divided into three categories, Basic Salary, Benefit and Deduction. You can define Benefit and Deduction titles in Setup worksheet. Total benefit, deduction and salary (all cells with green color) will be calculated automatically.
This part has the same structure with JOB DETAILS part. There is a hired area where you have to fill the numbers of respective employees at their hired dates, while you have to each year numbers as well for report and chart purposes.
6. Leave Details (Day Based)
Set paid and unpaid leaves titles in Setup worksheet and type allocated paid and unpaid leaves here.
7. Taken Leaves (Day Based)
Paid and unpaid leaves titles will follow above titles. You just have to fill respective employees taken leaves each year for report purposes.
8. Salary Structure (Hour Based)
How to fill this area is the same with number 5, except the unit is in hour.
9. Leave Details (Hour Based)
How to fill this area is the same with number 6, except the unit is in hour.
10. Taken Leaves (Hour Based)
How to fill this area is the same with number 7, except the unit is in hour.
11. Emergency Contact Details
Type emergency contact here.
12. Health Details (Optional)
Type health detail here.
13. Company Properties
If you lent some company properties to your employee, you can type those information in this area. The titles will follow the titles you type in Setup worksheet.
14. Joining Process Details (Optional)
Type any joining information process in this area
15. Work Experiences Details (Optional)
Type any previous work experiences of your employees in this area
16. Education Details (Optional)
Type any previous education accomplishment of your employees in this area
If you need other information that are not covered in this worksheet, you can define the titles in Setup worksheet and fill it here.
Go to SUMMARY Worksheet
There are ALL and INDIVIDUAL summary worksheets where you can see summary of related Human Resources parameters in this summary worksheet. This is a worksheet where you can go to specific employee data form using the link next to their names.
In this worksheet, you can read yearly summary of
– Employment status based on number of hired and resigned employees
– Employee distribution by division, department, titles and class
– Leaves distribution by allocation and taken leaves
– Employment status
– Salary, benefit plan and deduction distribution by their types
– Company properties distribution
– Salary, benefit plan and deduction distribution by employee division
With the Pro version, you can add as many summary as you need because the raw database is unprotected and you can use the excel formulas like above summary table formulas to pull your aimed data.
In this worksheet, you can find and go to specific employee by selecting it from employee link column. You will also 3 months update of your recently hired/resigned employees as well as monitor expiring employee’s contract. There is a filter tool applied in employee’s table. You can filter your employees based on names, birth date, division, department, title, hired date, resigned date and contract expiration date. You can go to specific new employee worksheet by clicking add new employee link in the right side of that top panel.
Go to DASHBOARD Worksheet
In this simple Human Resources dashboard you can see a chart of
– Salary Chart : Budget vs Realization
– Number of Employees Chart : Target vs Realization
– Employees Turnover Chart : Hired vs Resigned
And also you can see a chart for current year which will map :
– Salary distribution
– Employee distribution
– Employment Status
Because budget and target data needs to be filled manually, you can type those information in Chart Table at right side of this worksheet.
As informed above, this is a simple employee database manager. You can develop it to meet your company specific needs. In paid version where you can see excel formulas inside its cells, in summary worksheets, I use SUMIFS and COUNTIFS mostly. I am using INDIRECT function to get and sort the data from employee data form worksheets to database worksheet. And because excel HYPERLINK function requires filename as part of its formula, I put the filename in Setup worksheet as its reference. And it must be the same with the filename that you have saved as or you can’t use the previous/next employee links in Employee worksheets.
Employee Database Manager Lite - V1.7 (1.2 MiB, 2,766 hits)
The lite version is protected and only allow you to manage maximum 15 employees. You can purchase the Pro version if you want to have a full control of the spreadsheet and manage until 150 – 250 employees.
Employee Database Manager Pro – USD 13.99