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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create an Employee Database in Excel



How to Create an Employee Database in Excel: with Easy Steps

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 Fill 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

Read More: How to Create Student 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

Read More: How to Create a Recipe Database in Excel


Download Practice Workbook

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


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.

Stay with us and keep learning.


Related Articles

<< Go Back To Database in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo