How to Calculate Normal Wages Plus Overtime in Excel

Get all the money you have coming to you.

If you work a lot of overtime, you want to make sure you get paid for every hour you work. One of the best ways to track your overtime hours and your overtime pay is with an Excel spreadsheet. You can create a spreadsheet and use it to track your hours each week. Then when you get your paycheck you can compare the totals to what you have in your spreadsheet and bring any discrepancies to your boss's attention right away.

Instructions

1. Open Microsoft Excel and start a new spreadsheet. Type a title for the spreadsheet in cell A1.

2. Create your column labels across the top of the spreadsheet, starting with cell A3. Label cell A3 "Day," cell B3 "Standard Hours," cell C3 "Standard Rate," cell D3 "Overtime Hours" and cell E3 "Overtime Rate."

3. Type the days of the week in cells A4 through A8. You can also enter the days of the month or other relevant labels, i.e. May 1 through May 31.

4. Enter the number of standard and overtime hours for each day of the week or day of the month in cells B4 through B8. Enter your standard pay rate in cell C4. Highlight the cell, press both the "Ctrl" key and "C," then highlight cells C5 through C8 and press the "V" key to copy the pay rate down.

5. Type the number of overtime hours for each day in cells D4 through D8. Move your cursor to cell E4 and type "=C4*1.5." This calculates your overtime pay as 1.5 times your regular rate of pay.

6. Highlight cell E4, hold the "Ctrl" key down and press "C." Highlight cells E5 through E8 and hold the "V" key down to copy the formula to those cells.

7. Place your cursor in cell F3 and type the column heading "Standard Pay." Move to cell F4 and type "=B4*C4." Highlight cell F4, hold the "Ctrl" key down and press "C," then highlight cells F5 through F8 and press "V" to copy the formula.

8. Move to cell G3 and type the heading "Overtime Pay." Move to cell G4 and type "=D4*E4." Highlight the cell, hold the "Ctrl" key down and press "C." Highlight cells G5 through G8 and press "V" to copy the formula.

9. Go to cell H3 and type the heading "Total Pay." Go to cell H4 and type "=F4+G4." Highlight the cell, hold the "Ctrl" key down and press "C." Highlight cells H5 through H8 and press "V" to copy the formula to those cells.

10. Come down to cell F9 and type "=SUM(F4:F8)" to get your standard pay total for the week. Highlight cell F9, hold the "Ctrl" key down and press "C." Highlight cells G9 and H9 and press "V" to copy the formula to the overtime and total pay columns.

References

• Winona State University: SUM Function
• University of South Dakota: Excel Tutorial