How to Calculate Gross Salary in Excel: 3 Useful Methods

Method 1 – Utilizing PRODUCT Function to Calculate Gross Salary in Excel

You can use the PRODUCT function when salary is based on Working Hours. We made a dataset, which is the Working Activity Sheet of Employees. The dataset has Name of the 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.

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

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

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

You’ll find all the Gross Salaries from cells F6 to F14.

Method 2 – Applying PRODUCT Function Considering Overtime

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. 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.

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

Clicking ENTER you will find the output as 2,925.

Use Fill Handle to find all the Gross Salaries.

Method 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.

The dataset is like this.

You need to put the formula in cell H5 like this.

Press ENTER to get the output in the H5 cell.

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

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

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