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.

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.

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

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

• 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.

• For the Basic Salary, insert the following formula in cell C13:

• For the House Rent Allowance, insert the following formula in cell C14:

• For Medical Allowance, apply the following formula to cell C15.

• For Travel Allowance, use the following formula in cell C16.

• For the Provident Fund, use the following formula in cell E13.

• For the Income Tax, put the following formula in cell E14:

Step 6 – Summing up Total Earnings in the Automatic Salary Slip

• Insert the following formula in cell C17:

• Press Enter.
• You can see the Total Earnings in cell C17.

Step 7 – Calculating the Total Deduction in Automatic Salary Slip

• Use the following formula in cell E17:

• Press Enter.

Step 8 – Calculating the Net Salary

• Insert the following formula in cell E18:

This simply deducts the Total Deduction from Total Earnings.

• Press Enter.

• Change the ID No. by clicking on the drop-down arrow in cell C9. We selected 108.

• The salary slip has been changed to the salary slip for the employee with ID No. 108.

Practice Section

You can download the Excel file below to practice the explained method or use it as a template.

Download Practice Workbook

<< Go Back to Make Salary Slip | Salary | Formula List | Learn Excel