How to Create a Salary Sheet in Excel? - Complete Step-by-Step Guide
Last Updated: August 19, 2026 | Reading Time: 10 minutes
Welcome to the most comprehensive guide on "How to Create a Salary Sheet in Excel?" - an essential skill for HR and accounting professionals.
1. Introduction to Salary Sheet
A salary sheet is a document that calculates employee salaries including basic pay, allowances, deductions, and net pay. Excel is the perfect tool for creating salary sheets.
2. Prerequisites
- ✅ Microsoft Excel installed
- ✅ Basic understanding of Excel formulas
- ✅ Employee salary data
3. Step 1: Create Employee List
- Open Excel and create a new workbook.
- In Column A, enter "Employee ID".
- In Column B, enter "Employee Name".
- Add employee details in rows below.
4. Step 2: Set Up Columns
| Column | Header | Description |
|---|---|---|
| A | Employee ID | Unique ID for each employee |
| B | Employee Name | Full name of employee |
| C | Basic Pay | Base salary amount |
| D | DA (Dearness Allowance) | Usually % of basic pay |
| E | HRA (House Rent Allowance) | % of basic pay |
| F | Gross Salary | Sum of all earnings |
| G | PF (Provident Fund) | % of basic pay |
| H | ESI | % of gross salary |
| I | Net Salary | Gross - Deductions |
5. Step 3: Enter Formulas
Formula 1 - DA: =C2*40% (if DA is 40% of basic)
Formula 2 - HRA: =C2*30% (if HRA is 30% of basic)
Formula 3 - Gross Salary: =C2+D2+E2
Formula 4 - PF: =C2*12% (if PF is 12% of basic)
Formula 5 - ESI: =F2*1.75% (if ESI is 1.75%)
Formula 6 - Net Salary: =F2-G2-H2
6. Step 4: Calculate Net Salary
- Enter Basic Pay for each employee.
- Excel will automatically calculate DA, HRA, Gross, PF, ESI.
- Net Salary is calculated as Gross - Deductions.
- Use Fill Handle to copy formulas down.
7. Formatting Tips
- 💰 Format currency cells as Currency or Number.
- 🎨 Use bold for headers.
- 📊 Add AutoSum for totals.
- 🔲 Use border for a professional look.
8. Frequently Asked Questions
❓ How to create a salary sheet in Excel?
Create columns for Basic, DA, HRA, PF, ESI, and use formulas to calculate Gross and Net Salary.
❓ What is Gross Salary?
Gross Salary is the total salary before deductions (Basic + DA + HRA + other allowances).
❓ What is Net Salary?
Net Salary is the amount an employee receives after all deductions (Gross - PF - ESI - TDS).