Excel is a superb tool for analytical workings because it has everything you may need to execute your task. Meanwhile, making a salary sheet also need you to have some expertise and Excel helps you out in this case. A company has to provide salary sheets every month to their employees, which contain different allowances, deductions, and bonuses as per company rules.
Excel salary sheet formulas let you handle the task vigilantly with the help of a basic salary. Here we’ll get to know about salary formulas and some useful steps that help in making salary sheets.
What is a Salary Sheet?
The net payable amount is recorded in a salary sheet. It also includes basic wages, allowances, and deductions. Based on these calculations, a gross and net salary sheet is made of an employee.
Salary Formula
The Roman letter “Salarium” was given to Roman soldiers in ancient times. And the word salary is derived from this Roman letter. Using the salary calculator will be super easy to calculate and have an idea of the increment status of the employee to estimate the deductions and allowances to be added to a salary sheet.
Usually, the salary of an employee is based on:
- Basic Salary
- House Rent Allowance
- Transport Allowance
- FBP Allowance
- Statutory Bonus
- Provident Fund
- Income Tax
- Insurance
Below you will find the Salary Calculator formula:
Salary = Basic + HRA + Transport Allowance + FBP Allowance + Bonus – Provident Fund – Income Tax – Insurance
Steps to Make a Salary Sheet in Excel with Formula
Using a dataset of a company having 10 names of employees and basic salaries as well as the structure database of the company, we will try to understand how Excel salary sheet formulas work.
Using the above details, let’s make a salary sheet. For this, you can follow the steps given below:
Steps to make a salary sheet in excel
Create Employee Database & Salary Structure
At first, you must have a salary structure along with a database of employees.
For this, you have to make two columns on the left side of the new worksheet. These columns will have the name of the employee and basic salary.
Now, maintain the allowance percentage as well as the deduction percentage on the right-hand side of the columns containing the basic salary and the name of the employee.
Now, the employee database and salary structure are ready.
Calculate Gross Salary
In the next step, you have to calculate the allowances right from the basic wage and then calculate the gross salary.
For this, you must know the basic salary from the given database. Following this data, click on the B5 cell and then open the Data tab. Now, click on the Data Tools group and then choose the Data Validation tool option.
Thus, you will see the Data Validation window in front of you. Go to the Settings tab and click on the List option given in the Allow dropdown menu. In the next step, open the text box that refers to the cells B5:B14 of the Database worksheet. In the end, click on the OK button.
Eventually, cell B5 will have all the employees’ names in the dropdown list.
Having this database, you can now choose any name of the employees for salary calculations. Here we will choose the first name and that is Mathew Jones given in cell B5.
Choose cell C5 to know the basic salary of the employees given in the database. Enter the following formula that uses the VLOOKUP function. Press Enter key.
=VLOOKUP(B5,Database!B4:C14,2,FALSE)
Click on cell C8 to find and calculate extra allowances. Put the following formula and press Enter key.
=$C$5*VLOOKUP(B8,Database!$E$5:$F$8,2,FALSE)
Now, you can easily calculate the house rent allowance for the selected employee. Put the cursor in the bottom right position of the cell. You will find a black fill handle, drag it and copy the same formula for all the allowances given below.
That way you can calculate each allowance of an employee. Click on cell C13 to calculate the gross salary and put the following formula with the SUM function. In the end, press the Enter key.
=SUM(C5,C8:C11)
Now, the gross salary and allowances are displayed in front of you.
Calculate Amounts to Deduct
In the next step, we need to find the deductions from the salary of an employee.
For this, choose cell B5 and refer to cell B5 of the Calculate Gross Salary sheet.
Likewise, choose cell C5 and refer to the Cell C5 of the Calculate Gross Salary sheet.
Now, click on cell C8 and put in the following formula. Press the Enter key.
=$C$5*(VLOOKUP(B8,Database!$E$11:$F$13,2,FALSE))
Ultimately, you can now get to know the provident fund deduction of the selected employee. Put the cursor in the bottom right side of the cell and drag the fill handle below. Copy the calculation formula.
Now, all the deductions are available, you just need to total it.
For this, choose the Cell C12 and put the following
formula. Using this formula, you can sum the values of cells C8 to C10. Now, press the Enter key.
=SUM(C8:C10)
Eventually, all the deductions are calculated in the sheet and you will have to results.
Calculate Net Salary
At the end of this procedure, we will calculate the total salary of the employee to complete our salary sheet.
For this, choose the cell C5 and put an equal sign. Choose cell C5 from the Calculate Gross Salary worksheet. Now, press the Enter key and you will see that cell C5 is connected with cell C5 of Calculate Gross Salary.
Likewise, choose cell C7 and connect it with cell C13 from the sheet Calculate Gross Salary.
Now, choose cell C8 and connect it with cell C12 from the Calculate Deduction sheet.
Click on cell C9 and put in the following formula. Using this formula you can subtract the cell C8 from the cell C7. Now, press the Enter key.
=C7-C8
That way all the calculations are completed for the making of the salary sheet using Excel salary sheet formulas.
To Sum Up
In conclusion, you are fully aware of how Excel salary sheet formulas work effectively and help you in making the salary sheets of an employee. Following the steps carefully will make you able to continue working on the salary sheets.
