.jpeg)
A salary sheet in Excel is used to record an employee's monthly salary, allowances, deductions, and final payable amount. It helps businesses calculate salaries in an organized way and reduces the need for manual calculations. When the basic salary or any salary rate changes, Excel automatically updates the related values through formulas.
This article explains how to create a salary sheet in Excel, calculate each salary component using formulas, find the total salary expense, and deduct payment for unpaid leave. It also includes a sample salary sheet with formulas, output, and explanations to help beginners understand the process.
Note: The rates used in this example, such as HRA at 40%, PF at 12%, a fixed allowance of ₹2,000, and professional tax of ₹200, are for learning purposes only. Actual salary calculations depend on company policy and applicable laws.
Salary Sheet Columns and Their Meaning
A salary sheet contains different columns for recording employee details, earnings, deductions, and net salary. Each column has a specific purpose in the calculation.
| Column | Definition |
|---|---|
| Emp ID | A unique identification number assigned to each employee. |
| Name | The name of the employee receiving the salary. |
| Basic Salary | The fixed main component of an employee's salary before adding allowances or deducting amounts. |
| HRA | House Rent Allowance, which helps employees meet their housing expenses. In this example, it is calculated as a percentage of basic salary. |
| Allowance | An additional amount paid to an employee along with the basic salary. The amount may be fixed or depend on company policy. |
| Gross Salary | The total earnings before deductions. In this example, it includes basic salary, HRA, and allowance. |
| PF | Provident Fund, a retirement savings contribution deducted from an employee's salary according to applicable rules. |
| Professional Tax | A tax on employment levied by certain state governments, where applicable. The amount depends on the relevant state rules. |
| Total Deductions | The combined amount deducted from the employee's salary, such as PF and professional tax in this example. |
| Net Salary | The final amount payable to the employee after subtracting total deductions from gross salary. |
Step 1: Create the Salary Sheet Format
First, create a table in Excel to store employee details and calculate their monthly salaries. Enter the employee information in cells A1 and keep the salary rates in a separate table in cells L1.
Keeping the rates separate makes the sheet easier to maintain. For example, if the HRA percentage changes, you only need to update its value in the rates table rather than edit every formula.
Employee Salary Table
Create the following columns in Excel and enter the sample employee details.
| Emp ID | Name | Basic | HRA | Allowance | Gross Salary | PF | Professional Tax | Total Deductions | Net Salary |
|---|---|---|---|---|---|---|---|---|---|
| 101 | Amit | 30000 | |||||||
| 102 | Neha | 35000 | |||||||
| 103 | Rahul | 32000 | |||||||
| 104 | Priya | 38000 |
Salary Rates Table
Enter the following values in cells L1.
| Rate | Value |
|---|---|
| HRA % | 40% |
| PF % | 12% |
| Allowance | 2000 |
| Professional Tax | 200 |
The values in this table are used by the formulas to calculate each employee's salary.
Step 2: Add the Salary Formulas
After entering the employee details and salary rates, use Excel formulas to calculate the remaining columns. These formulas perform the calculations automatically, so you do not have to work out each employee's salary manually.
Enter the formulas in row 2, starting from cell D2, and copy them down to row 5.
| Column | Formula | Explanation |
|---|---|---|
| D2 (HRA) | =C2*$M$2 | Multiplies the basic salary by the HRA percentage stored in M2. |
| E2 (Allowance) | =$M$4 | Retrieves the fixed allowance amount from M4. |
| F2 (Gross Salary) | =C2+D2+E2 | Adds the basic salary, HRA, and allowance to calculate total earnings. |
| G2 (PF) | =C2*$M$3 | Calculates PF using the basic salary and the percentage stored in M3. |
| H2 (Professional Tax) | =$M$5 | Retrieves the professional tax amount from M5. |
| I2 (Total Deductions) | =G2+H2 | Adds PF and professional tax to calculate total deductions. |
| J2 (Net Salary) | =F2-I2 | Subtracts total deductions from gross salary to calculate the payable amount. |
Understanding the Formulas with an Example
Consider Amit, whose basic salary is ₹30,000. The HRA rate is 40%, the fixed allowance is ₹2,000, the PF rate is 12%, and the professional tax is ₹200.
- HRA: The formula calculates ₹30,000 × 40% = ₹12,000.
- Allowance: The fixed allowance is ₹2,000.
- Gross Salary: Adding ₹30,000 + ₹12,000 + ₹2,000 gives ₹44,000.
- PF: The formula calculates ₹30,000 × 12% = ₹3,600.
- Total Deductions: Adding ₹3,600 + ₹200 gives ₹3,800.
- Net Salary: Subtracting ₹3,800 from ₹44,000 gives ₹40,200.
The dollar signs in references such as $M$2 and $M$3 make them absolute cell references. When you copy a formula to another row, these references remain fixed, while references such as C2 change to C3, C4, and so on. This allows every employee's salary to be calculated using the same rates.
Output
After entering the formulas and copying them down, the salary sheet displays the following results.
| Emp ID | Name | Basic | HRA | Allowance | Gross Salary | PF | Professional Tax | Total Deductions | Net Salary |
|---|---|---|---|---|---|---|---|---|---|
| 101 | Amit | 30000 | 12000 | 2000 | 44000 | 3600 | 200 | 3800 | 40200 |
| 102 | Neha | 35000 | 14000 | 2000 | 51000 | 4200 | 200 | 4400 | 46600 |
| 103 | Rahul | 32000 | 12800 | 2000 | 46800 | 3840 | 200 | 4040 | 42760 |
| 104 | Priya | 38000 | 15200 | 2000 | 55200 | 4560 | 200 | 4760 | 50440 |
The output shows that Excel calculates each employee's earnings, deductions, and net salary based on the values entered in the employee table and rates table.
Step 3: Calculate the Total Salary Expense
A total row helps you find the combined gross salary, deductions, and net salary for all employees. It is useful for checking the overall monthly payroll amount.
If the employee records are in rows 2 through 5, enter the following formulas in the appropriate cells of row 6:
| Column | Formula | Purpose |
|---|---|---|
| F6 (Gross Salary) | =SUM(F2:F5) | Adds the gross salaries of all employees. |
| I6 (Total Deductions) | =SUM(I2:I5) | Adds the total deductions for all employees. |
| J6 (Net Salary) | =SUM(J2:J5) | Calculates the total net salary payable to all employees. |
Output
| Description | Gross Salary | Total Deductions | Net Salary |
|---|---|---|---|
| Total | 197000 | 17000 | 180000 |
The total gross salary is ₹1,97,000, while the combined deductions are ₹17,000. After subtracting the deductions, the total net salary payable is ₹1,80,000.
The SUM() function adds the values in a selected range of cells. It is helpful for calculating payroll totals without adding each employee's salary manually.
Step 4: Deduct Salary for Unpaid Leave
Loss of Pay (LOP) refers to the salary deducted when an employee takes leave for which payment is not provided under the company's leave policy. Excel can calculate this deduction by using the gross salary, the number of days used for salary calculation, and the number of unpaid leave days.
To calculate LOP, add two columns to the salary sheet: LOP Days and LOP Deduction.
Suppose Amit has taken two unpaid leave days in a month, and the company uses a 30-day salary calculation basis. If his LOP Days value is entered in K2, enter the following formula in L2:
=ROUND(F2/30*K2,0)
Explanation
The formula divides Amit's gross salary by 30 to calculate the daily salary, multiplies it by the number of unpaid leave days, and rounds the result to the nearest whole number.
For Amit:
- Gross salary = ₹44,000
- Daily salary = ₹44,000 ÷ 30 = ₹1,466.67
- LOP deduction for two days = ₹1,466.67 × 2 = ₹2,933.33
- Rounded LOP deduction = ₹2,933
To calculate the salary after the LOP deduction, use the following formula:
=F2-I2-L2
The resulting net salary is ₹37,267 after subtracting the regular deductions and LOP deduction from the gross salary.
Some companies use 26 working days, 30 days, or the actual number of calendar days in the month to calculate unpaid leave deductions. Use the method followed by the company and applicable rules.
Step 5: Format the Salary Sheet
Formatting makes a salary sheet easier to read and helps users identify important figures quickly.
- Format salary amounts: Select the salary columns and press Ctrl + 1. Choose Number with a thousands separator or Currency with the ₹ symbol.
- Highlight the headings: Select the header row and apply bold text, a background colour, and suitable alignment.
- Keep headings visible: Open View and select Freeze Panes > Freeze Top Row so the column headings remain visible while scrolling.
- Validate salary entries: Select the Basic Salary cells and choose Data > Data Validation. Set the allowed value to a decimal or whole number greater than zero.
- Highlight the total row: Apply bold formatting to the total row so the combined salary figures are easy to identify.
Important Points About Salary Sheets in Excel
- Keep salary rates in separate cells so you can update them without changing every formula.
- Use absolute cell references, such as $M$2, when a formula needs to use the same rate for multiple employees.
- Gross salary represents earnings before deductions, while net salary represents the amount payable after deductions.
- Use the SUM() function to calculate total payroll amounts and check the results.
- Calculate LOP according to the company's salary calculation method and unpaid leave policy.
- Treat the rates in this example as sample values, not universal payroll rules.
For more Excel practice, explore Excel Interview Questions on CodingRoute.