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

## Common Salary Sheet Components

Part 1 – Employee Database and Salary Structure

This section contains a list of employees and their basic salaries, as well as all fixed modifiers that need to be applied to the salary to get the net earnings

Part 2 – Gross Salary Calculation

This section allows you to calculate the allowances for each employee. The allowances include the house rent allowance, transport allowance, and flexible benefits plan, and are listed in the Database section. The basic wage and the total allowances are summed up to calculate the gross salary:

Gross Salary = Basic Salary + Allowances

Part 3 – Calculation of Deductions

This section calculates deductions on the basic salary, per the information in the Database.

Part 4 – Net Payable Salary

This section calculates the net payable salary:

Net Payable Salary = Gross Salary – Deductions

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

We have a dataset of a company with 10 employees.

### Step 1 – Create the Employee Database and Salary Structure

• Open a new worksheet and make two columns on the left side that will contain the employee names and their basic salaries.

• Record the allowance percentages and deduction percentages of the company in a smaller dataset on the right.

• Here’s our sample.

### Step 2 – Calculate the Gross Salary

• Create a new sheet for the calculator.
• Click on the B5 cell.
• Go to the Data tab, select Data Validation (from Data Tools), and choose Data Validation….

• The Data Validation window will appear. In the Settings tab, choose the option List from the Allow: drop-down list.
• In the Source: text box, refer to the cells B5:B14 of the Database worksheet.
• Click on the OK button.

• The B5 cell has all the names of the employees in the dropdown list.

• We have chosen the first employee’s name.

• Select the C5 cell and insert the following formula, then 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 value that is in the same row as the found cell and in the second column of the array.

Result: 150,000

• Click on the C8 cell and insert the following formula, then 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 value in the second column 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)

Multiplies the C5 cell’s value with the previous result.

Result: \$ 75,000

Note:

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. The basic salary (C5) reference should also be in absolute reference when multiplying. Use the dollar sign (\$) or press the F4 key to make cell references absolute.

• Place your cursor in the bottom-right corner C8. A black fill handle will appear.
• Drag the fill handle down to copy the formula for all the allowances.

• To calculate the gross salary, click on the C13 cell and insert the following formula, then press the Enter button.
`=SUM(C5,C8:C11) `

• Here’s our result.

### Step 3 – Calculate Deductions

• Click on the B5 cell and refer to the Calculate Gross Salary sheet’s B5 cell.

• Click on the C5 cell and refer to the Calculate Gross Salary sheet’s C5 cell.

• Click on the C8 cell and use the following formula, then 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:

The Database worksheet’s range (E11:F13) should be an absolute reference and the basic salary (C5) should also be an absolute reference when copying the formula. The lookup value (B8) should be in relative reference, as it changes with respect to your deduction criteria.

• Drag the fill handle down to fill the column.

• Enter the following formula in C12:
`=SUM(C8:C10)`

• Here’s our result.

### Step 4 – Calculate the Net Salary

• Click on the C5 cell.
• Put an equal sign (=) and select cell C5 from the Calculate Gross Salary worksheet.
• Press the Enter button.

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

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

• Click on the C9 cell and insert the following formula, then press Enter.
`=C7-C8`

• Here’s the result.

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

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

Get FREE Advanced Excel Exercises with Solutions!
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