**Formula to Calculate the Salary in Excel:**

**Net Salary = Gross Pay – Total Allowances**

**Step 1 – Create a List of Employees and a Supporting Data sheet**

- Enter name and position of the employee and format the salary sheet.

- Add a sheet with supporting information.

Gender, month, and year were stored here.

** ****Step 2 – Prepare the Columns to Insert Data and Calculate Annual Salary**

Prepare the gender, year, and month columns.

- Set a drop-down list for
**Gender:**Go to**D6**. - Go to the
**Data**tab. - Choose
**Data Validation**in**Data Tools**.

- In the
**Data Validation**window, select**List**in**Allow**.

- Choose the gender options for the
**Supporting Info**sheet in the**Source**field. - Click
**OK**.

You can see a drop-down list in** D6**.

- Apply the drop-down list to the rest of the cells using the
**Fill Handle**.

- Set the gender of the employees in the drop-down list.

- Add drop-down lists for the
**Year**and**Month**columns.

This is the output.

- Go to
**E6**. - Open the
**Data Validation**window. - Choose the list of years as
**Source**in the**Supporting Info**sheet.

A drop-down list is added to the dataset.

- To select the year, enter the following formula in
**E5**.

**)**

`=IF($B7=0," ",$E$6`

- Press
**Enter**and then drag the**Fill Handle**.

- In
**E5**, choose a year from the drop-down list and it will be displayed in the rest of the cells.

Add another drop-down list for month.

- Open the
**Data Validation**window. - Choose the list of years as
**Source**in the**Supporting Info**sheet.

A drop-down list is added to **F6.**

- Enter the following formula in
**F7**.

`=IF($B7=0," ",$F$6)`

- Press
**Enter**and double-click the**Fill Handle**.

- Select a month from the drop-down list.

- Go to
**G6**and enter the formula.

`=IF(F6="January",31,IF(F6="February",28,IF(F6="March",31,IF(F6="April",30,IF(F6="May",31,IF(F6="June",30,IF(F6="July",31,IF(F6="August",31,IF(F6="September",30,IF(F6="October",31,IF(F6="November",30,IF(F6="December",31," "))))))))))))`

It shows the number of days based on the month shown in** F6**.

- Press
**Enter**and double-click the**Fill Handle**.

** ****Step 3 – Enter the Leave Information**

- Expand the dataset to enter additional information.

- Insert the leaves manually.

- Go to
**J6**to calculate the number of worked days of each employee.

`=IF(B6=0,"",ROUND(K6/G6*J6,-1))`

It is calculated based on the allowed leaves and taken leaves.

- Press
**Enter**and double-click the**Fill Handle**.

- Enter the
**CTC**of each employee manually.

Here, **February 2016 **was selected. Calculate the salary for that month.

- Enter the following formula in
**L6**to get the**CTC**.

`=IF(B6=0,"",ROUNDDOWN(K6/G6*J6,0))`

- Drag down the
**Fill Handle**.

### Step 4 – Add Salary Data

- The basic salary is
**50%**of the**CTC**. - Enter the following formula in
**M6**.

`=ROUNDDOWN($L6*50%,0)`

- Press
**Enter**and drag the**Fill Handle**.

**HRA**is**25%**of the**CTC**. Enter the formula in**N6**.

`=ROUNDDOWN($L6*25%,0)`

- Drag the
**Fill Handle**.

- Medical allowance is
**10%**of the**CTC**. Use this formula in**O6**.

`=ROUNDDOWN($L6*10%,0) `

- Drag the
**Fill Handle**.

- Enter the
**TA**,**Conveyance**, and**Bonus**values manually.

- Calculate the total using the
**SUM function**in**S6**. Use the following formula.

`=SUM(M6:R6)`

- Press
**Enter**and drag the**Fill Handle**.

The gross salary of each employee is displayed.

**Step 5 – Calculate PF, Tax & TDS and Deduct Them from Gross Salary**

- Add columns on the right side for the deductions.

- Calculate the
**PF**based on the**Basic Salary**–**9%**. Enter the following formula in**T6**.

`=ROUNDUP($M6*9%,0) `

- Press
**Enter**and drag the**Fill Handle**.

Enter a formula to calculate the personal tax, based on the annual income.

- Create a formula based on those values and use it in
**U6**.

`=(IF(M6*12>523600,157804.25+(M6*12-523600)*37%,IF(M6*12>209425,47843+(M6*12-209425)*35%,IF(M6*12>164925,33603+(M6*12-164925)*32%,IF(M6*12>86375,14751+(M6*12-86375)*24%,IF(M6*12>40525,4664+(M6*12-40525)*22%,IF(M6*12>9950,995+(M6*12-9950)*12%,IF(M6*12<=9950,M6*12*10%,""))))))))/12`

- Press
**Enter**and drag the**Fill Handle**.

- Enter the
**TDS**manually.

- Add all the deducted values using the following formula in
**W6**.

`=SUM(T6:V6)`

- Press
**Enter**and drag the**Fill Handle**.

### Step 6 – Calculate the Net Salary

- Enter the following formula in
**X6**.

`=$S6-$T6`

- Drag the
**Fill Handle**.

Download the practice workbook.

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