Afia Kona
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.
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.
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.
- To do so, first, we type the following formula using the TODAY function in 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.
- 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.
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.
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
- How to Calculate Midpoint of Salary Range in Excel (3 Easy Ways)
- Create Salary Breakup Calculator in Excel
- How to Make a Future Salary Calculator in Excel (with Easy Steps)
- Calculate Gross Salary in Excel (3 Useful Methods)
- How to Calculate Income Tax on Salary with Example in Excel
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)
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)
- 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.
- 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)
- 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-E17This 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
- How to Calculate Monthly Salary in Excel (with Easy Steps)
- How to Calculate Prorated Salary in Excel (3 Suitable Ways)
- Leave Salary Calculation in Excel (With Easy Steps)
- How to Create a Salary Increase Matrix in Excel (With Easy Steps)
- How to Model Salary Regression Analysis in Excel (2 Ways)
- Salary Deduction Formula in Excel for Late Coming (with Example)
