Convert an Hourly Rate to an Annual Salary in Excel

To convert an hourly rate to an annual salary in Excel, multiply the rate by the hours per week and by 52 weeks: =B2*C2*52. At $25 an hour and 40 hours a week, that is $52,000 a year before tax. This guide shows how to set up the sheet so one formula works for every employee, plus monthly pay, overtime and the reverse calculation from salary to hourly.

The hourly to annual salary formula#

Annual salary is the hourly rate times the paid hours in a year. The paid hours are hours per week times weeks per year.

=Hourly rate * Hours per week * Weeks per year

In Excel, * means multiply. With the rate in B2 and hours in C2:

=B2*C2*52

A full-time year of 40 hours for 52 weeks is 2,080 paid hours. That is why a quick mental check is "hourly rate × 2,000, plus a little". The basics of * and other operators are in the guide on how to multiply in Excel.

Not every full-time job is 40 hours. These are the paid hours in a 52-week year for common weekly schedules:

If you compare two job offers, one hourly and one salaried, convert both to the same unit first. Then compare the hours too: a higher salary for longer weeks can mean a lower hourly rate.

Set up the sheet for many employees#

Put the number of weeks in its own cell, then point every formula at it. If the weeks change, you edit one cell instead of every formula.

In D2, type:

=B2*C2*$F$1

Press Enter. Then select D2:D5 and fill down (Windows: Ctrl+D; Mac: ⌘D). The results are:

Why the $ signs matter#

The dollar signs in $F$1 make it an absolute reference: a cell address that doesn't change when you copy the formula. Without them, the formula in D3 would point at F2, which is empty, and Maya's salary would show 0.

B2 and C2 have no $, so they move down one row each time: D3 uses B3 and C3, D4 uses B4 and C4. That mix of moving and fixed references is one of the most tested Excel skills. To add the $ signs quickly, click inside F1 in the formula and press F4 on Windows, or ⌘T on a Mac. The guide to the dollar sign in Excel formulas explains all four reference types.

Monthly, biweekly and weekly pay from an hourly rate#

Divide the annual salary by the number of pay periods in a year. Using Maya's $52,000:

Monthly pay often has many decimals. Wrap it in ROUND to keep two: =ROUND(D3/12,2). The ROUND function guide explains the second argument. For a partial year, such as someone who starts in September, see how to prorate in Excel.

Add overtime to the annual figure#

If someone regularly works overtime, add it as a separate part of the formula. Overtime is often paid at 1.5 times the normal rate, but check your own contract and local rules.

Say Maya works 5 overtime hours a week at 1.5 times her $25 rate. Put the overtime hours in a cell, such as G3:

=B3*C3*$F$1 + B3*1.5*G3*$F$1

Excel multiplies before it adds, so no extra brackets are needed. The result is 52,000 + 9,750 = $61,750. To track overtime from real timesheets, see how to calculate overtime hours in Excel.

Convert an annual salary to an hourly rate#

To go the other way, divide the salary by the paid hours in a year. With the salary in B2 and hours per week in C2:

=ROUND(B2/(C2*52),2)

For $60,000 at 40 hours a week, that is 60,000 ÷ 2,080 = 28.846…, which ROUND turns into 28.85. The brackets around C2*52 matter: without them, Excel divides by C2 first and then multiplies by 52, which gives a huge wrong number.

Mistakes to check before you share the sheet#

A salary sheet is easy to get slightly wrong. Check these:

• Weeks per year. Use 52 only if every week is paid, including holidays and vacation. Unpaid weeks lower the total.
• Gross, not net. The formula gives pay before tax and deductions.
• Rates stored as text. A rate typed as $25/hr is text, and the formula shows #VALUE!. Keep just the number in the cell and format it as currency.
• Hours that vary. For part-time staff with changing hours, use the average weekly hours, or total the real hours from timesheets.
• Missing $ signs. If every row after the first shows 0, the weeks cell isn't locked.

For a full sheet with deductions and pay periods, the guide to building a payroll calculator in Excel takes this further.

Practise it#

This calculation uses two core skills: writing a formula and locking a cell with $. Practise your first formulas in a live spreadsheet, then practise absolute references with the $ sign: you type the formula, fill it down and see instantly if every row is right. Or try the salary exercise on this page.