How to Do Payroll in Excel: 7 Simple Steps (Plus Step by Step Video and Free Template)

To add a column, choose the column letter (e.g., G, H, or I) you want your new column to come before. Right-click your mouse and then select “Insert.” Enter a header (in row 3) for your new column (for example, “Local NYC Tax”) and input the rates for applicable employees. Go to all the month tabs (from “January Payroll” to “December Payroll”), including the “Year-to-Date Payroll” tab, and enter a new column between columns N and Q (the tax columns). Label this “Local NYC Tax.”

When adding new columns to the month tabs, you must be consistent. If you add a “Local NYC Tax” column in column O on the “January Payroll” tab, you must add it in the same place on other month tabs. For the “Year-to-Date Payroll” tab, which contains the summary of the 12 months, you also need to add any new columns so that these correctly reflect the details from the monthly payroll tabs.

When adding a column, simply highlight the column you want a new one to be in front of and right-click and select Insert.

To delete columns, highlight the column you want to remove, right-click your mouse, and then select “Delete.” The rules are the same if you delete a column from the “Set Up Employee Data” tab. If you delete a column from one of the payroll tabs, including the “Year-to-Date Payroll” and the “Set Up” tabs, then you must delete it from the others.

For example, if you delete the “Social Security Tax Rate” in column G of the “Set Up Employee Data” tab, you need to remove the “Social Security Tax” column (column N) in all the 13 payroll tabs. If not, you’ll receive error messages—and your formulas won’t work.

This is the “Set Up Employee Data” tab. Don’t change the Social Security and Medicare tax rates in columns G and H, respectively, because they are fixed.

---

Check how the cells with formulas automatically calculate totals in the monthly payroll tabs once you enter data in columns A through I.

---

When adding and deleting columns, consider whether or not you can simply change some of the existing column labels in the payroll Excel template. Aside from cutting down on errors, this will save you time from having to change multiple tabs.

To help, we added two columns (columns N and O) in the “Set Up Employee” tab and labeled them “Other Deduction.” Just double-click on the description and overwrite it with a label that more appropriately reflects your needs.

Note that we created the “Other Deduction” columns to be easily personalized, so you don’t have to change any other tabs to match. There’s only one “Other Deductions” column in the 13 payroll tabs, and the data that reflects here is the sum of columns N and O in the “Set Up Employee Data” tab. If you need to add more columns for deductions in the “Set Up” tab, follow the directions above for adding columns.

You can use the “Other Deduction” columns in the “Set Up” tab to input miscellaneous deductions specific to your company.

---

Although there are two “Other Deduction” columns in the “Set Up“ tab, the monthly payroll tabs sum them in one column.

---

Enter your employees’ full names under column A in the “Set Up Employee” tab. To do payroll in Excel, you need to re-enter their names on the monthly payroll tabs. When you do, ensure that it matches the name you entered in the “Set Up” tab. If there are differences in spelling and formatting, the monthly and annual payroll tabs will not be able to make automatic calculations.

When you have new hires, enter their details in the “Set Up” tab after the existing employees; information will be auto transferred to other tabs when you input their names.

IMPORTANT NOTE: For employees who have resigned or been terminated, simply stop adding payroll data for them in the monthly payroll tabs. Don’t remove them from the “Set Up Employee” tab, or any prior months reflected in their payroll will update to $0, and your year-to-date payroll expense amounts will change. You can remove all inactive employees from the “Set Up” tab when you recreate the payroll Excel template for the next calendar year.

In column B of the “Set Up Employee Data” tab, enter each hourly employee’s straight-time hourly rate (excluding overtime). Leave this cell blank for salaried employees.

In column C of the same tab, input each salaried employees’ annual salary. For example, if your staff earns $50,000 a year, enter “$50,000.”

In column D, labeled “# of Pay Periods in Year,” enter the applicable payroll period based on how often you pay employees.

• If biweekly, input “26” (52 weeks/two weeks per pay period = 26)
• If weekly, input “52” (52 weeks in a year)
• If semimonthly, input “24” (12 months/two pay periods per month = 24)
• If monthly, input “12” (12 months in a year)

Enter each employee’s federal and state income tax rates in columns E and F, respectively. To find the applicable rates, use the W-4 forms collected from employees when they joined your company. The tax rates depend on the number of allowances your employees entered (usually based on the number of dependents) and their filing status—like married, single, or head of household. Generally, the more allowances employees claim, the lower their tax rates.

Columns A through F in the “Set Up Employee Data” tab don’t have any formulas in the cells. Use your payroll records to ensure you enter accurate information.

This is the “Year-to-date Payroll” tab. All data automatically populates within this tab, so you shouldn’t have to change any information in the cells.

Don’t change the Social Security and Medicare tax rates in columns G and H, respectively. These Federal Insurance Contributions Act (FICA) rates are predetermined by the IRS.

• Social Security
• Medicare
• Paid Time Off (PTO) Calculations

For Social Security, the tax rate is 6.2% of the employee’s gross salary. Keep in mind that Social Security maxes out if an employee earns $168,600 (in 2024) in wages before the end of the year. If you have employees earning more than that, you need to stop withholding the 6.2% tax once their YTD earnings exceed that amount.

For example, if your employee meets the $168,600 earnings threshold for Social Security in April, delete all the formulas under “Social Security Tax” (column N) in payroll tabs May through December for the employee. Note that because the Social Security tax rate is 6.2% of wages, the maximum amount any employee will pay annually in 2024 is $10,453.20 ($168,600 maximum x 0.062).

To help check your employees’ total earnings, look at column M (labeled “Year-to-Date Gross Pay”) in the “Year-to-Date Payroll” tab of the Excel payroll template. This column shows the employees’ YTD gross wages. Simply deduct all YTD nontaxable income (column I) from this amount to find the taxable gross income.

For Medicare, the tax rate is 1.45% on an employee’s first $200,000 in wages and an additional 0.9% if the earned wages exceed $200,000 in a calendar year. This increases the total Medicare tax to 2.35% for those individuals. If you have employees who reach this threshold, you need to change the formulas in the “Medicare Tax” column (column O) on all monthly payroll tabs following the month the employee reached $200,000 in gross pay.

To further clarify, look at the screenshot below, specifically the formula field. Delete the (gray) highlighted part of the formula and replace it with “2.35%.” Doing so will overwrite the link to the employee’s Medicare tax rate (which is 1.45%) on the “Set Up Employee Data” tab.

If your employee reaches the $200,000 wage threshold for Medicare, check and revise the formula in the “Medicare Tax” column on applicable monthly payroll tabs.

Note that the threshold for Social Security has increased for the past eight years, while the Medicare tax rate has been the same for the past six years. To ensure that you remain compliant, check for any tax rate changes at the beginning of each year and update the payroll Excel template accordingly.

If you want to know more about these two payroll taxes, read our guides to Social Security taxes and Medicare taxes.

Relying on your employees to keep track of their PTO is not a good strategy. To help you monitor this, we included a PTO calculation tool that’s optional to use.

If you decide to use the PTO calculation tool, check the last three columns in the “Set Up” tab that pertain to PTO. In column P (“Enter Annual PTO Hours”), input the total PTO hours employees are entitled to in a year.

You don’t need to fill column Q (“Auto Calculation-PTO Hours Taken”) since the template will automatically pull the total PTO hours each employee has used from the “Year-to-Date Payroll” tab. The same goes for column R (“Auto Calc-PTO Hours Remaining”) because it contains a formula that deducts the actual PTO taken from the employee’s annual entitlement.

Note that you have to separately track the actual PTO hours that each employee has taken for the applicable payroll period of every pay run. Input the data in column F (“PTO Hours Taken”) of the monthly payroll tabs and the “Year-to-Date Payroll” tab will sum up all PTO hours per employee.

The template contains several columns for the different types of benefits you may offer employees, such as health, dental, vision, and 401(k) plans. For each benefit applicable to your business, enter the employees’ premium per pay period in columns I through L of the “Set Up Employee Data” tab.

You can also use columns M, N, and O for garnishments and other employee deductions. After entering the benefits and deduction details on the “Set Up” tab, the template will automatically populate the appropriate fields when you input the employees’ names in column B of the monthly payroll tabs.

There are columns for you to withhold premium deductions for common insurance benefits.

Relying on your employees to keep track of their PTO is not a good strategy. To help you monitor this, we included a PTO calculation tool that’s optional to use.

If you decide to use the PTO calculation tool, check the last three columns in the “Set Up” tab that pertain to PTO. In column P (“Enter Annual PTO Hours”), input the total PTO hours employees are entitled to in a year.

You don’t need to fill column Q (“Auto Calculation-PTO Hours Taken”) since the template will automatically pull the total PTO hours each employee has used from the “Year-to-Date Payroll” tab. The same goes for column R (“Auto Calc-PTO Hours Remaining”) because it contains a formula that deducts the actual PTO taken from the employee’s annual entitlement.

Note that you have to separately track the actual PTO hours that each employee has taken for the applicable payroll period of every pay run. Input the data in column F (“PTO Hours Taken”) of the monthly payroll tabs and the “Year-to-Date Payroll” tab will sum up all PTO hours per employee.

There’s a section for PTO tracking. It will update as you enter paycheck details each period.

For accurate employee paychecks, enter the correct number of straight-time hours in column E and overtime hours in column G of the monthly payroll tabs. For straight-time hours, this is usually 40 hours or fewer—unless your business is located in California. For overtime, the template calculates pay at a rate of 1.5 times your regular pay rate using the number of hours you specify. However, this can change depending on the labor laws of the state where your business operates.

When doing payroll in Excel, we encourage you to review your state laws on overtime rates. Federal law generally requires you to pay employees overtime for any hours worked over 40 in a consecutive seven-day period. However, California considers any hours worked over eight in a day to be overtime—regardless of whether you work over 40 hours in a workweek.

The straight-time and overtime hourly rates listed in columns C (“Straight-Time Hourly Rate”) and D (“Overtime Hourly Rate”) of the monthly payroll tabs are automated using a formula. There’s also a note above both hourly rate columns that states not to change the contents of the cells because they will update automatically. So, whenever you enter your employees’ names under column B of the monthly payroll tabs, the straight-time hourly rates automatically carry over from the “Set Up Employee Data” tab.

The overtime rate in column D is based on the straight-time hourly rate you enter in column C with a formula that simply multiplies it by 1.5 (time and a half).

For example, if you need to pay double time for an employee who worked 16 hours in a day, you can overwrite the existing calculation with this formula. Note that the formula is broken into two parts: four hours to be paid at a rate of time and a half while the other four hours are considered double-time:

If you decide to pay an employee bonus, you must withhold payroll taxes similar to the way you do with regular wages. In this payroll Excel template, just enter the bonus amount in column H, and it will be included in the taxable gross income.

For reimbursements of expenses that employees incurred during business travel, note that these transactions are generally not taxable. This is because you are simply reimbursing the money employees used to pay for business expenses; therefore, the employee didn’t actually receive any new income.

In this case, you don’t need to withhold payroll taxes. Instead, enter the amount you need to reimburse to the employee in column I of the applicable monthly payroll tab. We have formulas in place that will disregard this amount when calculating taxes to withhold and pay.

Bonuses and other taxable income should be entered in column H.

Salaries in column J of the monthly payroll tabs are automatically pulled from the “Set Up Employee Data” tab. Once the template has the employee’s annual salary and your total number of pay periods in the year, the column will automatically reflect the employee’s salary for the period. In calculating the per-period salary amount, note that the cells in column J have formulas that simply divide the annual salary by the number of annual pay periods you entered on the “Set Up Employee Data” tab.

Similar to column J, the “Straight-Time Pay” column (K) of the monthly payroll tabs also contains automated formulas. It has two parts: the number of straight-time hours worked multiplied by the straight-time hourly rate and the number of PTO hours taken multiplied by the straight-time hourly rate. This helps ensure that you’re paying employees all of the money they’re due, especially PTO hours.

Overtime pay is simple in that it’s calculated using the number of overtime hours you entered in column G and the hourly overtime rate in column D of the monthly payroll tabs. If you have overtime hours listed in column G, there should be a corresponding dollar amount in column L.

Gross pay (column M) is the total income an employee is due before deductions and taxes. In this payroll Excel template, it’s calculated by adding columns J through L, plus columns H and I, of the monthly payroll tabs. These columns contain the employees’ per period salary, straight-time pay, overtime pay, bonuses and commissions, nontaxable income, and reimbursements.

FICA taxes are labeled as “Social Security Tax” (column N) and “Medicare Tax” (column O) of the monthly payroll tabs. These columns are linked to the “Set Up Employee Data” tab, automatically pulling the applicable tax rates from there. The cells then multiply the rates by the employee’s gross pay minus any nontaxable income (such as reimbursements).

As an employer, you are responsible for withholding federal income taxes from your employees’ paychecks. You might also have to withhold additional amounts—depending on the state. In the monthly payroll tabs, columns P and Q (labeled “Federal Income Tax” and “State Income Tax,” respectively) calculate taxes based on the tax rate information you provided in the “Set Up” tab.

Benefits are reflected in column R on each monthly payroll tab, which sums all premiums showing in columns I through L on the “Set Up Employee Data” tab. The amounts should be fixed from one pay period to the next, but you can update the amounts in the “Set Up” tab if they ever change. If you do happen to change the benefit deductions for a particular employee, you need to overwrite the benefit deduction formulas for the prior months first and then overwrite it with the actual dollar amounts.

Other deductions should be handled the same way as benefits. Note that the “Other Deductions” column (column S) in the monthly payroll tabs consists of garnishments and other deductions detailed in the “Set Up” tab. On the other hand, the “Total Deductions” (column (column T) is the sum of all taxes withheld, premiums collected for benefits, and other deductions.

The “Net Pay” column (column U) contains the amount you need to pay employees through check, direct deposit, or pay card. This amount is calculated automatically by subtracting the employee’s total deductions (column T) from their gross pay (column M).

Cells with “-” in them will continue to reflect that until sufficient employee and/or paycheck details are entered for it to complete the calculation.

---

Column U shows the net pay amounts of employees.

---

Setting up a payroll format in Excel beats having to do payroll manually using a calculator to compute the applicable salaries, taxes, and deductions. You can take advantage of Excel formulas and commands to automate some of the computations (such as Social Security and Medicare taxes) and customize how you want the data to be accessed or appear in Excel tabs or spreadsheets.

It can be a good option for some employers with a small workforce (up to 10 employees). However, while Excel provides ease in managing employee and payroll-related data, we only recommend it for very small businesses that have simple payroll and tax calculation needs.