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.
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.
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.
Similar Readings
- How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)
- Create Salary Slip Format with Formula in Excel Sheet
- How to Create Tally Salary Slip Format in Excel (With Easy Steps)
- calculate salary increase percentage in Excel [Free Template]
- How to Create Automatic Salary Slip Generator Using Excel
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)))
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.
=SUM(D5:G5)
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.
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.
