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

Get FREE Advanced Excel Exercises with Solutions!

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.

Watch Video â€“ Make Salary Sheet in Excel

## 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

## Make a Salary Sheet in Excel with Formula: Step-by-Step Procedures

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.

### ðŸ“Œ 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.
`=VLOOKUP(B5,Database!B4:C14,2,FALSE)`

ðŸ”Ž 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)`

ðŸ”Ž 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.

• 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)Â `

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

### ðŸ“Œ 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.
`=\$C\$5*(VLOOKUP(B8,Database!\$E\$11:\$F\$13,2,FALSE))`

ðŸ”Ž 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.

• 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)`

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

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

## How to Make Salary Sheet in Excel: Knowledge Hub

<< Go Back to Salary | Formula List | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio