How to Get Gross Wages in Excel
Microsoft's Excel is powerful enough to run the payroll of a small business. If you don't need a full-featured payroll application, however, you can create an Excel worksheet that will calculate employees' gross wages based upon the hours they worked and their pay rate plus any additional supplemental income they earn, such as tips, accrued interest, dividends, commissions, bonuses or severance pay.
Open a blank Excel worksheet.
Type column headings for “Hourly Pay," “Hours Worked," “Regular Hours," “Overtime Hours," “Regular Pay," “Overtime Pay” and “Bonus.”
Type in the wage per hour and the hours worked under the appropriate column.
In the cell under “Regular Hours," type in the formula to display non-over hours. For instance, if the total number of hours worked is in cell B2, type "=IF(B2>=40,40,B2)” into the cell. This formula will display no more than 40 hours of the total worked. Format the result as a "General" number.
In the cell under "Overtime Hours," type in the formula to calculate hours over 40. For instance, if the total number of hours worked is in cell B2, type “=IF(B2>40,50-40,0)” into the cell. Format the result as a "General" number. This formula will calculate and display all hours over 40.
Type the formula to calculate regular, gross pay in the cell under the “Regular Pay” heading. For example, if you entered the pay per hour into cell A2 and your regular hours are in cell C2, type in “=A2*C2” and press the “Enter” key to accept the formula and calculate regular, gross pay.
Type the formula to calculate overtime pay in the cell under the “Overtime Pay” heading. For example, if your pay per hour is in cell A2 ,your overtime hours are in cell D2 and you pay time and a half for overtime hours, type in “=1.5_D2_A2” and press the “Enter” key to accept the formula and calculate overtime.
Type in the dollar amount for bonuses under the “Bonus” column heading.
Select an empty worksheet cell and type in "Gross Wages."
Select an empty worksheet cell beneath the “Gross Wages” label to contain your total.
Type "=sum(" into the empty cell.
Select the dollar amount for the “Regular Pay," hold down the “Shift” key then select the overtime pay amount. While still holding down the "Shift" key, select the bonus amount.
Press “Enter” to accept the “Sum” formula and calculate gross wages.