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.


Download Practice Workbook

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


Overview of a Salary Sheet and Automatic Salary Slip

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

Salary Sheet for Creating Automatic Salary Slip Using Excel

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

Overview of Automatic Salary Slip Using Excel

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


8 Steps to Create Automatic Salary Slip Generator Using Excel

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.

Read More: How to Create Tally Salary Slip Format in Excel (With Easy Steps)


Step-2: Adding Date to Automate Salary Slip

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

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

Use of TODAY Function in Automatic Salary Slip

  • After that, press ENTER.

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

Read More: How to Create Driver Salary Slip Format in Excel


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.

Applying Data Validation in Creating Automatic Salary Slip in Excel

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.

Read More: How to Make Salary Sheet in Excel with Formula (with Detailed Steps)


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.

Defining Main Salary Sheet Name to Create Automatic Salary Slip in Excel

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.

Read More: Daily Wages Sheet Format in Excel (with Quick Steps)


Similar Readings


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.
=VLOOKUP(C9,Salary,2,FALSE)

Applying VLOOKUP Funcion in automatic Salary slip in Excel

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.

  • 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.
=VLOOKUP(C9,Salary,3,FALSE)

Using VLOOKUP Function to Find Out Department in Automatic Salary Slip

  • 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.
=VLOOKUP(C9,Salary,4,FALSE)
  • After that, for House Rent Allowance, we type the following formula in cell C14..
=VLOOKUP(C9,Salary,5,FALSE)
  • Moreover, for Medical Allowance, we type the following formula in cell C15.
=VLOOKUP(C9,Salary,6,FALSE)
  • In addition, for Travel Allowance, we type the following formula in cell C16.
=VLOOKUP(C9,Salary,7,FALSE)
  • Furthermore, for the Provident Fund, we use the following formula in cell E13.
=VLOOKUP(C9,Salary,9,FALSE)
  • In addition, for the Income Tax, we use the following formula in cell E14.

=VLOOKUP(C9,Salary,10,FALSE)

Read More: Per Day Salary Calculation Formula in Excel (2 Suitable Examples)


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.
=SUM(C13:C16)

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

Use of SUM Function to Find Total Earnings in Automatic Salary Slip

  • Furthermore, press ENTER.

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

Read More: How to Calculate Annual Salary in Excel (with Detailed Steps)


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.
=SUM(E13+E14)

Calculating Total Deduction in Automaric Slalary Slip in Excel

  • After that, press ENTER.

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

Read More: TDS Deduction on Salary Calculation in Excel Format


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.
=C17-E17

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.

Read More: How to Calculate Net Salary in Excel (With Easy Steps)


Practice Section

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


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. Please visit our website Exceldemy to explore more.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo