How to Do Payroll in Excel [+ Free Template]

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 a payroll Excel template with prefilled 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.

Visit Gusto

How Doing Payroll in Excel Works

Running 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 duplicate 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.

• Download the Excel Payroll Template

Here are the steps you need to follow when using our free Excel payroll template to do your business’ payroll:

1. Review Payroll Excel Template & Edit for Your Business

Figuring out how to do payroll using an Excel template instead of payroll software 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. If you live in a locality, like New York City (NYC), that charges local income tax, you will need to reflect it in the template. 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.

To add a column between State Income Tax Rate and Social Security Tax Rate, select Column G and click “Insert.”

To add a column, select it by clicking the column letter (e.g., G, H, I) you want your new column be in front of. Then right-click and select “Insert.” Enter a header for your new column (e.g., “Local NYC Tax”) and enter the rates for applicable employees. Go to all 13 payroll tabs (January through December and Year-to-Date) and enter a new column somewhere between columns L and O (the tax columns); label it “Local NYC Tax.”

Once the new column has been added, add a title or label, like NYC Local Tax Rate.

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 add it on the other tabs, “February Payroll,” “March Payroll,” and so forth. The year-to-date tab on the bottom far right is a sum of the 12 months, so you also need to add any new columns to it.

Social Security tax is set to automatically calculate if you enter the employee’s name and pay rate information correctly. You will only need to change cells in this column if you have employees who earn over $132,900 at some point in the year.

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 employee setup tab, you must delete it from the others.

For example, if you delete the Social Security tax rate in column G of the employee setup tab, you will need to delete the Social Security tax column (Column N) in the 13 payroll tabs (January through December and year-to-date). If not, you’ll receive error messages, and your formulas won’t work.

Change Column Names

Before adding and deleting, you should 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.

Change the “Other Deductions” label to fit your business.

To help, we added two Columns (N and O) in the “Employee Set Up” tab and labeled them “Other Deductions.” Just double-click on the description and overwrite with a more appropriate label.

The “Other Deductions” column sums all other deductions listed on the setup tab. Do not change it within the payroll tab.

Since we set “Other Deductions” up for you to be able to easily personalize, 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 “Employee Set Up” tab. If you need to add more columns for deductions in the “Employee Set Up” 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 setup, you can begin setting up your employee data. Enter names, hourly pay rates or annual salary, and number of pay periods in the year within the employee setup tab.

Also, enter all of your federal and state tax rates, except Social Security and Medicare (Columns G and H). Social Security and Medicare (FICA tax) rates are fixed until employees reach over $132,900 per year. Add benefits and deductions.

Don’t change the Social Security and Medicare tax rates on the setup tab.

Employee Name

Enter your employees’ full names under Column A in the setup 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 setup tab. If there are any differences, spelling or formatting, the monthly and annual payroll tabs will not be able to make their automatic calculations.

Another tip to remember is that when employees are terminated, you should not remove them, or your payroll data will be inaccurate. Remember, all 13 payroll tabs are linked to the “Set Up Employee Data” tab. If you delete employees from the setup tab mid-year, any prior months that reflected 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 next year, you can remove all inactive employees from the setup tab. If you need to add an employee, enter after the existing employees. Their payroll information will transfer to the other payroll tabs automatically when you enter their names in Column B of the payroll tabs.

Please note: The year-to-date payroll tab will automatically pull data without you having to enter anything. Your employee names will pull directly from the setup tab, and all corresponding amounts, such as pay and deductions, will populate from the monthly payroll tabs.

Straight-time Hourly Rate

Enter an amount in Column B for hourly employees. This should be the straight-time hourly rate you pay them, excluding consideration of overtime. Leave this cell blank for salaried employees.

You must enter an hourly rate in the setup tab, or your hourly employees’ pay will not calculate correctly.

Annual Salary

Enter the annual salary in Column C for your salaried employees. For example, if your employee earns $50,000 per year, enter “$50,000.”

Number of Pay Periods in Year

If you pay biweekly, enter 26 (52 weeks / 2 = 26) in Column D. If you pay monthly, enter 12 (12 months in a year). If you pay weekly, enter 52 (52 weeks in a year). If you pay semi-monthly, enter 24 (52 months / 2 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 rates depend on the number of allowances they entered (usually based on the number of dependents) and their filing status, like head of household, married, or single. Generally, the more allowances employees claim, the less their tax rates are.

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 predetermined by the IRS. If you have an employee who earns $132,900 in wages before the end of the year, you don’t have to withhold any more money for Social Security.

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.

Check year-to-date gross pay to determine if any employees have met the FICA tax thresholds. Be sure to deduct any nontaxable income from this total before you decide.

If you have an employee who meets the $132,900 earnings threshold for Social Security, for instance in April, delete all of the formulas in Column M (“Social Security Tax”) for that particular employee in payroll tabs May through December. Note that since 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 max x 6.2% = $8,329.80).

The Medicare tax rate is 1.45% on the 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 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.

If one of your employees’ pay exceeds $200,000 during the year, overwrite the Medicare tax rate on all of the future payroll tabs to reflect the 2.35% rate.

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 also.

Benefits & Other Deductions

We included numerous benefits you may offer, like health, dental, and vision insurance; 401(k) is also on the list. You will need to enter the employee’s premium per pay period in Columns I through L of the “Set Up Employee Data” tab for each that is applicable to your business.

You may not use all benefit columns for all employees. Leave cells blank for those that are not relevant.

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)

Using the PTO calculation tool is optional, but would be useful if you offer any holiday, vacation, or sick pay. Relying on your employees to keep track is not a good strategy, and assuming you will remember is not either.

“A PTO tracking system is both an imperative for the employer and a benefit for the employee, particularly with the Tax Cuts and Jobs Act (TCJA) Section 45S in place.

Once you have determined the algorithm for how PTO is earned, embed it in your payroll system. I don’t care if it is accrued once a year, each pay period, or by the hour. This allows you to track PTO in your payroll system every pay period. It helps maintain detailed records that you can supply to your employee. It allows you to track usage, prevents overusage, and keeps overtime down, as PTO hours don’t count for overtime calculation.”

– Charles Read, Owner, President & CEO, GetPayroll

If you opt to use the PTO calculation tool in the template, you will need to 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.” 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 tab sums all PTO hours you enter in Column F, “PTO Taken,” on the monthly payroll tabs. Column R, “Auto Calculation-PTO Hours Remaining” will keep you updated on how much PTO is remaining by deducting total year-to-date PTO taken from the total annual PTO hours. Watch this video on calculating PTO hours using Excel to help.

Enter your employees’ total PTO hours in Column P, and you should always be able to see how much is remaining to-date.

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. Since these taxes are usually charged per employee up to a certain dollar amount ($7,000), you should review the information in the payroll tabs each month to help you sum the amount due for each employee and the company.

Use the employers’ payroll tax tab to help you track the payroll taxes and insurance your business owes per federal and state laws.

Workers’ compensation insurance is another payment you’ll likely be responsible for. Rates and amounts can vary depending on the work your employees do, claim history, and other factors. 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.”

It’s important to calculate payroll accurately and file your taxes on time. If not, your business can face costly fines due to non-compliance. Small business payroll software Gusto calculates payroll taxes based on up-to-date state and federal labor laws and automatically files and pays the taxes on your behalf to keep you compliant. Try it free for 30 days.

Visit Gusto

4. Enter Hours Worked

Now that you have all of your employee and employer data set up, you’re ready to run your first set of payroll calculations. Go to the appropriate monthly payroll tab, e.g., “January Payroll” if it’s January. The first step is to enter the pay date in Column A. Then you’ll enter your employees’ names and hours worked.

Enter the pay date and employee name first. Then, enter hours that should be paid.

Enter the full name of each employee you are paying for the period in Column B. The name must reflect the same spelling as you entered it in Column A of the setup tab; if it doesn’t match, pay rates, tax rates, and deductions will not populate in the payroll tab. 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 on the monthly payroll tabs in Columns C and D are automated using a formula. When you enter your employees’ names under Column B, the straight-time hourly rates automatically carry over from the setup 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 the hourly rate columns that state not to change them. However, if your business operates in California, you may need to 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.

Edit the overtime hourly rate directly in the monthly payroll tabs if your state follows different overtime laws.

If you’re ever required to pay double-time, you’ll need to overwrite the formula in Column D, “Overtime Hourly Rate.” 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 hours below 12 (12 – 8 = 4) can be paid at a rate of 1.5 times the regular pay rate, and any hours above 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.

Enter bonuses in Column H, and the template will calculate taxes on it.

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. In Column I on the template’s payroll tabs, enter the amount you reimbursed the employee on the respective row. We have formulas in place that will disregard this amount when calculating taxes to withhold and pay.

5. Review Automatic Payroll Calculations

After you’ve completed your initial setup and entered employee work hours, all payroll calculations should be showing. 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 depends 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 setup 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 the annual salary by the number of annual pay periods you entered on the setup tab to reach the per period salary amount.

All amounts in Column J should automatically populate for each employee for whom you entered an annual salary amount and number of pay periods per year in the employee setup tab.

Straight-time Pay

Straight-time pay in Column K is made up of two parts, one being the number of straight-time hours worked times the straight-time hourly rate, and the other being the number of PTO hours taken times 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. Bonuses (in Column H) and reimbursements (in Column I) are also included.

FICA Taxes

FICA taxes are labeled as Social Security tax (Column N) and Medicare tax (Column O). These cells are linked to the setup tab and automatically pull the tax rates from there. The cells multiply the rates by the employee’s gross pay minus any nontaxable income (reimbursements).

Income Tax Withholding

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) calculate taxes based on the tax rate information you provided in the setup 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 one column (Column R) on each of the monthly payroll tabs. It sums all premiums showing in Columns I through L on the setup tab. The amounts should be fixed from one pay period to the next, but if the amounts ever change, you can update them in the setup tab.

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 tab for that particular worker. Overwrite with $100, and then update the setup tab.

Other deductions should be handled the same way as benefits. The “Other Deductions” column in the payroll tabs consists of garnishments and other deductions detailed in the setup tab.

Total deductions are the sum of all taxes withheld, premiums collected for benefits, and other deductions.

Net Pay

Net pay also calculates automatically, subtracting total deductions (Column T) from gross income (Column M). This is the amount you will pay out to your employees, through check, direct deposit, or paycard. Watch this video on calculating employee pay to help you get started.

Review the net pay for each employee. Scroll to the bottom of the spreadsheet to see total paid for the period or month.

6. Pay Your Employees

After you’ve reviewed your payroll tax, deduction, and wage calculations for the pay period, you’re ready to pay your employees. You can choose to print checks online for free, deposit money to employee paycards, 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 detail for the year in the “Year-to-Date Payroll” tab (last tab in the template). It’s linked to all of the monthly payroll tabs, so all employee payroll expenses should reflect. If you decide to provide pay stubs—some states require you to—this tab will give you the year-to-date information you need to complete. Download a free pay stub template to get started.

Process Payroll in Gusto

Processing payroll using a payroll provider, like Gusto, is much simpler than using an Excel template. You don’t have to enter any formulas or worry about errors due to name, tax rate, or benefit deduction changes. The majority of your work will be done in the beginning, during the setup phase.

The steps you will follow to process payroll in Gusto are:

1. Setup Employees & Company in Gusto

You will need to enter employee information, like name, pay rate, tax rates, and hourly versus salary status, in addition to details about your business, such as unemployment tax rates and workers’ compensation premiums. The good thing about entering this information in Gusto versus Microsoft Excel is that you can update all of this information throughout the year without compromising consistency and accuracy. Data for prior pay periods will not be impacted.

Click on “Employees” in the left-hand column of the dashboard to set up a new employee.

2. Review Employee Hours

Employees will enter their hours worked (versus you entering it in Excel), which means you only have to spend time reviewing and approving. Once you approve hours, you can sync them to payroll so they’re transferred to dollars to be paid.

Click ‘Sync Hours to Payroll” when you’re comfortable with all submitted hours.

3. Review & Update Hours & Pay Calculations

Once you sync employee hours worked, Gusto will calculate total pay. Review it for reasonableness, and then approve. You also have the opportunity to update hours or amounts if necessary. If you opt to track PTO within the system, you will have the opportunity to review that next.

Double-Check pay calculations and hour worked (again) for accuracy, and overwrite any errors.

Review and approve employee time off for the period so total pay is correct.

4. Final Review

After approving time off, hours worked, and pay calculations, you will have one last opportunity to review payroll data for the period. Check taxes, benefits, reimbursements, and net pay for reasonableness. For example, if you see $0 in taxes are being deducted, there’s probably a problem. You can go back to a previous step to troubleshoot. Click “Submit” when you are comfortable with the payroll details.

Check that there is a dollar amount under taxes; even if you’re a nonprofit that’s exempt from federal unemployment taxes, there should still be dollars reflecting for FICA taxes.

5. Pay Your Employees With Gusto

If you pay employees through direct deposit, Gusto will start processing after you submit the payroll. If you pay any employees via check, you’ll receive a reminder to pay them at this time. You can print the checks immediately after submitting payroll or wait until later. Regardless of when you choose to print, Gusto will walk you through the process.

You can choose to print payroll checks immediately after submitting payroll or opt to do it later.

Visit Gusto

How to Do Payroll in Excel Frequently Asked Questions (FAQs)

Listed below are the most frequently asked questions about how to do payroll in Excel:

What Excel tools or formulas can I use to evaluate payroll expenses in my Excel template?

You can use a pivot table to analyze the data if your data is organized in a table. It can help you see which employees are paid the most, the payroll taxes costing the most money, and who is costing 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 the specific data you want to analyze.

What Excel formulas can I use to calculate payroll taxes?

You can use the Excel “Sum” function 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 enter payroll data like hours worked and pay rates to calculate totals you can use to make payments.

You can also set one up 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 into your payroll process.

Where can I find other Excel payroll templates?

Microsoft has some downloadable payroll templates you can use. There’s 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 timesheets, pay registers, and employee attendance trackers.

Bottom Line

If you have 50 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. 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.

Visit Gusto