Doing payroll in Excel is best for businesses that need to pay 10 or fewer employees and operate in states without complex labor and tax laws. To save time, use our payroll Excel template with pre-filled information like tax rates and overtime formulas. Automation helps streamline payroll, whether you use a template or payroll provider.
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. Start a 1-month free trial today.
How Doing Payroll in Excel Works
Doing payroll in Excel efficiently requires a standardized payroll Excel template you can use from month to month. There should be tabs for each month linked with formulas that calculate and sum employees’ taxes, deductions, and pay. You should also have a setup tab so your payroll calculations can pull standard information like pay rate and benefits enrolled; keeping this updated so you can link to it each month helps automation and prevents double-work.
We’ve created an Excel template for you, and it has 15 tabs, one for each month, employee data setup, employer tax information, and year-end payroll information. First, you’ll enter the information that doesn’t vary often, such as employee names, pay rates, tax rates, and deductions. Then you’ll enter hours worked, and watch your payroll information automatically populate.
Watch how to do payroll using our Excel payroll template
Follow these steps when using our free Excel payroll template to do your business’ payroll:
1. Review Payroll Excel Template & Edit For Your Business
2. Set Up Payroll Information for Each Employee
3. Set Up Employer Payroll Tax Information
4. Enter Hours Worked
5. Review Automatic Payroll Calculations
6. Pay Your Employees
7. Review Year-to-Date Payroll Information
1. Review Payroll Excel Template & Edit For Your Business
Figuring out how to do payroll using an Excel template can take some time on the front end. First, take a look at the template and evaluate your business needs: How many employees do you have? What benefit 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 columns to meet the needs of your small business. For example, if you live in a place that charges local income tax like New York City you will need to reflect that in the template. To do this, go to the “Set Up Employee Data” tab first. This is where you should always start when making changes because all of the remaining payroll tabs pull their source data from here.
This is the “Set Up Employee Data” tab. Don’t change the Social Security or Medicare tax rates in columns G and H within the Set Up tab, because they are fixed rates.
To add a column, select it by clicking the column letter (e.g., G, H, I) you want your new column to be in front of. Then right-click and select “Insert.” Enter a header for your new column, for example “Local NYC Tax,” and enter the rates for applicable employees. Go to all 13 payroll tabs (“January Payroll” through “December Payroll” and “Year-to-Date Payroll”) and enter a new column somewhere between columns L and O (the tax columns); label it “Local NYC Tax.”
This is how the “Set Up Employee Data” sheet looks when you add a column. Simply highlight the column you want to add the new column beside and left-click.
When adding new columns to the payroll tabs, you must be consistent. If you add a “Local NYC Tax” column in column M on the “January Payroll” tab, you must also add it to the other tabs, “February Payroll,” “March Payroll,” and so forth. The “Year-to-Date Payroll” tab on the bottom far right is the sum of the 12 months; you also need to add any new columns to it, so that it reflects the individual payroll tabs.
See how the cells with formulas automatically calculate totals once you enter data in columns A through I
To delete columns, select the column—similar to how we instructed you to do when adding a column—and right-click. Select “Delete.” The rules are the same if you’re deleting a column from the “Set Up Employee Data” tab. If you delete a column from one of the 13 payroll tabs or the Set Up tab, 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 will need to delete the “Social Security Tax Rate” column N in the 13 payroll tabs (“January Payroll” through “December Payroll” and “Year-to-Date Payroll”). If not, you’ll receive error messages and your formulas won’t work.
Change Column Names
Before adding and deleting columns, consider whether or not you can just change the column labels to personalize the payroll Excel template for your business. This will save you time from having to change multiple tabs and can cut down on errors.
Notice the “Other Deduction” columns on the setup sheet. Feel free to use them for miscellaneous deductions that are specific to your company
To help, we added two columns (N and O) in the “Set Up Employee Data” tab and labeled them “Other Deductions.” Just double-click on the description and overwrite it with a label that more appropriately reflects your needs.
Although there are two “Other Deduction” columns in the setup tab, the monthly payroll tabs sum them in one column
Because we created “Other Deductions” to be easily personalized, you won’t have to change any other tabs to match. There’s only one “Other Deductions” column in the other 13 tabs, and they sum the columns N and O in the “Set Up Employee Data” tab. If you need to add more columns for deductions in the “Set Up Employee Data” tab, follow the directions above for adding or deleting columns.
2. Set Up Payroll Information for Each Employee
Once you’re satisfied with the template layout, you can begin setting up your employee data. Enter names, hourly pay rates or annual salary, and number of pay periods in the year to the “Set Up Employee Data” tab.
Also, enter all of your federal and state tax rates, except Social Security and Medicare (columns G and H, respectively). Social Security and Medicare (FICA tax) rates are fixed until employees reach over $132,900 a year. Finally, add benefits and deductions.
Enter employee information on each row to set them up within the payroll template
Employee Name
Enter your employees’ full names under column A in the “Set Up Employee Data” tab. You will re-enter their names on the monthly payroll tabs as you prepare to pay them each pay period. When you do, be sure it matches the name you entered on the Set Up tab. If there are any differences, including spelling or formatting, the monthly and annual payroll tabs will not be able to make its automatic calculations.
Also note that if you remove employees from the spreadsheet after they are terminated, your payroll data will be inaccurate. This is because all 13 payroll tabs are linked to the “Set Up Employee Data” tab. So, if you delete employees from the Set Up tab midyear, any prior months reflected in their payroll data will update to $0 and your year-to-date payroll expense amounts will change.
If employees are terminated, simply stop adding payroll data for them in the monthly payroll tabs. When you recreate the payroll Excel template for the next year, you can remove all inactive employees from the “Set Up Employee Data” tab. If you need to add an employee, enter after existing employees and their payroll information will transfer to the other payroll tabs automatically when you enter their names in column B of the monthly payroll tabs.
As noted above, the “Year-to-Date Payroll” tab will automatically pull data without you having to enter anything. Your employee names will pull directly from the “Set Up Employee Data” tab, and all corresponding amounts such as pay and deductions, will populate from the monthly payroll tabs.
Straight-Time Hourly Rate
Use column B to enter the straight-time hourly rate for each hourly employee; exclude overtime. Leave this cell blank for salaried employees.
Columns A through F in the “Set Up Employee Data” sheet don’t have any formulas within the cells. Use your payroll records to ensure you enter accurate information.
Annual Salary
Enter each salaried employee’s annual salary in column C. For example, if your employee earns $50,000 a year, enter “$50,000.”
Number (#) of Pay Periods in Year
If you pay biweekly, enter 26 (52 weeks/two weeks per pay period = 26) in column D. Alternatively, if you pay monthly, enter 12 (12 months in a year). If you pay weekly, enter 52 (52 weeks in a year); for semi-monthly payments, enter 24 (52 months/two pay periods per month = 24).
Federal & State Income Tax Rates
You will need to use the W-4 forms you collected when your employees were hired to find their applicable federal and state tax rates. The tax rates depend on the number of allowances your employees entered (usually based on number of dependents) and their filing status—like head of household, married, or single. Generally, the more allowances employees claim, the lower their tax rates.
Social Security & Medicare Tax Rates
Don’t make any changes to the Social Security and Medicare tax rates in columns G and H. These FICA rates are pre-determined by the IRS. You do need to keep in mind though that Social Security maxes out if an employee earns $132,900 in wages before the end of the year. If you do have any employees earning more than that, you will need to stop withholding the 6.2% tax once their year-to-date earnings exceed that amount.
Check column M, Year-to-Date Gross Pay, of the “Year-End Payroll” tab to verify year-to-date gross wages. Deduct all year-to-date nontaxable income (column I) from this amount to find taxable gross income.
This is the “Year-to-date Payroll” sheet. All data automatically populates within this tab, so you shouldn’t have to change any information within the cells.
For example, if you have an employee who meets the $132,900 earnings threshold for Social Security in April, delete all of the formulas in column M (“Social Security Tax Rate”) for that particular employee in payroll tabs May through December. Note that because the Social Security tax rate is 6.2% of wages, the maximum amount any employee will pay is $8,239.80 in a year ($132,900 maximum x 6.2% = $8,329.80).
The Medicare tax rate is 1.45% on an employee’s first $200,000 in wages and 2.35% on any wages earned over $200,000. If you have any employees who reach this threshold during the year, you will need to change the formulas in the “Medicare Tax Rate” column (column N) on all monthly payroll tabs following the month the employee reached $200,000 in gross pay.
Look at the screenshot below and overwrite the highlighted part of the formula with the 2.35% tax rate. The information you are overwriting is a link to the employee’s Medicare tax rate (1.45%) on the “Set Up Employee Data” tab.
Take a look at the formula in the “Medicare Tax” cell. If your employee earns over the $200,000 threshold, you’ll need to withhold an additional 0.9% (2.35%= 1.45%+.9%) on the excess earnings.
The threshold for Social Security has increased for the past eight years, so you should check for any changes at the beginning of each year and update the template. The Medicare tax rate has been the same for the past six years, but it’s still a good idea to verify it each year.
Benefits & Other Deductions
We included numerous benefits you may offer employees including health, dental, and vision insurance as well as 401(k) benefits. You will need to enter the employees’ premium per pay period in columns I through L of the “Set Up Employee Data” tab for each that is applicable to your business.
There are columns for you to withhold premium deductions for common insurance benefits
Use columns M, N, and O for garnishments and other employee deductions. Once you enter them on this tab, they will automatically populate when you enter employee names in column B on the monthly payroll tabs.
Calculate Paid Time Off (PTO)
The PTO calculation tool is optional but will be useful if you offer any holiday, vacation, or sick pay. Just remember that relying on your employees to keep track of their PTO is not a good strategy—nor is assuming you will remember.
If you opt to use the PTO calculation tool in the template, review the last three columns in the setup tab that pertain to PTO. Enter the total annual PTO hours your employee is entitled to in column P (“Enter Annual PTO Hours”). Then, column Q (“Auto Calculation-PTO Hours Taken”) will automatically pull the total PTO hours each employee has used from the Year-to-Date tab.
Remember, the “Year-to-Date Payroll” tab sums all PTO hours you enter in column F (“PTO Hours Taken”) of the monthly payroll tabs. Column R (“Auto Calc-PTO Hours Remaining”) will keep you updated on how much PTO is remaining by deducting total year-to-date PTO taken from total annual PTO entitlement. Watch this video on calculating PTO Hours using Excel to help.
There’s a section for paid time off tracking. It will update as you enter paycheck details each period.
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. 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 sum the amount due for each employee and the company.
Use the “Employer Payroll Taxes” sheet to help keep track of the taxes your business owes
You will also likely be responsible for workers’ compensation insurance payments but rates and amounts can vary depending on the work your employees do, claim history, and other factors. However, you should receive a monthly premium amount due from the insurance fund that creates your policy, which should make it easy for you to track. Simply enter monthly premium amounts in column G, “Enter Workers’ Comp Payable.”
4. Enter Hours Worked
Now that when you have all of your employee and employer data entered, you’re ready to run your first set of payroll calculations. Go to the appropriate monthly payroll tab—“January Payroll” if it’s January—and enter the pay date in column A. Then, you’ll enter your employees’ names and, finally, hours worked.
This is an example of a monthly payroll sheet. Each monthly spreadsheet should be formatted the same
Enter the full name of each employee you are paying for the period in column B. The name must reflect the same as you entered it in column A of the “Set Up Employee Data” tab; if it doesn’t match, 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.
Hours Worked & Other Income
The only other information you need to enter is straight-time hours worked, which is usually 40 or below (unless you’re in California), PTO hours used, overtime hours worked, bonuses, and nontaxable income.
Straight-Time vs Overtime Hours
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 40 hours or less in a workweek.
You must enter the correct number of straight-time hours in column E and overtime hours in column G so your employees’ paychecks are accurate. As we’ll discuss later, the template calculates overtime pay at a rate of 1.5 times your regular pay rate using the number of hours you specify.
Straight-Time vs Overtime Hourly Rate
The straight-time and overtime hourly rates listed in columns C and D of the monthly payroll tabs are automated using a formula. When you enter your employees’ names under column B, the straight-time hourly rates automatically carry over from the “Set Up Employee Data” tab. The overtime hourly rates in column D are calculated at 1.5 times the straight-time hourly rate shown in column C.
There’s a note above both hourly rate columns that states not to change the contents of the cells because they will update automatically. However, if your business operates in California, you may need to make adjustments because employees who work over 12 hours in one workday are entitled to double-time pay. In this case, you may need to change the formula below to reflect what you actually owe.
The overtime rate in column D is based on the straight-time hourly rate you enter in column C; the formula simply multiplies it by 1.5 (time and a half)
If you’re ever required to pay double time, overwrite the formula in column D, “Overtime Hourly Rate.” For example, if an employee works 16 hours in one day, you would overwrite the existing calculation with this formula:
= (4*(C4*1.5))+(4*(C4*2))
You’ll need to break the formula into two parts: four hours to be paid at a rate of time and a half and the other four hours that should count as double time. Remember, in California, an employee working a 16-hour day must be paid overtime for eight of those hours.
Any time less than 12 hours (12 – 8 = 4) can be paid at a rate of 1.5 times the regular pay rate, and any hours over 12 (16 – 12 = 4) must be paid at twice the straight-time rate.
Taxable & Nontaxable Income
If you decide to pay out an employee bonus, you must withhold payroll taxes similar to the way you do with regular wages. In this payroll template, just enter the amount of the bonus in column H, and it will be included in taxable gross income.
Bonuses and other taxable income should be entered in column H
If you happen to reimburse an employee for expenses incurred during travel for your business, that is generally not taxable. The employee paid for it, and you are just reimbursing the money; 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 reimbursed the employee in the respective row in column I of the template’s payroll tabs. We have formulas in place that will disregard this amount when calculating taxes to withhold and pay.
If learning how to do payroll in Excel seems too complex to you, 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. Also, the accuracy of your payroll tax calculations depend on how accurate your employee payroll information is, meaning you can subject yourself to penalties if you’re not careful.
Salary per Period
Salaries in column J 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, column J on the payroll tabs will automatically show the employee’s salary for the period. The cells in that column simply divide annual salary by the number of annual pay periods you entered on the “Set Up Employee Data” tab to reach the per period salary amount.
The cells with N/A in them will continue to reflect that until sufficient employee and/or paycheck details are entered for it to complete the calculation
Straight-Time Pay
“Straight-Time Pay” in column K is made up of two parts: (1) the number of straight-time hours worked multiplied by the straight-time hourly rate and (2) the number of PTO hours taken multiplied by the straight-time hourly rate. In short, you want to be sure you’re paying employees all of the money they’re due, especially PTO time.
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. If you have overtime hours listed, there should be a corresponding dollar amount in column L.
Gross Pay
Gross pay is the total income an employee is due before deductions and taxes. In this template, it’s calculated by adding columns J through L, which includes per period salary, straight-time pay, and overtime pay. “Bonus/Commissions/Other Taxable Income” (column H) and “Nontaxable Income, i.e., Reimbursements” (column I) are also included.
FICA Taxes
FICA taxes are labeled as “Social Security Tax Rate” (column N) and “Medicare Tax Rate” (column O). These cells are linked to the “Set Up Employee Data” tab and automatically pull the tax rates from there. The cells multiply the rates by the employee’s gross pay minus any nontaxable income, such as reimbursements.
Income Tax Withholdings
You’re responsible for withholding federal income taxes from your employees’ paychecks and—depending on the state—you might have to withhold additional amounts. Columns P and Q (federal and state income tax, respectively) calculate taxes based on the tax rate information you provided in the Set Up tab. Generally, employers retrieve the information that’s required to figure out the tax rates from Form W-4 provided at the time of hire.
Benefits & Other Deductions
Benefits are reflected in column R on each of the monthly payroll tabs, 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 will need to overwrite the benefit deduction formulas for the prior months first and overwrite with actual dollar amounts.
For example, if you need to change an employee’s health insurance deduction from $100 to $200 starting in the month of 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 update the “Set Up Employee Data” tab.
Other deductions should be handled the same way as benefits. The “Other Deductions” column (column S) in the payroll tabs consists of garnishments and other deductions detailed in the Set Up tab.
Total deductions is the sum of all taxes withheld, premiums collected for benefits, and other deductions.
Net Pay
Net pay is the amount you will pay out to your employees through check, direct deposit, or pay card. This amount also calculates automatically by subtracting the employee’s total deductions (column T) from their gross income (column M). Watch this video on calculating employee pay to help you get started.
Column U shows the net pay amounts you’ll need to pay your employees
6. Pay Your Employees
After you review your payroll tax, deduction, and wage calculations for the pay period, you’re ready to pay your employees. To do this, you can choose to print checks online for free, deposit money to employee pay cards, or work with your bank to process direct deposits.
7. Review Year-to-Date Payroll Information
In the payroll template we provided, you can view all of your employee payroll details for the year in the “Year-to-Date Payroll” tab, which is the last tab in the template. It’s linked to all of 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 year-to-date information you need to fill them in. Check out some of our other free Excel payroll templates to get started.
How to Do Payroll in Excel Frequently Asked Questions (FAQs)
What Excel tools or formulas can I use to evaluate payroll expenses in my Excel template?
You can use a pivot table to analyze your data if it is organized in a table. This can help you see which employees are paid the most, which payroll taxes cost the most money, and who costs the most in overtime work. You can also use the Excel filter function to hide some of the data in a table, so you can focus on specific data you want to analyze.
What Excel formulas can I use to calculate payroll taxes?
The Excel “Sum” function can be used to calculate your total payroll taxes if you have them entered in a spreadsheet. You can also create your own formula that multiplies two cells to calculate a certain tax like Social Security; for example, if the payroll tax rate is in cell C7 and gross wages are in cell E5, use this formula “ =C7*E5.”
How do I create my own Excel payroll template?
To create your own payroll Excel template, you will need to decide how much you would like to automate calculations. If you only have a couple of employees, you can create a simple template with one spreadsheet to use as a payroll calculator—just entering payroll data like hours worked and pay rates to calculate totals you can use to make payments (40 hours X $8/hour = $320 gross pay). Of course, you’ll need to set up columns for taxes so you know how much to withhold (subtract from gross pay).
You can also set up an excel template for the year, similar to our free downloadable payroll Excel template, to save you time from having to re-enter employee data each month. You will need to create multiple tabs and use formulas to link some spreadsheets together. Be sure to double-check that it works properly before implementing it as part of your payroll process.
Where can I find other Excel payroll templates?
Microsoft has some downloadable payroll templates such as a payroll calculator you can use to calculate employee deductions, payroll, and print pay stubs for each employee. Other downloadable Excel payroll templates include weekly and biweekly time sheets, pay registers, and employee attendance trackers.
Bottom Line
If you have 50 or less 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. You’ll be able to pay employees, keep track of the payroll taxes you owe, and maintain payroll records electronically.
If you want a payroll system you can easily personalize to your business, consider Gusto. After the initial setup, you can set payroll to run on autopilot. Gusto will pay your employees and your taxes electronically starting at $45 a month. Sign up for a free trial, and see if it’s right for you.
DSV Group Elevates
If you just want to pay your employees via 1099, would I still need to use this payroll spreadsheet for any reason?
Amanda Norman
Hi DSV Group,
Thanks for visiting the site! The spreadsheet template is geared more towards companies with W2 employees. I suggest checking out our article, Best Payroll Software For Paying Contractors for some great recommendations of payroll software that not only pays contractors but handles tax reporting too.
Best wishes,
Mandy, Moderator
Rob Hall
Hello and Thank you for putting together this free excel spreadsheet to do payroll. We are a small trucking company and have two partners in the business. We are looking at hiring a driver, so there will be 3 employees. No one makes an hourly wage. Everyone works for a Percentage of the weekly loads. How do I put this in the spreadsheet?
Amanda Norman
Hi Rob,
That’s a good question. If your employees are independent contractors, this spreadsheet may not be the best fit. If that’s the case, I suggest checking out Gusto as an option for calculating your payroll. However, if you want add or edit columns, the video included in the article shows you how to do that. Also, if you are using a commission tracking application or calculating it manually, then you can enter that amount into the “Other” column in the same section where you would enter hours worked. That will still total a gross pay and take any deductions that have been set up in the sheet.
Hope this helps. Thanks for visiting the site!
Best wishes,
Mandy, Moderator
Charlette Beasley
Hi Jill,
Thanks for bringing the NA error to our attention. The spreadsheet has been updated, so let us know if you experience any more issues.
Charlette
Jill Keller
Hello, Thank you for providing this informative payroll spreadsheet and some instructions.
I could not get the Employer Payroll Tax part to work. When would the information transfer from the monthly payroll to that spreadsheet? Is there a send button or a finish payroll button I’m not seeing? Do I transfer that information myself?
Thank you,
Jill
Amanda Norman
Hi Jill,
Thank you for your question. The data for the Employer Payroll Taxes sheet is pulled from the sum of the Gross Pay and SS Tax columns in each month’s payroll sheet. If that value isn’t there, that’s why it isn’t showing up in your Employer Payroll Taxes sheet. Does your sheet show #N/A as the total for those columns?
I’ve reached out to someone to double check the spreadsheet.
Mandy, Moderator
Jill Keller
Hi Mandy,
Yes, that is the code, #N/A. The Gross Pay field is filled in, and the corresponding columns have been automatically calulated for the taxes, but it’s not automatically filling in on the Employer Tax form. Thank you for responding and looking into this, it will be a valuable tool for me to use!
Jill
Charlette Beasley
Hi Jill,
Thanks for bringing the NA error to our attention. The spreadsheet has been updated, so let us know if you experience any more issues.
Charlette
M J
I just downloaded the template November 2020 (thank you!) and have the same problem of values not carrying into Employer Payroll Taxes spreadsheet. Under the monthly payroll spreadsheet, the Enter Hours Worked provides totals at the bottom of the spreadsheet, however, the section with automatic calculations (straight pay, gross pay, ss tax, etc.) have #NA as the total. Thank you.
Amanda Norman
Hi MJ,
Thanks for letting us know, and sorry for the inconvenience. We’ll get it fixed.
Best wishes,
Mandy, Moderator