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

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.

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:

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

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:

=PRODUCT(A1,A2)

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

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:

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

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:

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