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.

Salary Sheet for Creating Automatic Salary Slip Using Excel

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

Overview of Automatic Salary Slip Using Excel


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.

Use of TODAY Function in Automatic Salary Slip

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

Applying Data Validation in Creating Automatic Salary Slip in Excel

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

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

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

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.

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

Using VLOOKUP Function to Find Out Department in Automatic Salary Slip

  • 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:
=VLOOKUP(C9,Salary,4,FALSE)
  • For the House Rent Allowance, insert the following formula in cell C14:
=VLOOKUP(C9,Salary,5,FALSE)
  • For Medical Allowance, apply the following formula to cell C15.
=VLOOKUP(C9,Salary,6,FALSE)
  • For Travel Allowance, use the following formula in cell C16.
=VLOOKUP(C9,Salary,7,FALSE)
  • For the Provident Fund, use the following formula in cell E13.
=VLOOKUP(C9,Salary,9,FALSE)
  • For the Income Tax, put the following formula in cell E14:

=VLOOKUP(C9,Salary,10,FALSE)


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

  • Insert the following formula in cell C17:
=SUM(C13:C16)

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

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

Calculating Total Deduction in Automaric Slalary Slip in Excel

  • Press Enter.


Step 8 – Calculating the Net Salary

  • Insert the following formula in cell E18:
=C17-E17

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

Get FREE Advanced Excel Exercises with Solutions!
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of NAME from Bangladesh University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also interested... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo