# Sample Excel File with Employee Data for Practice

In this example, you will get a sample Excel file with employee data for practice. The problems are beginner-friendly and you will need to know the following features and functions: 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.

## Problem Overview

We have an Excel dataset with six columns for employee information. The first two columns are filled in. You will fill in the rest of the values in the dataset as per instructions. We have set today’s date in cell B28 to calculate date-related values.

Here’s how to fill the rest of the information.

• Email Address – The format will be “[email protected]”.
• 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.

• Job Status – If an employee joined more than 180 days ago, they 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.

Get FREE Advanced Excel Exercises with Solutions!

Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

4 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

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.

2. how can i use the formulas 0f count if and average

• Reply
Lutfor Rahman Shimanto Feb 7, 2024 at 1:00 PM

Hello ASNATH

Thanks for reaching out and posting your comment. You want to know how to use the COUNT, IF and AVERAGE functions in a formula. I am presenting an example where I will form a formula using these functions.

OUTPUT OVERVIEW:

Suppose your dataset has Employee ID, Name, Department, Age, and Salary columns. You want to find out the average salary based on department. You can get the result using only the AVERAGEIF function, but if the department does not exist, it will provide a #DIV/0! Error. So, to handle this type of situation, you can combine COUNT, IF, SUM and AVERAGE functions:

`=IF(SUM(IF(C2:C11=B14, COUNT(1),""))<=0,"There is no employee.",AVERAGE(IF(C2:C11=B14, E2:E11)))`

Hopefully, the idea will help you. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

Advanced Excel Exercises with Solutions PDF