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

People who work in a company mostly need to make a salary sheet to calculate the salary of employees. In Microsoft Excel we can calculate the monthly salary sheet very easily just by using some simple formulas. In this article, I am explaining how to calculate monthly salary in excel.


Download Practice Workbook

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


3 Quick Steps to Calculate Monthly Salary in Excel

In the following article, I have shared 3 quick steps to calculate monthly salary in excel.

Suppose we have a dataset of some Employee Name, Basic Salary of employees, and Total Working Days. Now we are going to calculate the monthly salary in an excel sheet.

Calculate Monthly Salary in Excel


Step 1: Determine Gross Salary for Each Employee

In this step firstly we will make the payment parts where we will calculate all the payments that need to be paid. With basic salary, there are some allowances paid by the company.

In the following screenshot, you will find some new columns where allowances-Conveyance, House Rent, Medical will be calculated according to the company’s terms and conditions. After that, we will calculate the Gross Salary.

  • Select a cell (E7) to calculate conveyance in the chosen cell.
  • Apply the formula-
=IF(C7>=8000,C7*3%,C7*2%)

Where,

  • (C7>=8000,C7*3%,C7*2%) stands for the company’s term. The term is if an employee’s salary is equal to or more than $8000, he/she will get 3% of conveyance, otherwise, he/she will get 2%.

Determine Gross Salary for Each Employee to Calculate Monthly Salary in Excel

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

  • Here we have calculated the conveyance payments for all employees.

Determine Gross Salary for Each Employee to Calculate Monthly Salary in Excel

  • Now, we will calculate the house rent allowance.
  • Choose a cell. Here I have selected cell (F7) to apply the formula.
  • Put the formula down-
=IF(C7>=8000,C7*10%,C7*8%)

Determine Gross Salary for Each Employee to Calculate Monthly Salary in Excel

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

  • Here we have house rent calculated for all the employees.

Determine Gross Salary for Each Employee to Calculate Monthly Salary in Excel

  • To calculate the medical allowances select a cell and apply the formula to the cell. Here I have chosen cell (G7).
  • Write the following formula-
=IF(C7>=8000, C7*4%,C7*3%)

Determine Gross Salary for Each Employee to Calculate Monthly Salary in Excel

  • Click the Enter button to continue.
  • Pull the “fill handle” down to get the output.

  • Now we have calculated the output of medical allowances.

Determine Gross Salary for Each Employee to Calculate Monthly Salary in Excel

  • Finally we can calculate the total gross salary. To do so we will sum all the allowances and then sum with the total working days to get the value.
  • Choose a cell (H7) and apply the following formula-
=SUM(E7:G7)+C7/31*D7

Determine Gross Salary for Each Employee

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

  • So, we have finished and got our values in our gross salary column representing the amount an employee will be paid.

Determine Gross Salary for Each Employee

Read More: How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)


Similar Readings


Step 2: Perform Necessary Deduction from Gross Salary

This time we will calculate the deduction from the gross salary to calculate the monthly salary in excel.

Here we have created some new columns which will represent Income Tax, Provident Fund (PF), and Total Deduction.

  • According to terms income tax is 5% whose salary is over or equal to $9000.
  • To get the output according to the terms choose a cell to apply the formula. I have chosen cell (I7).
  • Write the formula down-
=IF(H7>=9000,H7*1.5%,0)

Perform Necessary Deduction from Gross Salary

  • Press Enter and pull the “fill handle” down.

  • Now we have income tax values in our hand which will be deducted from the gross salary.

Perform Necessary Deduction from Gross Salary

  • To calculate the provident fund select a cell (J7) and write the formula down-
=H7*5%

Perform Necessary Deduction from Gross Salary

  • Click Enter and drag down the “fill handle” to fill with precious output.

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

Perform Necessary Deduction from Gross Salary

Now let’s sum the income tax and provident fund to get the total deduction value.

  • Choose a cell (K7).
  • Apply the formula-
=SUM(I7:J7)

Perform Necessary Deduction from Gross Salary

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

  • Here we have total deduction values.

Perform Necessary Deduction from Gross Salary

Read More: How to Create Salary Slip Format with Formula in Excel Sheet


Step 3: Calculate the Monthly Net Salary

It’s time for the final part to calculate the monthly net salary which will be actually paid after the end of the month.

  • Select a cell (L7) and put the following formula down-
=H7-K7

Calculate the Monthly Net Salary

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

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

Calculate the Monthly Net Salary

  • After all the steps are completed our final table will look like this.

Calculate the Monthly Net Salary


Things to Remember

  • Sometimes a term might come for some employees’ advanced salary. Just open a new worksheet and attach the values in the deduction part using the VLOOKUP function.

Conclusion

In this article, I have tried to cover all the simple methods to calculate monthly salary in excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo