Sample Excel File with Employee Data for Practice

Get FREE Advanced Excel Exercises with Solutions!

In this example, you will get a sample Excel file with employee data for practice. The problems are beginner friendly, with a little bit of knowledge about Excel will allow anyone to solve the problems within a few minutes. You will need to know the following features and functions of Excel to solve the problems: how to join two cell values, the Data Validation list, the VLOOKUP, and the IF functions. These features and functions are available from Excel 2007. So, all of you should be able to solve the problems without any compatibility issues.


Download Practice Workbook

You can download the Excel file from the following link.


Problem Overview

We have an Excel dataset with six columns consisting of “Name”, “Joining Date”, “Email Address”, “Department”, “Monthly Salary”, and “Job Status”. The first columns are filled in by us. You will fill in the rest of the values in the dataset as per the previously mentioned methods. Moreover, we have set today’s date in cell B28 to calculate date related values. This is a better approach than using the TODAY function, as this will save memory.

Problem Sheet for Sample Excel File with Employee Data for Practice

Now, your task is to fill the data into the four columns –

  • Email Address – Fill the email addresses that have the same domain as “@demomail.com”. The format will be “[email protected]”. To solve this you need to know how to join two strings in Excel.
    • Solution: We have used the ampersand operator to join the values from the Name column with the email domain. You can use other methods, such as the CONCATENATE, TEXTJOIN functions, or even VBA to do so.

  • Department – You will need to create a Data Validation in this column. This Excel feature helps us to restrict data entry. The source for data validation is on the “Reference Table” Sheet (Range B5:B11).
  • Salary –  There is a lookup table in the “Reference Table” Sheet. Your task will be to match the department name and return the monthly salary in that. You can use any lookup function to do so.
    • Solution: We have used the VLOOKUP function to solve this problem. However, you can also use any other lookup function to do so, such as the INDEX, MATCH, XLOOKUP functions.

The lookup table looks like this.

Lookup Table in Another Sheet

  • Job Status – If an employee joined more than 180 days ago, then he or she will be a permanent employee of the company. You need to use conditionals to solve this.

The completed Excel file will look similar to this.

Snapshot of Solved Problem


Conclusion

Thank you for reading this article about sample Excel file with employee data for practice. You can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience and we will solve your query as soon as possible. Keep excelling!

Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

2 Comments
  1. Should have been helpful if you have shown all the steps.

    Vlookup has fetched some N/A errors in the Salary column. Not sure why as I have given same formula for all cells and some have shown correct results some N/A error.

    • Hi A
      We haven’t shown step-by-step solutions because the underlying objective of this article is to help our users internalize these problems to the core. We strongly believe that this will be possible only if we give a generic guideline and let our users practice more.

      Coming to your VLOOKUP issue, the formula you should use is =VLOOKUP(E5,’Reference Table’!$B$5:$C$11,2,0)

      Please double-check that you have the cell references correct.

      You can check the following articles

      for VLOOKUP: https://www.exceldemy.com/excel-vlookup-function/

      for referencing: https://www.exceldemy.com/difference-between-absolute-and-relative-reference-in-excel/

      I think these two articles, along with the workbook attached to this article will solve your problem.

      However, if these resources do not solve your problem, then please send us your workbook at [email protected]

      Thank you for your patience.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo