**Excel** is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in **Excel**. Sometimes we take the help of **Excel** to calculate monthly salary of employees. In this article, I will show you how to create a monthly salary sheet format in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

Download this workbook and practice while going through the article.

## 6 Easy Steps to Create a Monthly Salary Sheet Format in Excel

This is the dataset for this article. I have some **employees** and their **basic salary**. I will calculate their net salary in this format.

### Step 1: Calculate Allowances of Each Employee from Dataset

First of all, I will calculate the allowances for the employees. Letâ€™s assume that the allowances are **30%** of the basic salary.

- Go to
**D5**. Write down the following formula

`=C5*30%`

- Now press
**ENTER**.**Excel**will calculate the allowances.

- After that, use
**Fill Handle**to**AutoFill**up to**D9**.

**Read More: How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)**

### Step 2: Use SUM Function to Find Gross Salary

The next step is to calculate the **gross salary**. This will be the summation of **Basic salary** and **Allowances**. So I will use **the SUM function**.

- Go to
**E5**and write down the formula

`=SUM(C5:D5)`

- Press
**ENTER**.**Excel**will calculate the**gross salary**.

- After that
**AutoFill**up to**E9**.

**Read More: Per Day Salary Calculation Formula in Excel (2 Suitable Examples)**

### Step 3: Calculate Provident Fund for Each Employee

In this section, I will calculate the provident fund per month. Letâ€™s assume that the salary deduction due to the provident fund is **5%** of the **basic salary**.

- Go to
**C14**and write down the following formula

`=C5*5%`

- Press
**ENTER**.**Excel**will calculate the**deducted salary for PF**.

- After that
**AutoFill**up to**E9**.

### Step 4: Apply IFS Function to Determine Tax Amount

Now I will calculate the tax amount using **the IFS function**. The condition is such that,

- if the
**basic salary**is greater than**$1250**, the tax rate is**15%**of the**basic salary** - If the
**1100 <= basic salary < $1000**, tax rate is**10%**of the**basic salary** - If the
**basic salary**is below**$1000**, the tax rate is**0%**. - Go to
**D14**. Write down the following formula

`=IFS(C5>=1250,C5*15%,C5>=1100,C5*10%,C5<1100,0)`

**Formula Explanation:**

- The
**first logical test**is**C5>=1250**, which is**TRUE**. So**Excel**will not check other tests and return the output as**C5*15%**.

- Now, press
**ENTER**.**Excel**will return the output.

- After that, use the
**Fill Handle**to**AutoFill**up to**D18**.

### Step 5: Calculate Total Deduction from Gross Salary

After that, I will calculate the **total deduction** by adding **PF** and **Tax**.

- Go to
**E14**and write down the formula

`=C14+D14`

- Press
**ENTER**.**Excel**will calculate the**Total Deduction.**

- After that
**AutoFill**up to**E18**.

### Step 6: Calculate Net Salary to Complete Monthly Salary Sheet Format

Finally, I will calculate the **net salary** by subtracting the **total deduction** from the **gross salary**.

- Go to
**F5**and write down the formula

`=E5-E14`

- Now press
**ENTER**.**Excel**will calculate the**net salary**.

- Use the
**Fill Handle**to**AutoFill**up to**F9**

**Read More: How to Make Salary Sheet in Excel with Formula (with Detailed Steps)**

## Things to Remember

**Allowances**may include house rent allowance, medical allowance, travel allowances, etc.**Excel**checks the**logical tests**until it finds one**TRUE**, If**Excel**finds the 1st logical test**TRUE**, it does not check the 2nd, 3rd, and other tests.

## Conclusion

In this article, I have demonstrated **6** easy steps to create a monthly salary sheet format in **Excel**. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment down below.

## Related Articles

**How to Calculate DA on Basic Salary in Excel (3 Easy Ways)****Create Salary Slip Format with Formula in Excel Sheet****How to Create Tally Salary Slip Format in Excel (With Easy Steps)****Calculate Bonus on Salary in Excel (7 Suitable Methods)****How to calculate salary increase percentage in Excel [Free Template]**