How to Calculate Annual Salary in Excel (with Detailed Steps)

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.

Calculate Annual Salary in Excel: Simple Steps

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.

📌 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.

📌 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.

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.

• 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.

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.

📌 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.

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.

• Now, drag the Fill Handle icon downwards.

📌 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.

• 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.

• Double-click the Fill Handle icon.

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

• 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.

• Press the Enter button and drag the Fill Handle icon.

We get the gross salary of each employee.

📌 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.

• 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.

• 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.

• Finally, press Enter and drag the Fill Handle icon.

📌 Step 6: Calculate Net Salary

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

• Put the following formula on Cell X6.

• Finally, drag the Fill Handle icon.

Download Sample Workbook

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

Conclusion

In this article, we described all the steps of how to calculate annual salary in Excel. I hope this will satisfy your needs.

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