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.

**Table of Contents**hide

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

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

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

`=$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.

**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!