Microsoft Excel has huge uses in different sectors. Mostly, medium and small enterprises and companies use Microsoft Excel to save costs from using paid software. In this article, we will show how to calculate annual salary in Excel with detailed steps.

**Table of Contents**hide

**Download Sample Workbook**

Download the following practice workbook to exercise while you are reading this article.

**Steps to Calculate Annual Salary in Excel**

In the below section, we will show each step to calculate the annual salary of employees of any organization. We are considering a dataset that comprises **10 **employees. We will show the **CTC**, gross salary, deduction from salary, and finally the net salary.

**Formula to Calculate Salary in Excel:**

Salary is the payment of an employee paid by the owing company. But there is some deduction from the salary like **PF**, income tax, etc. The formula to calculate the Net salary is given below.

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

**📌 ****Step 1: Make a List of Employees with Supporting Data**

- First, we collect the name and positions of the employee in the dataset. We also set the format of the salary sheet.

- Now, we add another sheet of supporting information in the Excel file.

So, we have stored gender, month, and year information here.

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

**📌 ****Step 2: Prepare Main Sheet Columns to Insert Data and Calculate Annual Salary**

In this section, we will prepare the gender, year, and month column of Excel cells to insert information. We will apply the drop-down list feature to choose gender, year, and month.

- First, we will set a drop-down list for the
**Gender**Go to**Cell D6**. - Then click on the
**Data**tab. - Then choose
**Data Validation**from the**Data Tools**section.

- The
**Data Validation**window appears. - Now, select
**List**in the**Allow**field.

- Next, choose the gender options from the
**Supporting Info**sheet in the**Source**field. - Finally, click on
**OK**.

- Look at the data set now.

We can see a drop-down list on **Cell D6**.

- After this, expand the drop-down list to the rest of the cells using the
**Fill Handle**icon.

- Now, set the gender of the employees from the drop-down list.

Similarly, we will add drop-down lists for the **Year **and **Month **columns.

- View the rest of the columns.

- Now, go to
**Cell E6**of the**Year**column. - Then enter the
**Data Validation**window as shown before. - After that, choose the list of years as the
**Source**from the**Supporting Info**sheet.

- We can see a drop-down list is added to the dataset.

- We want to select the year one time for each sheet. So, we put the following formula that will follow
**Cell E5**.

**)**

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

The year value showing on **Cell E5** will reflect on the rest of the cells.

- Now, press the
**Enter**button and then drag the**Fill Handle**icon.

- After that, set a year on
**Cell E5**from the drop-down list and that will reflect on the rest of the cells.

- Next, add another drop-down list for the month.
- Now, follow the steps of
**Data Validation**as shown before. - Then choose the
**Source**from the**Supporting Info**sheet.

- Look at the following image. A drop-down list there has been added to
**Cell F6**

- Now, put the following formula on
**Cell F7**.

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

- Finally, press the
**Enter**button and double-click on the**Fill Handle**icon.

- After that, select the desired month from the drop-down list.

- Go to
**Cell G6**and put the below 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," "))))))))))))`

This formula shows the number of days based on the month shown on **Cell F6**.

- Press the
**Enter**button and pull the**Fill Handle**icon.

We have already inserted the general information of each employee in the salary sheet.

Now, we will add the **CTC **and leave information of the employees.

**Read More:** **Daily Wages Sheet Format in Excel (with Quick Steps)**

**Similar Readings**

**How to Calculate Prorated Salary in Excel (3 Suitable Ways)****How to Create a Salary Increase Matrix in Excel (With Easy Steps)****Attendance Sheet with Salary in Excel Format (with Easy Steps)****How to Calculate Average Salary in Excel (6 Effective Methods)****How to Calculate Monthly Salary in Excel (with Easy Steps)**

**📌 ****Step 3: Insert Leave Information**

- First, expand the dataset to insert the rest of the information.

- Manually insert the leaves of a certain month.

- Move to
**Cell 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 the
**Enter**button and pull the**Fill Handle**icon.

- Then, enter the
**CTC**of each employee manually.

Here, we selected **February 2016**. We will calculate the salary of employees for that month.

- Put the following formula on
**Cell L6**to get the**CTC**for each employee for specific months considering leaves.

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

- Now, drag the
**Fill Handle**icon downwards.

**Read More:** **Leave Salary Calculation in Excel (With Easy Steps)**

### 📌 Step 4: Add Salary Data

In this step, we will **calculate the gross salary**. Gross salary is divided into some components. Basic salary, **House Rent Allowance (HRA)**, Medical allowance, TA, Conveyance, etc. are the components of gross salary. In the following section, we will show the calculation of gross salary.

- First, the basic salary which is
**50%**of the**CTC**. - Put the following formula on
**Cell M6**for that.

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

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

- On the other hand,
**HRA**is**25%**of the**CTC**. Put the below formula on**Cell N6**.

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

- Double-click the
**Fill Handle**icon.

- Medical allowance is
**10%**of the**CTC**. Apply this formula to**Cell O6**.

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

- Again, drag the
**Fill Handle**icon.

- This time, insert the
**TA**,**Conveyance**, and**Bonus**values manually.

- Now, calculate the total using the
**SUM function**on**Cell S6**. Apply the following formula.

`=SUM(M6:R6)`

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

We get the gross salary of each employee.

**Read More:** **How to Calculate Bonus on Salary in Excel (7 Suitable Methods)**

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

We calculated the gross salary in the previous section. But this is not the **net salary**. We have to deduct the **Provident Fund** (**PF**), **Personal Tax**, **and TDS **from this gross salary.

- First, add columns on the right side for the deduction.

- We calculate the
**PF**based on the**Basic Salary**which is**9%**. Put the following formula on**Cell T6**.

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

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

After that, we put a formula to calculate the personal tax. This is based on the annual income of an employee. There are different levels of tax based on income quantity.

- First, we create a formula based on those values and insert that into
**Cell 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`

- After that, press the
**Enter**button and drag the**Fill Handle**icon.

- After that, put the
**TDS**manually.

- Then, add all the deducted values using the following formula on
**Cell W6**.

`=SUM(T6:V6)`

- Finally, press
**Enter**and drag the**Fill Handle**icon.

**Read More:** **How to Calculate Income Tax on Salary with Example in Excel**

### 📌 Step 6: Calculate Net Salary

Now, we will use this deducted value to calculate the net salary.

- Put the following formula on
**Cell X6**.

`=$S6-$T6`

- Finally, drag the
**Fill Handle**icon.

**Read More:** **How to Calculate Basic Salary in Excel (3 Common Cases)**

**Conclusion**

In this article, we described all the steps of how to calculate annual salary in Excel. I hope this will satisfy your needs. Please have a look at our website **ExcelDemy** and give your suggestions in the comment box.

**Related Articles**

**How to Calculate Salary Increase Percentage in Excel****Create Tally Salary Slip Format in Excel (With Easy Steps)****How to Create Salary Slip Format with Formula in Excel Sheet****Create a Monthly Salary Sheet Format in Excel (with Easy Steps)****How to Calculate DA on Basic Salary in Excel (3 Easy Ways)****How to Model Salary Regression Analysis in Excel (2 Ways)**