Employee Database Excel Template

• Version
• 36'688 Downloads
• 30.2 Mb File Size
• January 4, 2022 Updated
• 8 Number of comments
• ★★★★★

  ★★★★★
  Rating

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.

Template Contents

• Employee Database Manager Lite (up to 15 employees)
• Employee Database Manager Pro 150 (up to 150 employees)
• Employee Database Manager Pro 250 (up to 250 employees)

Employee Database Manager Lite

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 is for up to 15 employees.

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

17. Others
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.

All Employees
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.

Individual Employee
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 Pro

If you are a Human Resources Manager or an employee who is responsible for managing your company’s employee database, you might think about having those data recorded in a Microsoft Excel file where you can see it easily with Microsoft Excel program or transferred among other Microsoft Office processor, like Word and Powerpoint.

The main question that usually arises is what kind of data format that should be used. Do I have to create a table and start typing the data? Or do I have to assign one worksheet to one employee? And how to process the data into a useful chart and summary report?

This Employee Database Manager should answer your questions. It is created with a simple approach to ease you on developing it to meet your company’s specific needs. The approach is to assign one worksheet to one employee and to set all employee information to be filled in one single column only. It will make the data mapping of each employees to a single database table easier where one filled column will be transposed into one row. And with specific period are separated in those filled columns, it will ease you to group any information on specific period, like year.

As the conclusion, this Employee Database Manager will be suitable for you who

• are looking a simple and easy to use database format in excel and do not want to start from the scratch
• want to start to move information in printed documents to an excel file
• have some excel knowledge limitation
• want to gather all information in single excel spreadsheet

You can insert image in Employee Data Form worksheet, but you have to set the size as minimum as possible, because if you manage hundreds of employee, you will make your excel file size very big if you don’t keep its image size small.

The lite version will allow you to see how it works by managing maximum of 15 employees. The Pro version is fully unlocked and it will allow you to do any kind of customization. There are 150 and 250 employees version. The formula is prepared to handle until 500 employees but you need to make sure that your computer processor can handle the calculation process.

Features

Ξ Custom Category Names and Titles

You can set specific names and titles to be shown in each employee data form.

Ξ All and Individual Summary

You can see summary of your employees by specific category (division, department, salary, leaves etc) in specific year. And there are Top Panel in Individual Summary worksheet to help you on seeing recent hired/resigned employees as well as monitoring employee’s expiring contracts.

Ξ Simple Employee Database

All employee information are grouped in a single database table where can utilize for many other purposes.

Ξ Human Resources Dashboard

You will get a customize dashboard where you can modify easily. It will show current and seven year summary in chart visualization. In this chart you will see :

Seven Year Visualization

• Salary Budget vs Realization
• Number of Employees Target vs Realization
• Hired vs Resigned Employees

Current Year Visualization

• Salary distribution
• Employee distribution
• Employment Status

Ξ Fully Unlocked

You can customize anything in this spreadsheet to

• record more important information of your employees
• create more specific reports
• create more specific charts