Salary sheet in Microsoft Excel

A salary sheet in Microsoft Excel is a convenient tool for distributing salaries among the various employees working for a particular company. And in this, the employees have many allowances and deductions according to the different company structures. And we can make a salary sheet by calculating and adjusting those respective amounts with the basic salary using Microsoft Excel.

Now in this tutorial, we will be showing step-by-step guidelines for making a salary sheet in Microsoft Excel using the formula respectively.

What do you mean by Salary Sheet in Microsoft Excel?

The respective salary sheet is a report in which the net payable amounts as salary to an employee is recorded efficiently, and the basic wage of an employee, extra allowances, as well as deductions, are recorded here based upon these calculations, the gross salary and net payable salary are calculated as well as recorded respectively.

What do you mean by Salary Formula in Microsoft Excel?

In Microsoft Excel the particular word that is "salary" was primarily derived from the Roman letter "Salarium," that was effectively given to the Roman soldier in the ancient times in addition to the wages they paid off. And the salary calculator is mainly helpful and plays a vital role in calculating and tracking the growth status of the respective employee and estimating the additions and deductions the employer makes in one's employee salary. The salary mainly consists of the various components in which there is a huge difference between the gross and net salaries.

What are the different components associated with the Salary sheet in Microsoft Excel?

We can effectively categorize the respective salary sheet into four components:

1. Employee Database and Salary Structure

Employee Database and the Salary structure basically comprises of the database realted to the employee that is none other than the employee's name and basic salary. And here, the salary structure is also declared, for which allowances the respective company provides and how much, it primarily containsinformation about which deductions are cut from the salary and how much of the salary respectively.

2. Gross Salary Calculation

In the Gross Salary calculation, we will efficiently calculate the allowances distinctively for each employee.

For example, It was well known that the respective allowances are the rent allowance related to the house, allowance related to the transport, flexible benefits plan, etc. And the basic wage, the total allowances will be get summed up to calculate the gross salary respectively.

Formula:

Gross Salary = Basic Salary + Allowances

3. Calculation of the Deduction

In the salary calculation there are some of the deductions which are also included are as follows:

a) Tax.

b) Provident fund.

c) Insurance, etc.

And these will be deducted from the basic salary, so in this respective component, these deductions are calculated for each employee based on their basic salary and the salary structure database's deduction percentage.

4. Net Payable Salary

Finally, the deduction will be efficiently subtracted from the gross salary in this portion. And as a result, we will get the net payable salary to an individual employee. So the formula would be like this.

Formula:

Net Payable Salary = Gross Salary - Deductions

Keywords used in the creation of the salary sheet of an employee:

The various keywords which are effectively used in the respective salary sheet of an employee:

Basic Salary: Basic salary is basically considered to be the primary source of an income for an individual, and it is the base salary by which one can easily derived other income components are respectively.
Housing Rent Allowance: Housing Rent Allowance is an employee's allowance which is paid for their housing as well as the rental expenses, and it is 50% or more of the basic salary.
Transport Allowance: The Transport allowance is the allowance that an employee gets for transportation.
FBP Allowance: In the FBP Allowance, the respective employee is entitled to get ancillary benefits under the flexible benefits plan, which mainly includes medical, conveyance, and other benefits as well.
Statutory Bonus: The employer grants a bonus of 8.33% of the respective employee's salary; the maximum allowable statutory bonus is not more than 20%.

Deduction

Provident Fund: It is mainly a deduction in which every employer face a deductionunder government law.
Income Tax: The Income tax is deductible as per the income tax slabs prevailing in India.
Insurance: Insurance isdeductible as per an employee's designation, employee level, and the years of experience they usually hold out.

What are the relevance and Uses of the Salary Formula in Microsoft Excel?

The salary calculator basically tells out the employees regarding their growth. And if any deduction is unclear to the employee, then in that case they can seek the help of the human resource department to clarify.
And the salary calculator also tells the employee about their current standing in the company as well as determining out whether the employee is underpaid or overpaid.
The salary calculator is quite useful when the company plans and implements the initiative in order to reduce human resource costs. And the calculator can be also be helpful in computing salaries as well as the compensation which are paid to the staff and to the top of the management.

What steps are the steps which are required to make a Salary Sheet in Excel with the help of the Formula?

Let us now assume that we have a company dataset for the 10 employees' names, their basic salaries, and the company's structure database. And we are required to create the salary sheet for the selected company. So we are required to follow the below-mentioned step one by one for a better understanding of the salary sheet creation.

Step 1: Creation of the Employee Database and their Salary Structure

So the first thing we need to do is prepare our employee database and their salary structure.

And To do this, we are required to open a new worksheet andmake two columns on the left-hand side, which willcontain the employee's name and basic salary as well.
The following will effectively record the allowance anddeduction percentages of the selected company on the right-hand side of the employee's name and basic salary, respectively.
And now, we have just created the employee database and salary structure in an organized structure.

Step 2: Calculating the Gross Salary

After that, we must calculate the allowances from the employees' basic wages and then calculate the gross salary.

And to achieve this, we first need to find out the basic salary of an employee from the dataset, andregarding this, we need to click on the B5 cell >> go to the Data tab >> Data Tools group >> Data Validation tool >> Data Validation option effectively.
And as a result, the Data Validation window will appear on the screen. We will choose the option List from the Allow: dropdown list at the Settings tab. Subsequently, in the Source: text box, refer to cells B5:B14 of the Database worksheet. And then finally, we will be clicking on the OK button as well.
After that, we willsee that the B5 cell has all the names of the employees in the given dropdown list.
And then we can also choose any of the employees' names to calculate their salary. Let us assume that we have chosen the first employee's name. So, for that, we will see Mathew Jones in the B5 cell respectively.
Next, we are required to find out the basic salary of the following employee from the given database, so we will select the C5 cell and write the following formulainto it. Here, the formula uses the VLOOKUP function to find the value, and then we need to press Enter button.

Formula:

=VLOOKUP (B5, Database! B4:C14, 2, FALSE)

Now after that, to find and calculate the allowances, we will be clicking on the C8 cell, inserting the following formula, and pressing the Enter button.

Formula:

=$C$5*VLOOKUP (B8, Database! $E$5:$F$8, 2, FALSE)

Important Note:

And here in this, it should be noted that the respective data range (E5:F8) must be in the absolute reference to avoid further errors while copying down the formula. But, the respective lookup value (B8) needs to be in relative reference as it should be changed concerning the allowance criteria.

And as a result, we can calculate the house rent allowance for employee Mathew Jones under their basic salary. Now we will place our cursor in the bottom right of our selected cell, and a black fill handle will appear on our screen. Drag it downward to copy the formula for all the allowances respectively.
After that, we will calculate all the respective allowances of that particular employee. And for calculating the gross salary, we will be clicking on the C13 cell and inserting the following formula by using the SUM function and clicking on the Enter button.
Formula:
=SUM (C5, C8:C11)

Step 3: Calculating the Amounts which need to be deducted from the basic salary

And the very next step is tocalculatethe deduction from that employee's salary as well, and to accomplish this, we are required to click on the B5 cell and refer to the Calculate Gross Salary sheet's B5 cell, respectively.
And after that, we will click on the C5 cell and refer to the Calculate Gross Salary sheet present under the C5 cell.
Now, we will then click on the C8 cell, write down the following formula, and click on the Enter button, respectively.
Formula:
=$C$5*(VLOOKUP (B8, Database! $E$11:$F$13, 2, FALSE))

Important Note:

Here, it should be noted that the respective Database worksheet's range (E11: F13) should be in the absolute reference, and the basic salary (C5) should also be in absolute reference while copying down the formula to prevent errors. But, the lookup value (B8) should be in the relative reference, as it would change concerning our deduction criteria.

And as a result, we will calculate the provident fund deduction for the employee Mathew Jones. Now, we will place our cursor in the bottom right of the cell. When the fill handle appears, we will drag it below to copy the formula.
After that, we must have the amount of the calculated deduction for the employee Mathew Jones, and we will total it. So, we will click on the C12 cell and insert the following formula, as this formula will sum out the C8 to C10 cell's value and will the Enter button.
Formula:
=SUM (C8:C10)

Step 4: Calculate Net Salary

And lastly, we arerequired to make a salary sheet in Microsoft Excel with a formula, and we need to calculate the employee's total salary.

To achieve this, first and foremost, we will click on the C5 cell and put an equal sign (=) selecting a cell C5 from the Calculate Gross Salary worksheet. Next, we are required to click on the Enter button. Thus, we will have linked the C5 cell with the Calculate Gross Salary's C5 cell.
And similarly, we will be clicking on the C7 cell and will link it with the C13 cell of the Calculate Gross Salary worksheet, respectively.
Next, we will click on the C8 cell and link it with the Calculate Deduction worksheet's C12 cell efficiently.
Last but not least, we will be clicking on the C9 cell and inserting the following formula as this respective formula will subtract the C8 cell from the C7 cell and will pressthe Enter button as well.