With attention to detail and an accurate and reliable template, processing payroll in Excel can be great for small businesses just starting out. Learn how with our guide and free template.
How to Do Payroll in Excel in 7 Steps + Free Template
Charlette has over 10 years of experience in accounting and finance and 2 years of partnering with HR leaders on freelance projects. She uses this extensive experience to answer your questions about payroll.
Heather is a staff writer and payroll specialist with several years of experience working directly with small business owners. Her expertise allows her to deliver the best answers to your questions about payroll.
This article is part of a larger series on How to Do Payroll.
Doing payroll in Excel is best for businesses needing to pay 10 or fewer employees and operating in states without complex labor and tax laws. Since it is a completely manual approach to payroll, it does take time compared to other methods but can definitely be cost-effective for growing businesses. Using a template can help with time and efficiency because it contains prefilled information like tax rates and overtime formulas. After inputting your employee information, you just need to enter the actual hours worked, and then the template will automatically populate the applicable payroll data.
Thank you for downloading!
Quick Tip:
If payroll is still taking too much time or if you’ve outgrown a spreadsheet, consider using a small business payroll software like Gusto.
It automatically files new hire paperwork, calculates and files payroll taxes, generates year-end W-2s and 1099s, pays via direct deposit, and even helps employees choose and manage their benefits, all from one dashboard.
If payroll is still taking too much time or you’ve outgrown a spreadsheet, consider using a small business payroll software like Gusto. It automatically files new hire paperwork, calculates and files payroll taxes, generates year-end W-2s and 1099s, and pays via direct deposit. It even helps employees choose and manage their benefits. Sign up for a 30-day free trial today.
Follow the steps below to run payroll using our free Excel payroll template. If you want to see the process in action, you can also watch our video tutorial on how to do payroll in Excel.
1. Review Payroll Excel Template & Edit For Your Business
Figuring out how to do payroll using an Excel template can take some time. First, you need to take a look at the template and evaluate your business needs. A standardized template should have tabs for each month, with links to formulas that calculate employee taxes, deductions, and pay. You also need a “Set Up” tab from which your payroll calculations can pull standard information such as pay rate and benefits enrolled.
Keep the “Set Up” tab up-to-date so you can easily link to it each month you run payroll. This helps with automation and prevents double work.
Here are some questions that can help you identify the information needed to finalize the template.
- How many employees do you have?
- What benefits payments and deductions do you need to withhold from their paychecks?
- Is there anything missing from the template that you need to add?
Add or Delete Columns
You may need to add or delete columns to meet the needs of your small business. For example, if your business is located in a place that charges local income tax (like New York City), you need to reflect that in the payroll Excel template. You can do this by adding the required information in the “Set Up Employee Data” tab. This is where you should always start when making changes because all of the remaining payroll tabs pull their source data from here.
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 it correctly reflects details from the monthly payroll tabs.
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.
When adding a column, simply highlight the column you want a new one to be in front of and right-click and select Insert.
Check how the cells with formulas automatically calculate totals in the monthly payroll tabs once you enter data in columns A through I.
Change Column Names
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.
2. Set Up Payroll Information for Each Employee
Once you’re satisfied with the template layout, you can begin setting up your employee data. To complete the “Set Up” tab, you need the following information.
Employee Name
Enter your employees’ full names under column A in the “Set Up Employee” tab. Note that 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.
**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.
Straight-time Hourly Rate
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.
Annual Salary
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.”
Number of Pay Periods in a Year
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 period per month = 24)
- If monthly, input “12” (12 months in a year)
Federal & State Income Tax Rates
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.
Social Security & Medicare Tax Rates
Benefits & Other Deductions
The template contains several columns for 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.
Paid Time Off (PTO) Calculations
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 are columns for you to withhold premium deductions for common insurance benefits.
There’s a section for PTO tracking. It will update as you enter paycheck details each period.
Need assistance on how to use the PTO calculation tool? This video tutorial on calculating PTO hours using Excel can help.
3. Set Up Employer Payroll Tax Information
In addition to tracking withholdings from employee paychecks, you’re responsible for paying your share of payroll taxes. The “Employer Payroll Taxes” tab has cells you can use to enter your federal and state unemployment tax rates. Note that the 6% federal unemployment tax is only charged on the first $7,000 each employee earns (most state unemployment taxes follow the same logic). Be sure to review the information in the payroll tabs each month to help you calculate the tax amount due for each employee and the company.
You will also likely be responsible for workers’ compensation insurance payments. The rates and amounts can vary, depending on the employees’ work, your claim history, and other factors. However, you should receive a monthly premium amount due from the insurance fund that creates your policy. Simply enter the monthly premium amounts in column G (“Enter Workers’ Comp Payable”).
Use the “Employer Payroll Taxes” tab to help keep track of the taxes your business owes.
Download our Free Excel Payroll Template
4. Enter Hours Worked & Other Income Details
After entering all of your employee and employer data into the payroll Excel template, you’re ready to calculate employee payments. Go to the appropriate monthly payroll tab—so if it’s January, go to the “January Payroll” tab. Enter the pay date in column A and the full name of each employee you are paying for the period in column B. Then, input the actual regular hours worked, overtime hours, PTO hours taken, and other income details in the applicable columns.
Remember, the name must be the same as you entered in column A of the “Set Up Employee Data” tab. If it doesn’t match, the pay rates, tax rates, and deductions will not populate in the monthly payroll tabs. If you’re concerned you’re not entering them correctly, copy and paste the names in.
Straight-time & Overtime Hours
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.
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.
For state-specific guidelines most relevant to your business, check out our state payroll guides:
Straight-time vs Overtime Hourly Rate
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.
Meanwhile, the overtime hourly rates in column D are calculated at 1.5 times the straight-time hourly rates in column C.
If your business operates in California, you may need to make adjustments because of its rule that employees who work over 12 hours in one workday are entitled to double-time pay. So, any time worked between eight and 12 hours must be paid at a rate of 1.5 times and any hours over 12 must be paid at twice the straight-time rate. In this case, you have to change the formula in column D to reflect what you actually owe.
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:
= (4*(C4*1.5))+(4*(C4*2))
Taxable & Nontaxable Income
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.
The monthly payroll tabs have specific columns to enter your employees’ names and actual hours worked.
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).
Bonuses and other taxable income should be entered in column H.
If learning how to do payroll in Excel seems too complex, try Gusto. You can pay your employees automatically and approve their hours worked online without having to update spreadsheets.
5. Review Automatic Payroll Calculations
After completing your initial setup and entering employee work hours, all payroll calculations should be visible. It’s a good idea to review them before paying employees so you don’t have to void and reissue payments later. The accuracy of your payroll tax calculations also depends on how accurate your employee payroll information is. If you’re not careful, you can subject yourself to penalties. Here are the items you should review, including how the payroll Excel template computes each.
Salary Per Period
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.
Straight-time Pay
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
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
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
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).
Income Tax Withholdings
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 & Other Deductions
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.
For example, if you need to change an employee’s health insurance deduction from $100 to $200 starting in June, go to each prior month’s payroll tab (January through May) and delete the formula in the health insurance column for that particular worker. Overwrite with $100 and then go to the “Set Up” tab to update the employee’s health insurance deduction to $200.
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.
Net Pay
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.
Want to know more about how this payroll Excel template computes salaries and deductions? Watch this video tutorial we’ve put together on calculating employee pay.
6. Pay Your Employees
After you have reviewed your payroll taxes, deductions, and wage calculations for the pay period, you’re ready to pay your employees. To do this, you can choose to either print checks online, deposit money to employee pay cards, or work with your bank to process direct deposits.
7. Review Year-to-Date Payroll Information
The last tab, titled “Year-to-Date Payroll,” in our payroll Excel template lists all your employee payroll details for the year. It’s linked to the monthly payroll tabs, so all employee payroll expenses should be reflected. If you decide to provide pay stubs—some states require you to do so—this tab will give you the YTD information you need to fill them in.
To help you organize and track payroll-related data, check out some of our other free payroll templates to get started.
Bottom Line
If you have 10 or fewer employees and live in a state that doesn’t heavily regulate labor and payroll law, learning how to do payroll in Excel could be good for your business. Aside from paying employees, you can keep track of the payroll taxes you owe and maintain payroll records electronically.
However, if you’ve outgrown spreadsheets and want a payroll system you can easily personalize to fit your business needs, consider Gusto. It offers full-service payroll with automated taxes and forms starting at $45, plus you can set it to run payroll on autopilot. Learn more about it in our dedicated Gusto review.
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. Keep in mind that Social Security maxes out if an employee earns $142,800 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 $142,800 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 is $8,853.60 ($142,800 maximum x 6.2%).
To help check your employees’ total earnings, look at column M (labeled “Year-to-Date Gross Pay”) in the “Year-to-Date Payroll” tab. 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.