How to Calculate Gross Salary in Excel (3 Useful Methods)

Gross Salary calculation is an unavoidable calculation in every office and organization. You need to give salary to every employee depending on this salary sheet. The topic of how to calculate gross salary in Excel will show you how to maintain this sheet properly. In this article, we’ll try to discuss how to calculate gross salary in Excel.


Download Practice Workbook


3 Methods to Calculate Gross Salary in Excel

The total monetary compensation that an employee receives during a pay period is known as gross salary. Gross compensation includes overtime pay, tips, commissions, and bonuses. It excludes non-cash benefits like health insurance. Additionally, salary can be two types.

  • Salary based on Working Hours
  • Salary based on Basic Salary

Depending on the specifics of your work, you can calculate gross salary using a variety of functions. Let’s discuss the functions.


1. Utilizing PRODUCT Function to Calculate Gross Salary in Excel

You can use the PRODUCT function when salary is based on Working Hours. To understand this formula well we have made a dataset which is the Working Activity Sheet of Employees. The dataset has Name of Employee, Designation, Working Hours, and Hourly Pay Rate in USD. It is like this.

how to calculate gross salary in Excel using PRODUCT function

You need to write the formula in the F5 cell like this.

=PRODUCT(D5,E5)

Here, D5 and E5 refer to Working Hours and Hourly Pay Rate of Jane respectively.

how to calculate gross salary in Excel using PRODUCT function

Secondly, you need to press ENTER and the output will be 2,250.

how to calculate gross salary in Excel using PRODUCT function

Thirdly, you need to use the Fill Handle by dragging the cursor of the mouse downwards. Furthermore, you should hold the right bottom corner of the reference F5 cell like this.

how to calculate gross salary in Excel using PRODUCT function

As a result, you’ll find all the Gross Salaries from cells F6 to F14.

how to calculate gross salary in Excel using PRODUCT function


Similar Readings


2. Applying PRODUCT Function Considering Overtime

Additionally, when employees do overtime you need to take this into consideration. The following picture has an additional column F. It is mainly the Overtime Hours of the employees. Here, to calculate Gross Salary you need to use the PRODUCT function in a complex form. You should write the formula in the G5 cell like this.

=PRODUCT(D5,E5)+PRODUCT(1.5,(PRODUCT(E5,F5)))

Here, D5, E5 and F5 refer to Normal Working Hours, Hourly Pay Rate & Overtime hours of Jane respectively. 1.5 refers that the Overtime Working Hours’ payment is 1.5 times of the Normal Working Hours’ payment.

Formula Breakdown:

This formula has two parts. These two parts are added with the sign “+”.

Firstly, PRODUCT(E5,F5) calculates the products of E5 & F5 cells.

Secondly, this output is multiplied by 1.5 with another PRODUCT function. This part is PRODUCT(1.5,(PRODUCT(E5,F5))

Finally, the output of PRODUCT(1.5,(PRODUCT(E5,F5)) is added to the output of PRODUCT(D5,E5) through the formula PRODUCT(D5,E5)+PRODUCT(1.5,(PRODUCT(E5,F5)))

how to calculate gross salary in Excel using PRODUCT function considering overtime

Eventually, by clicking ENTER you will find the output as 2,925.

Lastly, use Fill Handle to find all the Gross Salaries.

how to calculate gross salary in Excel using PRODUCT function considering overtime


3. Using SUM Function to Calculate Gross Salary in Excel

You need a SUM function to calculate Gross Salary. You should do this when the salary is based on Basic Salary. We have made a dataset which is the Salary Sheet Based on Basic Salary. It includes Name of Employee, Designation, Basic Salary, Conveyance, House Rent & Medical Allowance. We have taken other payments as.

  • Conveyance as 5% of Basic Salary.
  • House Rent as 40% of Basic Salary.
  • Medical Allowance as 15% of Basic Salary.

Finally, the dataset is like this.

using SUM function

Eventually, you need to put the formula in cell H5 like this.

=SUM(D5:G5)

using SUM function

Secondly, you need to press ENTER to get the output in the H5 cell.

Finally, use the Fill Handle to find all other Gross Salaries. Eventually, these outputs are from cells H6 to H14.

As a result, the final Gross Salary sheet will be like this.

using SUM function

Read More: How to Calculate DA on Basic Salary in Excel (3 Easy Ways)


Things to Remember

  • When you need to calculate Gross Salary which is dependent on Basic Salary, you should calculate Conveyance, House Rent, Medical Allowance, or any other allowance individually. Usually you can do this by simple arithmetic calculation.
  • You can take Overtime Payment as any value other than 1.5. It depends on the official regulations.

Conclusion

This article will help you effectively to calculate Gross Salary in Excel. The formulas in Excel in this calculation are very much handy to use. Please feel free to visit our Excel learning page ExcelDemy for further queries.


Related Articles

Towhid

Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo