How to Create Automatic Salary Slip Generator Using Excel

Overview of a Salary Sheet and the Automatic Salary Slip

In the following picture, you can see the overview of a Salary Sheet.

Using this Salary Sheet, we have created the following automatic salary slip.

Create an Automatic Salary Slip Generator Using Excel: 8 Steps

Step 1 – Creating an Outline for the Automatic Salary Slip

We have created the outline for the automatic salary slip. Use the image below as the guide if you’re making your own. We have used All Borders and Thick Outline Borders. Add the company name and address at the top.

Step 2 – Adding the Date to the Salary Slip

Copy the following formula into cell F6.

=TODAY()

The TODAY function returns the current day date. Whenever you print or use the slip the Date will be updated to the current date by itself.

Press Enter.

Step 3 – Inserting the Employee ID from the Salary Sheet to the Salary Slip

Click on cell C9 and go to the Data tab.
Click on the Data Validation icon and select Data Validation.

A Data Validation dialog box will appear.
From the Allow group, select List.

Click on the upward arrow of the Source box to select the data source.

Go to the Salary Sheet and select cells B5:B15 as source data.

Click OK in the Data Validation dialog box.

You can see a drop-down arrow on the right side of cell C9.
Click on that drop-down arrow and select an ID from the list. We selected 104.

Step 4 – Using a Named Range in the Main Salary Sheet

Select the entire Salary Sheet from cells B4:M15.
From the Formulas tab, select the Define Name group, and select the Define Name option.

At this point, a New Name dialog box will appear.
Set the Name as Salary and click OK.

Step 5 – Using the VLOOKUP Function to Automate the Salary Slip

To find out the Employee Name, insert the following formula in cell E9 and press Enter.

=VLOOKUP(C9,Salary,2,FALSE)

Formula Breakdown

VLOOKUP(C9,Salary,2,FALSE) →The VLOOKUP function looks for values in a table or data range.
C9→ is the lookup_value.
Salary → is the table_array.

2 → is the col_index_num.
FALSE→ means exact match.
VLOOKUP(C9,Salary,2,FALSE) → becomes
- Output: Rain

Explanation: Here, Rain is the Employee’s Name.

You can see the Employee’s Name in cell E9.

Insert the following formula in cell E10 to find the Department.

=VLOOKUP(C9,Salary,3,FALSE)

Press Enter.

Similarly, using the VLOOKUP function, we will fetch the Basic Salary, House Rent Allowance, Medical Allowance, Travel Allowance, Provident Fund, and Income Tax.