How to Make a Salary Sheet in Excel

A salary sheet in Excel lists every employee alongside their basic pay, allowances like HRA and DA, deductions like PF and tax, and their final net salary. Two formulas carry the whole thing: gross salary = basic + allowances, and net salary = gross − deductions. This project builds a real, reusable salary sheet from a blank workbook, using the formulas you've already learned.

What a salary sheet contains

A basic Indian salary sheet usually has these columns:

• Basic Pay — the fixed base salary.
• HRA — House Rent Allowance, often a percentage of basic.
• DA — Dearness Allowance.
• Gross Salary — basic + all allowances.
• PF — Provident Fund deduction (often 12% of basic).
• Professional Tax / TDS — other deductions.
• Net Salary — what actually reaches the employee.

Note: PF and tax rules change and vary by company. The percentages below are examples for practice, not tax advice. Confirm your organisation's actual rates.

Step by step: build the sheet

1. Enter the headings

In row 1, type: Emp ID, Name, Basic, HRA, DA, Gross, PF, Prof Tax, Net Salary.

2. Enter a few employees

Fill in Emp ID, Name and Basic for three or four staff. Leave the calculated columns blank for now.

3. Calculate HRA and DA

If HRA is 40% of basic and DA is 10%, in the HRA cell (say D2) write:

=C2*40%

and in DA (E2):

=C2*10%

4. Gross salary

Gross is basic plus allowances. In F2:

=C2+D2+E2

Or use SUM: =SUM(C2:E2).

5. Deductions

PF at 12% of basic, in G2:

=C2*12%

Enter Professional Tax as a flat figure (say 200) in H2, or leave it blank if not applicable.

6. Net salary

Net is gross minus deductions. In I2:

=F2-G2-H2

7. Copy down

Select F2:I2, then drag the fill handle (the small square at the bottom-right of the selection) down to cover all employees. Because these are relative references, each row calculates from its own basic pay.

Worked example

Change any Basic figure and every dependent cell updates on its own.

Make it look professional

• Bold the heading row and give it a fill colour (Home ▸ Fill Color).
• Format money columns as ₹: select them, press Ctrl + 1, choose Number or Accounting.
• Add a total row at the bottom with =SUM() for Gross and Net.
• Freeze the header: View ▸ Freeze Panes ▸ Freeze Top Row, so headings stay visible while you scroll.

Pro tips

• Put the HRA%, DA% and PF% in their own cells at the top and reference them with absolute references ($B$1). Then you can change a rate in one place.
• Use Ctrl + 1 to open Format Cells for quick number and currency formatting.
• Save a blank version as a template so you can reuse it every month.

Common mistakes

• Hard-coding percentages in every formula. If the rate changes, you'd have to edit every cell. Reference a rate cell instead.
• Forgetting to copy formulas down. Only row 2 calculates until you fill down.
• Mixing up gross and net. Gross is before deductions, net is after. Net is always the smaller, final figure.

Key takeaways

• Gross = basic + allowances; Net = gross − deductions.
• Use relative references and copy formulas down the column.
• Store rates in their own cells and reference them absolutely.
• Format money columns and freeze the header for a clean sheet.

Practice task

Build a salary sheet for five employees with your own basic pay figures. Put the HRA, DA and PF percentages in cells at the top, reference them in your formulas, then change one percentage and watch every net salary update.

This is exactly the kind of task employers test in interviews. Build real, job-ready Excel skills in the ADCA program at HCI.