Tracking employees can be a hard challenge especially when you are dealing with dynamic data. Besides, employee management software can be expensive hence leaving you with few options. Luckily, you can use Excel to create a comprehensive Employee database. Also you can take advantage of the Excel features and formula to make employee management Easy.
In this tutorial, I will guide you on how you can create Employee database in Excel. Read to the end to learn more tips and tricks on how to utilize the database.
Step 1: Create all the important fields
Every database contains field that hold information. These fields makes reading, analyzing, adding or changing data easier.
The most common fields in an employee database include:
- Name
- Date of Birth
- Age
- Contact
- Address
- Department
- Job Role
- Joining Date
- Experience
- Salary
- Bonus
Type the field names on the first Row. You can add fill to make it stand out from the rest of the cells.
Click on file then save the excel file. You can save it with a unique name to differentiate it from other files.
2: Fill the Employee data on the spreadsheet.
Start by filling names then followed by their details such as date of birth, age, and salary
Step 2: Use =(YEAR(NOW())-YEAR(DOB)) to calculate the Age of employees automatically
To make your database easy to work with and efficient you need to leverage on Excel formulas. One of the the most prevalent data in most employee data bases is age. Keeping track of age manually can be very tiresome. Excel formula can calculate the employees age based on their date of birth. To achieve this use the following formula.
=(YEAR(NOW())-YEAR(B2))
Change B2 to the cell containing the Employees Date of birth then drag the formula down to apply it to the rest of the cells.
The age will update automatically.
You can use the same formula to calculate the number of years a certain employee has been working in your entity.
All you need to do is enter the the formula on the first cell then drag down
Step 3: Set permissions on the Employee Database
Employee database should be secured. To ensure only authorized people access the data you need to set up worksheet permissions. For instance you can set the database as read-only so that no changes can be made. Alternatively, you can encrypt the worksheet using a password. Here is a guide showing how you can protect a worksheet using password.
If you’re using Microsoft Office 365, Click share and enter the emails of the people only authorized to make changes. If you need to share to more people not allowed to make changes, you can set permissions to read only.
Step 4: Convert the data into a table
Every database is made up of tables that store different types of data. These tables makes it easy to index and retrieve data easily.
1. To convert your at into a table select all the cells then use the shortcut ctrl+T
2. A pop up menu will launch. Here you need to confirm the range of your data an whether your table has headers.
After converting the dat into a table, it will appear like in the image below.
Step 5: Use filter or sort to interact with the database
The purpose of using a a database is to store it securely and to interact with it in an efficient manner. Excel offers one of the best approach to interacting with data. For example, We can filter the database to show all the employees over the age of 35.
1. To filter age, click on the drop own arrow under the age column
2. Next click Number filters then select greater than
3. Enter 35 in the new pop up window an press okay
4. Excel will filter out all data for employees less or equal to 35.
5. As you can see from the image above the only data displayed is for employees aged 35 and above.
Download Free Excel Employee Database Template
You can use the template below to create your Excel employee database.
Conclusion
In this article we have seen how to create employee database in Excel. You can manage all your employers using a single Excel Worksheet. In addition, you can also set permissions and access to secure the Employee data. Microsoft 365 allows users to set share permissions and to collaborate with others in real time. Use the free template share in this article to practice or as a starting point.
