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.

• Click on the C8 cell and insert the following formula, then press the Enter button.

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

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

• Drag the fill handle down to fill the column.

• Enter the following formula in C12:

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

• Here’s the result.

Download the Sample Workbook

How to Make Salary Sheet in Excel: Knowledge Hub

• Daily Wages Sheet Format in Excel
• How to Create a Monthly Salary Sheet Format in Excel

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