How to Create an Employee Database in Excel (with Easy Steps)

To keep track of employee information, we may need an Excel database with fields such as names, positions, salaries, and experiences, among other things. We do, however, want to create a dynamic employee database in which all information is updated automatically in real-time. So, in this tutorial, we will show you how to create a dynamic employee database in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Steps to Create an Employee Database in Excel

We have provided some sample fields of a database where we will enter and keep our records in the image below. To make the database dynamic, we’ll use TODAY, INT, VLOOKUP, and MATCH functions later. We’ll use a drop-down list to select the information we need for each employee.

Sample Data


Step 1: Insert Basic Details to Create an Employee Database

  • Firstly, write the names of each employee.

Steps to Create an Employee Database in Excel

  • Type the positions of each employee.

Steps to Create an Employee Database in Excel

  • Enter the information for the base salary.

Steps to Create an Employee Database in Excel

  • Then, type the joining dates.

Steps to Create an Employee Database in Excel


Step 2: Use TODAY Function to Count Experience

  • To count the experience in years, subtract the joining date from today’s date with the following formula.
=TODAY()-E5)

Steps to Create an Employee Database in Excel

  • Consequently, it will result in a date format.

Steps to Create an Employee Database in Excel

  • Go to the Number ribbon, and change the format to Number.

Steps to Create an Employee Database in Excel

  • As a result, the result will show in days. We need to convert the days into years.

Steps to Create an Employee Database in Excel

  • To convert the number of days into years, enter the following formula.
=(TODAY()-E5)/365

Steps to Create an Employee Database in Excel

  • The result will appear in cell F5 as 10.30.

Steps to Create an Employee Database in Excel

  • Now, we need to count the full number of completed years. To do this, we will apply the INT function with the following formula.
=INT((TODAY()-E5)/365)

Steps to Create an Employee Database in Excel

  • Press Enter to see the result (10.00).

Steps to Create an Employee Database in Excel

  • Finally, use the AutoFill Handle Tool to fill in the columns.

Steps to Create an Employee Database in Excel


Step 3: Insert Formula to Calculate Present Salary

  • For the 5% increment per year, insert the following formula to calculate the present salary.
=D5*(1.05)^F5

Steps to Create an Employee Database in Excel

  • Then, press Enter.

Steps to Create an Employee Database in Excel

  • Finally, auto-fill the required cells by dragging down the AutoFill Tool.

Steps to Create an Employee Database in Excel


Step 4: Insert Drop-Down List

  • Firstly, click on the Data.
  • Select the Data Tools.
  • Then, click on Data Validation.

Steps to Create an Employee Database in Excel

  • In the Allow box, choose the List option.
  • To make a drop-down list with the employee names, select the range B5:B11.
  • Finally, click OK.

Steps to Create an Employee Database in Excel

  • As a consequence, your drop-down list will be created as the image shown below.

Steps to Create an Employee Database in Excel


Step 5: Apply VLOOKUP Function to Create Employee Database in Excel

  • To find the position of the name in the drop-down list in cell B5, type the following formula in cell C15.
=VLOOKUP($B$15,$B$4:$G$11,MATCH(C4,$B$4:$G$4,0),FALSE)

Sample Data

  • Then, press Enter to get the position (Team Leader) of the employee (William).

Sample Data

  • Drag the AutoFill Tool from left to right to fill in the cells.

Sample Data

  • Select the required format for currency ($) and date format.

Sample Data

  • Choose an employee’s name from the drop-down list.

Sample Data

  • As a result, all of the fields (i.e., positions, salaries, and experiences) change as the names are chosen.

Sample Data


Conclusion

I hope this article has given you a tutorial about how to create an employee database in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us and keep learning.

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo