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.

Calculate Gross Salary in Excel: 3 Methods

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.

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

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

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

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.

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

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.

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.

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

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

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.

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

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.

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.

Download Practice Workbook

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.

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