How to Make Salary Sheet in Excel with Formula (with Detailed Steps)

A salary sheet is a very handy tool regarding the distribution of salaries among the employees of a company. There are many allowances and deductions for the employees according to different company structures. We can make a salary sheet by calculating and adjusting these amounts with basic salary through Excel very easily. In this article, I will show you step-by-step guidelines to make a salary sheet in Excel with formula.

Download Sample Workbook

You can download and practice from our workbook here for free!

What Is a Salary Sheet?

A salary sheet is a report where the net payable amount as salary to an employee is recorded. The basic wage of an employee, extra allowances, and deductions are recorded here. Upon these calculations, gross salary and net payable salary are calculated and recorded.

Common Salary Sheet Components

You can discretize the salary sheet in mainly four components. Such as :

1. Employee Database & Salary Structure

This component is comprised of the employee database which means the employee’s name and their basic salary. Here, the salary structure is also declared. Like which allowances the company provides and how much. Besides, It contains the information about which deductions are cut from the salary and how much of the salary.

2. Gross Salary Calculation

In this portion, we calculate the allowances distinctively for each employee. For example, the allowances are the house rent allowance, transport allowance, flexible benefits plan, etc.  Consequently, the basic wage and the total allowances are summed up to calculate the gross salary. So, the formula would look like this.

Gross Salary = Basic Salary + Allowances

3. Calculation of Deduction

There are some deductions like tax, provident fund, insurance, etc. are cut from the basic salary. In this component, these deductions are calculated for each employee on the base of their basic salary and the salary structure database’s deduction percentage.

4. Net Payable Salary 

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

Net Payable Salary = Gross Salary – Deductions

Steps to Make a Salary Sheet in Excel with Formula

Suppose, you have a dataset of a company for 10 employees’ names and basic salaries along with the company’s structure database. Now, you need to create the salary sheet for the company. Follow the step-by-step guidelines to accomplish this.

📌 Step 1: Create Employee Database & Salary Structure

The very first, you need to prepare your employee database and salary structure.

• For doing this, open a new worksheet and make two columns on the left side that will contain the employee’s name and basic salary.

• Following, record the allowance percentage and deduction percentage of the company on the right side of the employee’s name and basic salary.

Thus, you have created the employee database and salary structure in an organized structure. And, the result sheet would look like this.

Read More: How to calculate salary increase percentage in Excel [Free Template]

📌 Step 2: Calculate Gross Salary

Now, you need to calculate the allowances from the basic wage of the employees and calculate the gross salary.

• To do this, at first, you need to find the basic salary of an employee from the dataset. Regarding these, click on the B5 cell >> go to the Data tab >> Data Tools group >> Data Validation tool >> Data Validation… option.

• As a result, the Data Validation window will appear. At the Settings tab, choose the option List from the Allow: dropdown list. Subsequently, in the Source: text box, refer to the cells B5:B14 of the Database worksheet. Finally, click on the OK button.

• Consequently, you will see that the B5 cell has all the names of the employees in the dropdown list.

• Now, you can choose any of the employees’ names to calculate their salary. Suppose, we have chosen the first employee’s name. So, you will see Mathew Jones in the B5 cell.

• Next, to find the basic salary of the following employee from the database, select the C5 cell and write the following formula. Here, the formula uses the VLOOKUP function to find the value. Next, press the Enter button.

• Now, for finding and calculating the allowances, click on the C8 cell and insert the following formula. Subsequently, press the Enter button.

• As a result, you will calculate the house rent allowance for Mathew Jones with respect to his basic salary. Now, place your cursor in the bottom right position of your cell. Subsequently, a black fill handle will appear. Drag it downward to copy the formula for all the allowances below.

• So, you will calculate all allowances of that particular employee. Now, to calculate the gross salary, click on the C13 cell and insert the following formula using the SUM function. Finally, press the Enter button.

Thus, you will have the allowances and gross salary calculated for the particular employee. And, the sheet should look like this.

Read More: Per Day Salary Calculation Formula in Excel (2 Suitable Examples)

Similar Readings

• Calculate HRA on Basic Salary in Excel (3 Quick Methods)
• How to Calculate DA on Basic Salary in Excel (3 Easy Ways)

📌 Step 3: Calculate Amounts to Deduct

The next step is to calculate the deduction from that employee’s salary.

• To accomplish this, at first, click on the B5 cell and refer to the Calculate Gross Salary sheet’s B5 cell.

• Similarly, click on the C5 cell and refer to the Calculate Gross Salary sheet’s C5 cell.

• At this time, click on the C8 cell and write the following formula. Subsequently, press the Enter button.

• As a result, you will calculate the provident fund deduction for Mathew Jones. Now, place your cursor in the bottom right position of the cell. Following, when the fill handle appears, drag it below to copy the formula.

• Now, you have calculated all deductions for Mathew Jones. Now, you need to total it. So, click on the C12 cell and insert the following formula. This formula will sum the C8 to C10 cell’s value. Subsequently, press the Enter button.

Thus, you have calculated all the deductions individually and totally in this sheet. For instance, the outcome will look like this.

📌 Step 4: Calculate Net Salary

Lastly, to make a salary sheet in Excel with a formula, you need to calculate the total salary of the employee.

• To achieve this, first and foremost, click on the C5 cell. Subsequently, put an equal sign (=) and select cell C5 from the Calculate Gross Salary worksheet. Next, press the Enter button. Thus, you have linked the C5 cell with the Calculate Gross Salary’s C5 cell.

• Similarly, click on the C7 cell and link it with the C13 cell of the Calculate Gross Salary worksheet.

• Next, click on the C8 cell and link it with the Calculate Deduction worksheet’s C12 cell.

• Last but not least, click on the C9 cell and insert the following formula. This formula will subtract the C8 cell from the C7 cell. Subsequently, press the Enter button.

Thus, you can calculate the summary of the salary sheet of the employee. And, the outcome should look like this.

Read More: How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)

Conclusion

To conclude, in this article, I have shown you step-by-step guidelines to make salary sheet in Excel with formula. Follow the guidelines carefully and make your own salary sheet according to your company. You can also download the template from here for free. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to find more articles like this. Thank you!

Related Articles

• How to Create Salary Slip Format with Formula in Excel Sheet
• Create Tally Salary Slip Format in Excel (With Easy Steps)
• How to Calculate Bonus on Salary in Excel (7 Suitable Methods)