How to Create Automatic Salary Slip Generator Using Excel

Salary slip is a report that has a full detailed salary of the employee. Employers used an automatic salary slip generator to understand the salary record of the salary and to know whether the salary is already paid or not. If you want to create an automatic salary slip generator using Excel, you have come to the right place. Here, we will walk you through some easy steps to do the task effectively.

Overview of a Salary Sheet and Automatic Salary Slip

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

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

Further, in this article, we will show you how to create such a salary slip with easy steps.

Create Automatic Salary Slip Generator Using Excel: 8 Steps

In the following article, we will describe some easy steps to create an automatic salary slip generator in Excel. Here, we used Microsoft Excel 365. You can use any available Excel version. Let’s go through the steps.

Step-1: Creating Outline for Automatic Salary Slip

In this step, we have created the outline for the automatic salary slip. We have created the outlines using All Borders and Thick Outline Borders. Also, we add the company name and address.

Step-2: Adding Date to Automate Salary Slip

In this step, we will add a date to the automatic salary slip.

• To do so, first, we type the following formula using the TODAY function in 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.

• After that, press ENTER.

As a result, you can see the date of today in cell E6.

Step-3: Inserting Employee ID from Salary Sheet to Salary Slip

In this step, we will insert Id No. in cell C9, and for reference, we will use the Salary Sheet.

• In the beginning, we will click on cell C9 >> go to the Data tab.
• Furthermore, click on the Data Validation icon marked with a red color box >> select Data Validation.

Then, a Data Validation dialog box will appear.

• Then, from the Allow group >> select List.

• Afterward, click on the upward arrow of the Source box to select the data source.

• Then, we will go to our Salary Sheet, and select cells B5:B15 as our source data.

• After that, click OK in the Data Validation dialog box.

Then, you can see a drop-down arrow on the right side of cell C9.

• Moreover, we will click on that drop-down arrow >> select an Id No. from the list.
• Here, we selected 104.

Step-4: Using Named Range in Main Salary Sheet

In this step, we will give a name to our Salary Sheet so that we can easily find out that data table.

• First of all, we will select the entire Salary Sheet from cells B4:M15.
• Then, from the Formulas tab >> select the Define Name group >> select the Define Name option.

At this point, a New Name dialog box will appear.

• Then, we set the Name as Salary >> click OK.
• Hence, we named the Salary Sheet as Salary.

Step-5: Using VLOOKUP Function to Automate Salary Slip

In this step, we will use the VLOOKUP function to find out the Employee Name, Department, Basics Salary, House Rent Allowance, Medical Allowance, Travel Allowance, Provident Fund, and Income Tax for Id No. 104.

• First, to find out the Employee Name, we will type the following formula in cell E9.

• After that, press ENTER.

As a result, you can see the Employee’s Name in cell E9.

• Furthermore, we type the following formula in cell E10 to find out the Department.

• At this point, press ENTER.

As a result, you can see the Department in cell E10.

In a similar way, using the VLOOKUP function, we find out the Basic Salary, House Rent Allowance, Medical Allowance, Travel Allowance, Provident Fund, and Income Tax.

• Here, first of all, for Basic Salary, we type the following formula in cell C13.

• After that, for House Rent Allowance, we type the following formula in cell C14..

• Moreover, for Medical Allowance, we type the following formula in cell C15.

• In addition, for Travel Allowance, we type the following formula in cell C16.

• Furthermore, for the Provident Fund, we use the following formula in cell E13.

• In addition, for the Income Tax, we use the following formula in cell E14.

Step-6: Calculating Total Earnings in Automatic Salary Slip

In this step, we will calculate the Total Earnings in the automatic salary slip by using the SUM function.

• In the beginning, we will type the following formula in cell C17.

The SUM function calculates the sum of the values in cells.

• Furthermore, press ENTER.

As a result, you can see the Total Earnings in cell C17.

Step-7: Calculating Total Deduction in Automatic Salary Slip

In this step, we will calculate the Total Deduction in the automatic salary slip.

• First of all, we will type the following formula in cell E17.

• After that, press ENTER.

Hence, you can see the Total Deduction in cell E17.

Step-8: Calculating the Net Salary

In this step, we will calculate the Net Salary of the automatic salary slip.

• First of all, we will type the following formula in cell E18.

This simply deducts the Total Deduction from Total Earnings.

• After that, press ENTER.

As a result, you can see the Net Salary.

• Here, now we will change the Id No. by clicking on the drop-down arrow that is situated on the right side of cell C9.
• After that, we will select 108.

Therefore, you can see the salary slip has been changed, and now it is the salary slip for Id No. 108.

In a similar way, you can find the salary slip for every employee.

Practice Section

You can download the above Excel file to practice the explained method.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.

Conclusion

Here, we tried to show you some easy steps to create an automatic salary slip using Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.

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