What Function Can You Use to Calculate Gross Pay on Excel?

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Use SUM, PRODUCT and arithmetical functions to calculate gross pay.
Image Credit: Stockbyte/Stockbyte/Getty Images

Gross pay is the total amount of monetary compensation that an employee receives during a pay period. Gross pay does not include non-monetary benefits such as health insurance, but does include overtime pay, tips, commissions and bonuses. There are several functions you can use to calculate gross pay, depending on the circumstances of employment.

Advertisement

SUM Function

Video of the Day

If your Excel spreadsheet contains several cells for an employee that contain different types of pay, use the SUM function to total the employee's gross pay. Click a cell in which you would like to calculate gross pay, then type the following formula in the box:

Advertisement

Video of the Day

=SUM(A1:A5)

This formula assumes that the employee's separate pay totals are contained in cells A1 through A5; substitute the appropriate cell range in place of "(A1:A5)" in the above example.

Advertisement

PRODUCT Function

When calculating gross pay for hourly employees, it may be appropriate to use Excel's PRODUCT function. The PRODUCT function multiplies values in different cells. This function is useful if one cell contains the employee's hourly pay rate and another contains the number of hours that the employee worked. To use this function, click a cell and type the following formula:

Advertisement

=PRODUCT(A1,A2)

Here, "A1" contains the employee's hourly pay rate and "A2" contains the employee's number of hours worked.

Subtract Function

When deductions such as tax withholding or tip shares are removed from an employee's gross pay, the result is net pay. Deductions are likely to appear as negative values in your spreadsheet. Use a subtraction formula to calculate gross pay from a sheet that contains net pay and deductions. Click an empty cell and type the following formula:

Advertisement

Advertisement

=A1-A2

Here, "A1" contains the net pay data and "A2" contains the negative deduction value. The formula returns the employee's original gross pay.

Complex Function

In some cases you may have to create a custom function to calculate gross pay. For instance, a spreadsheet may contain an hourly pay rate in cell "A1," normal hours in cell "A2" and overtime hours in cell "A3." If the employee receives time-and-a-half pay for overtime, the following formula calculates the employee's gross pay:

Advertisement

= PRODUCT(A1,A2) + PRODUCT (1.5,(PRODUCT(A1,A3))

This formula multiplies the regular hours worked by the hourly pay rate and multiplies the overtime hours worked by the pay rate times 1.5. The results of those two formulas are then added together to calculate the gross pay.

Advertisement

Advertisement

references