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.

Employee Database & Salary Structure

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

Calculation of Gross Salary

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.

Calculation of Deduction

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

Calculation of Net Salary


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.

Employee's Salary Sheet Dataset


đź“Ś 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.

Create Employee Database

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

Record Salary Structure

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

Employee Database & Salary Structure to Make Salary Sheet with Formula

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.

Using Data Validation Tool to Select Employee

  • 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.

Creating Employee's Name Dropdown List

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

Selecting one Employee from 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.

Choosing Mathew Jones to Make Salary Sheet with Formula

  • 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.
=VLOOKUP(B5,Database!B4:C14,2,FALSE)

Using VLOOKUP Function to Extract Basic Salary

🔎 Formula Breakdown:

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

It looks at the B5 cell value in the Database worksheet’s B14:C14 range. It returns the respective 2nd columns result from the following selection where the B5 cell’s value is found.

Result: 150,000

  • Now, for finding and calculating the allowances, click on the C8 cell and insert the following formula. Subsequently, press the Enter button.
=$C$5*VLOOKUP(B8,Database!$E$5:$F$8,2,FALSE)

Calculating the Allowances in order to Make Salary Sheet in Excel with Formula

🔎 Formula Breakdown:

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

It returns the respective second column value from the Database worksheet’s E5:F8 range where the value of the B8 cell from this worksheet is found there.

Result: 50%

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

It multiplies the C5 cell’s value with the previous result.

Result: $ 75,000

Note:

Here, the data range (E5:F8) should be in absolute reference to avoid further errors when copying the formula. But, the lookup value (B8) should be in relative reference as it should be changed with respect to the allowance criteria. Again, the basic salary (C5) reference should also be in absolute reference when multiplying. Use the dollar sign ($) or press the F4 key to make cells absolute.

  • 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.

Drag Fill Handle to Copy Formula

  • 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.
=SUM(C5,C8:C11) 

Use the SUM Function to Calculate Gross Salary in order to Make Salary Sheet in Excel with Formula

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

Calculation of Gross Salary to Make Salary Sheet in Excel with Formula

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


Similar Readings


đź“Ś 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.

Referencing to the Employee's Name

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

Referencing to the Employee's Basic Salary

  • At this time, click on the C8 cell and write the following formula. Subsequently, press the Enter button.
=$C$5*(VLOOKUP(B8,Database!$E$11:$F$13,2,FALSE))

Calculating the Deduction to Make Salary Sheet in Excel with Formula

🔎 Formula Breakdown:

VLOOKUP(B8,Database!$E$11:$F$13,2,FALSE)

This returns the respective second column value from the Database worksheet’s E11:F13 range upon the condition of finding the B8 cell’s value from this worksheet in the specified range. 

Result: 10%

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

This multiplies the C5 cell’s value with the previous result.

Result: $ 15,000

Note:

Here, the Database worksheet’s range (E11:F13) should be in absolute reference and the basic salary (C5) should also be in absolute reference when copying the formula for preventing errors. But, the lookup value (B8) should be in relative reference, as it would change with respect to your deduction criteria. You can make a cell absolute by putting a dollar sign ($) or simply pressing the F4 key.

  • 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.

Using Fill Handle to Copy 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.
=SUM(C8:C10)

Using SUM Function to Calculate Total Deduction to Make Salary Sheet in Excel with Formula

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

Calculation of Deduction to Make Salary Sheet in Excel with Formula


đź“Ś 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.

Referencing to the Basic Salary of the Employee

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

Referencing to the Gross Salary of the Employee

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

Referencing to the Deduction of the Employee's Basic Salary

  • 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.
=C7-C8

Calculating the Net Payable Salary to Make Salary Sheet in Excel with Formula

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

Calculation of Net Salary to Make Salary Sheet in excel with Formula

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

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo