Mukesh Dipto
This tutorial will demonstrate how to create a tally salary slip format in Excel. For a professional, it is very important to understand what a salary slip is. Generally, an employee receives salary slips from his/her employers. Many employees face difficulties at the start of their career as they don’t understand all the terms of salary slips. However, in this tutorial, we will follow a step-by-step procedure to create a tally salary slip format in Excel.
Download Template
You can download the template from here.
Step-by-Step Procedures to Create Tally Salary Slip Format in Excel
In the following image, we can see a template of tally salary slip format in Excel. Throughout this article, we will create this template by following some simple steps. So, without further delay let’s see the steps to create a salary slip format.
STEP 1: Add Company Name in Header Section of Slip Format
In the first step, we will add the header in our tally salary slip format in Excel. Follow the below steps to do this.
- To begin with, open an Excel file.
- Then, type the company name as the header of that Excel file in cell B4.
STEP 2: Insert Employee Details
In the second step of making a salary slip format in Excel, we will insert employee details. Follow the below instructions to do this.
- First, create a section like the following image in cell range B6 to F12.
- Next, insert the header of that section like the following image.
- Then, we will add the following fields for employee details:
- Employee Name: Add employee name in this field.
- Employee Code: In this field, insert the employee code of the company.
- Designation: Type designation of the person here.
- Identity Number: This part depends on the geographic location.
- Bank Account Number: Input the bank account number of the employee here.
- Bank Name: Type the bank name where the company will deposit the salary.
- Date of Joining: Here, we will add the joining date of the employee.
- Total Working Days: Total working days in a month excluding government holidays.
- Number of Working Days Attended: In this field, insert the number of working days of an employee in a particular month.
- Leaves: Number of leaves an employee takes from the company.
STEP 3: Include Salary Components
In the third step, we will include salary components in the tally salary slip format in Excel. To include salary components we will go through the following auctions.
- Firstly, create a section like the following image in cell range (B14:F20).
- Secondly, type the headings like the following image in the first two rows of the section.
- Thirdly, insert the following fields as salary components. We can describe the salary components in two parts. Let’s take a look at them:
» Income: We get the following salary slip components in the income part:
- Basic Salary: Here, we will type the amount of basic salary.
- Dearness Allowance: We will insert the cost of living in these fields.
- House Rent Allowance (HRA): This field contains the amount of house rent.
- Medical Allowance: Medical allowance provided by the company.
» Deductions: In the deduction part we get the below components:
- Provident Fund (PF): Here, input the amount of the provident fund according to the company policy.
- Professional Tax: Insert tax payable amount.
- Health Insurance: Add an amount of health insurance in this field.
Read More: How to Calculate Bonus on Salary in Excel (7 Suitable Methods)
STEP 4: Calculate Net Salary
After that, in the fourth step, we will create a field in cell B22. We will calculate the net salary of an employee in this section of the tally salary slip format.
STEP 5: Add Approval and Notes
In the final step, we will add approval and notes in the tally salary slip format. We will create a section like the following image. In this section, we will add the ‘Employee Signature’ and ‘Employer Signature’ fields.
Read More: How to Insert Digital Signature in Excel (3 Quick Methods)
Demo Calculation of Tally Salary Slip Format in Excel
So far we have seen how to create a tally salary slip format in Excel. In this section, we will see the demo calculation of the tally salary slip. To illustrate the calculations of this format follow the below steps.
STEPS:
- To begin with, input the ‘Date of Joining’, and the number of ‘Total Working Days’. Also, input the ‘Number of Attended Working Days’ by the employee.
- In addition, type the amount $2500 in C16. This is the amount of basic salary.
- Furthermore, we will calculate ‘Dearness Allowance’ which is 50% of the basic salary. So, select cell C17. Insert the following formula:
=C16*50%- Press Enter.
- As a result, we get the value of dearness allowance in cell C17.
- Similarly, assume the house rent allowance is 40% of the basic salary. To evaluate that insert the following formula in cell C18:
=C16*40%- Hit Enter.
- So, we get the amount of house rent allowance in cell C18.
- Afterward, add the amount of medical allowance in cell C19.
- Moreover, in cell C20 type the following formula:
=SUM(C16:C19)- Press Enter.
- As a result, we get the amount of total income in cell C20.
- Then, to calculate provident fund type the following formula in cell F16:
=IF(C16>=3000,3000*12%,C16*12%)- Hit Enter.
- Also, input the amount of $150 in cell F18 for health insurance.
- After that, insert the following formula in cell F20 to calculate the total amount of deductions:
=SUM(E16:F18)- Press Enter.
- So, the above command returns the total amount of deductions in cell F20.
- Next, in cell F22 insert the following formula to calculate net salary:
=C20-E20- Press Enter.
- Finally, we get the amount of net salary is $4700 in cell F22.
Read More: Per Day Salary Calculation Formula in Excel (2 Suitable Examples)
Conclusion
In conclusion, this provides step-by-step directions to create a tally salary slip format in Excel. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment in the box below. We’ll do our best to respond as soon as possible. Keep an eye on our website ExcelDemy for more interesting Microsoft Excel solutions in the future.
