How to Calculate Monthly Salary in Excel (with Easy Steps)

Suppose we have a dataset of some Employee Name, Basic Salary of employees, and Total Working Days. We’ll use this data to calculate the full salary for the employees.

Step 1 – Determine the Gross Salary for Each Employee

• We’ll extend the dataset with new columns where allowances such as Conveyance, House Rent, and Medical will be calculated according to the company’s terms and conditions.

• Select a cell (E7) to calculate conveyance in the chosen cell.
• Apply the following formula:

(C7>=8000,C7*3%,C7*2%) stands for the company’s requirements. The term is if an employee’s salary is equal to or more than $8,000, they will get 3% of conveyance or will get 2%.

• Press Enter and drag down the “fill handle” to fill.

• We have calculated the conveyance payments for all employees.

• Choose a cell for the house rent allowance. We have selected cell (F7):
• Insert the following formula into the cell:

• Hit the Enter button and then drag the Fill Handle down to fill all the cells.

• We have the house rent calculated for all the employees.

• Choose cell G7.
• Copy the following formula into it:

• Press the Enter key.
• Pull the “fill handle” down to get the output.

• We have calculated the output of medical allowances.

• Choose cell H7 and apply the following formula:

• Hit the Enter button and drag the Fill Handle down to get all the employee’s gross salary.

• We have all the values in the gross salary column.

Step 2 – Perform Necessary Deductions from the Gross Salary

• Add new columns which will represent Income Tax, Provident Fund (PF), and Total Deduction.

• Let’s say the income tax is 5% for a salary equal to or above $9,000.
• Choose cell I7.
• Copy the following formula into it:

• Press Enter and pull the fill handle down.

• We have income tax values in the dataset.

• Select J7 and insert the following:

• Hit Enter and drag down the fill handle to fill the column.

• We have calculated the provident fund values for all the employees.

• Choose the cell K7.
• Apply the following formula:

• Press Enter and fill the cells by dragging the “fill handle” down.

• Here we have total deduction values.

Step 3 – Calculate the Monthly Net Salary

• Select cell L7 and insert the following formula:

• Hit the Enter button and drag the “fill handle” down.

• We have our desired output where we got the output by subtracting the deduction amount from the gross salary.

• Our final table will look like this.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article or use it as a template.

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