How to Do Payroll in Excel in 7 Steps + Free Template

Paying employees can be a complicated and daunting task, but it doesn’t have to be. Learn how you can automate your payroll in just seven steps using Excel.。

Payroll is a process that requires a lot of work. It can be tedious to do it in Excel, but if you use the “payroll excel formulas pdf” and follow these 7 steps, then your job will be much easier.

Payroll in Excel is appropriate for companies with less than ten workers that operate in states with simple labor and tax rules. Because it has pre-filled information like tax rates and overtime formulae, you may save time and handle payroll easily with our free payroll Excel template.

Consider adopting a small company payroll software like Gusto if payroll is still taking too long or if you’ve outgrown a spreadsheet. It submits new hire paperwork, calculates and submits payroll taxes, prepares year-end W-2s and 1099s, and pays employees through direct deposit. It also assists workers in selecting and managing their benefits. Sign up for a free 30-day trial right now.

Gusto is a great place to visit.

How Does Excel Payroll Work?

To make payroll in Excel, establish a consistent template that you may reuse month after month. It should feature monthly tabs with access to formulae for calculating employee taxes, deductions, and compensation. You’ll also need a “Set Up” page where typical information like pay rate and benefits enrolled may be retrieved for payroll calculations.

Maintain the “Set Up” page so you can quickly connect to it each month when you process payroll. This aids automation and avoids duplication of effort.

We’ve designed a payroll Excel template for you that includes 16 tabs: one for each month, as well as tabs for general instructions, employer tax information, employee data setup, and year-end payment information. After entering employee information (such as names, pay rates, tax rates, and deductions), all you have to do now is enter the actual hours worked, and the template will complete the relevant payroll data for you.

Follow the steps below to run your paycheck using our free Excel payroll template. You can also watch our video lesson on how to conduct payroll in Excel if you want to see the procedure in action.

1. Review and edit the Payroll Excel Template for your company.

It might take some effort to figure out how to process payroll using an Excel template. To begin, review the template and assess your company’s requirements. Here are some questions to help you figure out what information you’ll need to finish the template.

• How many people work for you?
• What kind of perks and deductions do you need to deduct from their pay?
• Is there anything more you need to include in the template?

Columns may be added or removed.

To fulfill the demands of your small company, you may need to add or remove columns. For example, if your company is based in a city that levies a local income tax (such as New York City), you must account for it in the payroll Excel template. You may accomplish this by filling out the “Set Up Employee Data” page with the necessary information. Because all of the remaining payroll tabs get their source data from here, you should always start here when making adjustments.

To add a column, choose the column letter you want your new column to appear in front of (for example, G, H, or I). Select “Insert” from the right-click menu. Fill in the rates for relevant workers and a title (in row 3) for your new column (for example, “Local NYC Tax”). Enter a new column between columns N and Q on all month tabs (from “January Payroll” through “December Payroll”), including the “Year-to-Date Payroll” tab (the tax columns). Identify this as a “Local NYC Tax.”

You must be consistent when adding new columns to the month tabs. If you add a “Local NYC Tax” column to column O on the “January Payroll” tab, you must also add it to the other month tabs in the same spot. You’ll also need to add any new columns to the “Year-to-Date Payroll” tab, which includes a 12-month summary, so that it accurately matches data from the monthly payroll tabs.

To delete a column, pick it with your mouse, right-click it, and then choose “Delete.” If you’re removing a column from the “Set Up Employee Data” page, the instructions are the same. If you remove a column from one of the payroll tabs, such as “Year-to-Date Payroll” or “Set Up,” it must also be removed from the others.

If you remove the “Social Security Tax Rate” column (column G) in column G of the “Set Up Employee Data” tab, you must also remove the “Social Security Tax” column (column N) from all 13 payroll tabs. You’ll get error warnings if you don’t, and your formulae won’t function.

This is the “Employee Data Setup” tab. Because the Social Security and Medicare tax rates in columns G and H are set, don’t make any changes to them.

Simply right-click on the column you want to add a new one in front of and choose Add Column.

Once you’ve entered data in columns A through I, look at how the cells containing formulae produce totals automatically.

Change the names of the columns

Consider if you can simply update any of the current column labels in the payroll Excel template when adding and removing columns. Aside from reducing mistakes, you’ll save time by not having to switch between many tabs.

To make things easier, we added two columns to the “Set Up Employee” tab (columns N and O) and titled them “Other Deduction.” Simply double-click the description and replace it with a label that better fits your requirements.

It’s worth noting that the “Other Deduction” columns were designed to be readily customized, so you won’t need to edit any other tabs to match. In the 13 payroll tabs, there is just one “Other Deductions” column, and the data shown here is the total of columns N and O in the “Set Up Employee Data” tab. If you need to add extra columns to the “Set Up” tab for deductions, follow the instructions above.

You may enter various deductions that are special to your organization in the “Other Deduction” columns in the “Set Up” page.

Although the “Set Up” tab has two “Other Deduction” columns, the monthly paycheck tabs combine them into one column.

2. Set up each employee’s payroll information

Once you’re happy with the template’s layout, you can start entering employee information. You’ll need the following information to finish the “Set Up” tab.

Name of the Employee

In the “Set Up Employee” tab, enter your workers’ complete names in column A. It’s important to remember that you’ll have to re-enter their names on the monthly payroll tabs. Make sure it’s the same as the name you specified in the “Set Up” page. The monthly and yearly payroll tabs will not be able to conduct automated calculations if there are discrepancies in spelling and formatting.

Employees who have been fired or quit should not be removed from the “Set Up Employee” menu. Any preceding months indicated in their payroll will adjust to $0 if you eliminate workers in the middle of the year, and your year-to-date payroll expenditure figures will alter. When you have new recruits, submit their information after the current workers in the “Set Up” page; information will be auto-transferred to other tabs after their names are entered.

**If an employee has resigned or been fired, just delete their paycheck data from the monthly payroll tabs. When you construct the payroll Excel template for the following calendar year, you may delete any inactive workers from the “Set Up” tab.

Hourly Rate for Straight Time

Enter each hourly employee’s straight-time hourly rate (excluding overtime) in column B of the “Set Up Employee Data” tab. For paid staff, leave this box blank.

Salary per annum

Input each salaried employee’s yearly wage in column C of the same tab. Enter “$50,000” if your employee makes $50,000 per year, for example.

The number of pay periods in a year is the number of pay periods in a year.

Enter the appropriate payment period in column D, labeled “# of Pay Periods in Year,” depending on how often you pay workers.

• If biweekly, enter “26” (52 weeks divided by two pay periods = 26).
• Input “52” if it’s a weekly schedule (52 weeks in a year)
• Input “24” if semimonthly (52 months divided by two pay periods per month = 24).
• Input “12” if it’s a monthly payment (12 months in a year)

Rates of Federal and State Income Taxes

In columns E and F, enter the federal and state income tax rates for each employee. Use the W-4 forms that you gathered from workers when they joined your organization to discover the suitable rates. Your workers’ tax rates are determined by the number of allowances they claimed (typically based on the number of dependents) and their filing status (married, single, or head of household). Employees’ tax rates are often lower the more allowances they claim.

There are no formulae in the cells of columns A through F in the “Set Up Employee Data” tab. Make sure you submit proper information by consulting your payroll records.

The “Year-to-Date Payroll” tab is located here. This tab will automatically fill with all data, so you shouldn’t have to modify anything in the cells.

Tax Rates on Social Security and Medicare

In columns G and H, don’t make any modifications to the Social Security and Medicare tax rates. The IRS has set these Federal Insurance Contributions Act (FICA) rates in advance. Keep in mind that if an employee makes $132,900 in salary before the end of the year, their Social Security benefits are capped. If your workers make more than that, you must cease withholding the 6.2 percent tax once their YTD earnings reach that level.

For example, if your employee earns $132,900 in April, erase all formulae in payroll tabs May through December for that employee under “Social Security Tax” (column N). The highest amount any employee will pay yearly is $8,239.80 ($132.900 maximum x 6.2 percent = $8,329.80) since the Social Security tax rate is 6.2 percent of earnings.

Look at column M (labeled “Year-to-Date Gross Pay”) on the “Year-to-Date Payroll” tab to see your workers’ total earnings. This column displays the workers’ gross earnings for the year to date. To calculate taxable gross income, subtract any non-taxable income (column I) from this amount.

The tax rate for Medicare is 1.45 percent on the first $200,000 in earnings earned by an employee, and an extra 0.9 percent if the earned wages surpass $200,000 in a calendar year. This raises the total Medicare tax for such people to 2.35 percent. If you have workers who earn more than $200,000 in gross pay, you must alter the formulae in the “Medicare Tax” column (column O) on all monthly payroll tabs after the month the employee earns more than $200,000.

Take a look at the screenshot below, particularly the formula field, for more information. Substitute “2.35 percent” for the (gray) highlighted portion of the calculation. The link to the employee’s Medicare tax rate (which is 1.45 percent) on the “Set Up Employee Data” page will be overwritten if you do so.

Check and update the calculation in the “Medicare Tax” column on appropriate monthly paycheck tabs if your employee earns more than $200,000 per year.

It’s worth noting that the Social Security eligibility level has risen for the last eight years, but the Medicare tax rate has remained same for the previous six. Check for any tax rate changes at the beginning of each year to guarantee compliance, and then update the payroll Excel template appropriately.

Other Deductions and Benefits

The template has various columns for benefits such as health, dental, vision, and 401(k) plans that you may provide to your workers. Enter the workers’ premium per pay period in columns I through L of the “Set Up Employee Data” page for each benefit that applies to your company.

For garnishments and other employee deductions, utilize columns M, N, and O. When you submit the workers’ names in column B of the monthly paycheck tabs, the template will automatically complete the required columns once you provide the benefits and deduction data on the “Set Up” tab.

Calculations for Paid Time Off (PTO)

It’s not a good idea to rely on your staff to keep track of their PTO. We added an optional PTO calculating tool to help you keep track of this.

Check the final three columns in the “Set Up” page that apply to PTO if you opt to utilize the PTO calculation tool. Enter the total PTO hours that your workers are entitled to in a year in column P (“Enter Annual PTO Hours”).

The template will automatically collect the total PTO hours each employee has used from the “Year-to-Date Payroll” tab, so you don’t have to fill in column Q (“Auto Calculation-PTO Hours Taken”). Column R (“Auto Calc-PTO Hours Remaining”) is the same way, since it includes a formula that subtracts the actual PTO taken from the employee’s yearly entitlement.

It’s worth noting that you’ll need to keep track of each employee’s actual PTO hours for the corresponding payroll period of each pay cycle individually. Enter the information in column F (“PTO Hours Taken”) of the monthly payroll tabs, and the “Year-to-Date Payroll” tab will calculate the total number of PTO hours per employee.

You may withhold premium deductions for popular insurance benefits in the columns provided.

There’s an area for keeping track of PTO. It will update when you fill in the specifics of your paycheck each time.

3. Set up Payroll Tax Information for Employers

You’re responsible for paying your portion of payroll taxes, in addition to keeping track of withholdings from employee paychecks. You may input your federal and state unemployment tax rates in the “Employer Payroll Taxes” tab’s cells. It’s worth noting that the federal unemployment tax of 6% is only applied to the first $7,000 earned by each employee (most state unemployment taxes follow the same logic). Make sure to go through the information in the payroll tabs each month so you can figure out how much tax each employee and the firm owe.

You’ll almost certainly be liable for paying workers’ compensation insurance premiums as well. Rates and amounts may vary based on the kind of job your workers undertake, their claim history, and other reasons. However, the insurance fund that establishes your coverage should send you a monthly premium payment. In column G (“Enter Workers’ Comp Payable”), just enter the monthly premium amounts.

To keep track of the taxes your company owes, go to the “Employer Payroll Taxes” page.

4. Fill in the hours worked and other income information.

You’re ready to compute employee payouts after inputting all of your employee and employer data into the payroll Excel template. Go to the relevant monthly payroll tab, such as “January Payroll” if it’s January. In column A, write the pay date, and in column B, write the entire name of each employee you’re paying for the period. Then, in the appropriate columns, enter the actual regular hours worked, extra hours, PTO hours taken, and other revenue data.

Remember that the name you input in column A of the “Set Up Employee Data” page must be the same. The pay rates, tax rates, and deductions will not appear in the monthly payroll tabs if they don’t match. Copy and paste the names if you’re worried you’re not typing them properly.

Hours of Straight-Time and Overtime

Enter the right amount of straight-time hours in column E and overtime hours in column G of the monthly payroll tabs for appropriate employee payouts. Unless your company is headquartered in California, straight-time hours are normally 40 hours or fewer. Overtime pay is calculated using the amount of hours you indicate and is calculated at 1.5 times your usual pay rate. However, depending on the labor regulations in the state where your company operates, this may be amended.

We recommend that you check your state’s overtime legislation. Overtime compensation is normally required by federal law for workers who work more than 40 hours in a seven-day period. California, on the other hand, considers all hours worked in a day above eight to be overtime, regardless of whether you work 40 hours or fewer in a week.

Hourly Rates: Straight-Time vs. Overtime

A formula is used to calculate the straight-time and overtime hourly rates reported in columns C (“Straight-Time Hourly Rate”) and D (“Overtime Hourly Rate”) of the monthly payroll sheets. There’s also a statement above both hourly rate columns stating that the contents of the cells will update automatically if you don’t modify them. As a result, anytime you input your workers’ names in column B of the monthly payroll tabs, the straight-time hourly rates from the “Set Up Employee Data” tab are immediately carried over.

Meanwhile, in column D, the overtime hourly rates are computed at 1.5 times the straight-time hourly rates in column C.

If your company is based in California, you may need to make changes due to the state’s regulation that specifies that workers who work more than 12 hours in one day are entitled to double compensation. As a result, any time worked between eight and twelve hours must be paid at 1.5 times the straight-time rate, and any time spent beyond twelve hours must be paid at twice the straight-time rate. In this situation, you’ll need to adjust the calculation in column D to reflect the amount you owe.

For instance, if you need to pay double time for an employee who worked 16 hours in a day, you may use this formula to replace the present computation. It’s worth noting that the formula is split into two parts: four hours will be paid at time and a half, while the remaining four hours will be considered double time:

= (4*(C4*1.5)+(4*(C4*2))+(4*(C4*2))+(4*(C4*2))+(4*(C4*

Income: Taxable and Non-Taxable

If you give an employee a bonus, you must withhold payroll taxes in the same manner that you do with normal compensation. Simply input the bonus amount in column H of this payroll Excel template, and it will be reflected in the taxable gross income.

Note that reimbursements for expenditures spent by workers while on business trips are typically not taxable transactions. This is because you are merely reimbursing workers for the money they spent to pay for company expenditures; the employee did not get any extra revenue as a result.

You don’t have to withhold payroll taxes in this scenario. Instead, in column I of the corresponding monthly payroll tab, enter the amount you need to refund the employee. We’ve set up formulae to ignore this amount when computing taxes to withhold and pay.

You may add your workers’ names and real hours worked in individual sections on the monthly payroll tabs.

The overtime rate in column D is calculated using a method that essentially doubles the straight-time hourly rate you specify in column C by 1.5. (time and a half).

Column H should be used to record bonuses and other taxable income.

Try Gusto if learning how to make payroll in Excel seems too difficult. Without having to update spreadsheets, you may pay your staff immediately and approve their hours worked online.

5. Examine the Payroll Calculations That Are Made Automatically

All payroll calculations should be accessible when you complete your first setup and input employee work hours. To avoid having to invalidate and resend payments later, it’s a good practice to check them before paying workers. The precision of your payroll tax computations is also determined by the correctness of your employee payroll data. If you’re not cautious, you might face fines. Here are the elements to go through, along with how the payroll Excel template calculates them.

Periodic Salary

Salaries are automatically retrieved from the “Set Up Employee Data” tab in column J of the monthly payroll tabs. The column will automatically display the employee’s salary for the period if the template contains the employee’s yearly salary and the total number of pay periods in the year. Note that the columns in column J feature formulae that simply divide the yearly salary by the number of annual pay periods you specified on the “Set Up Employee Data” tab when calculating the per period salary amount.

Pay on the Spot

The “Straight-Time Pay” column (K) of the monthly payroll tabs includes automated algorithms, same as column J. The number of straight-time hours worked multiplied by the straight-time hourly rate, as well as the number of PTO hours taken multiplied by the straight-time hourly rate, make up this calculation. This ensures that you pay workers all of the money they are owed, particularly for PTO hours.

Pay for Extra Time

The number of overtime hours you put in column G and the hourly overtime rate you entered in column D of the monthly payroll tabs are used to determine overtime compensation. There should be a comparable monetary amount in column L if you have overtime hours recorded in column G.

Gross Pay

The entire income an employee is due before deductions and taxes (column M). It’s computed in this payroll Excel template by summing the monthly payroll tabs’ columns J through L, plus columns H and I. Employees’ per-period salaries, straight-time pay, overtime pay, bonuses and commissions, non-taxable income, and reimbursements are all shown in these columns.

FICA (Federal Insurance Contributions Act) taxes

FICA taxes appear on the monthly paycheck tabs as “Social Security Tax” (column N) and “Medicare Tax” (column O). These columns are connected to the “Set Up Employee Data” page, and the necessary tax rates are automatically pulled from there. The rates are then multiplied by the employee’s gross pay less any nontaxable income in the cells (such as reimbursements).

Withholdings on Income Tax

Withholding federal income taxes from your workers’ paychecks is your responsibility as an employer. Depending on the state, you may be required to withhold extra sums. Columns P and Q (labeled “Federal Income Tax” and “State Income Tax,” respectively) in the monthly paycheck tabs compute taxes depending on the tax rate information you entered in the “Set Up” page.

Other Deductions and Benefits

On each of the monthly payroll tabs, benefits are indicated in column R, which adds all premiums shown in columns I through L on the “Set Up Employee Data” page. The amounts should remain consistent from one pay period to the next, but if they vary, you may edit them under the “Set Up” page. If you want to adjust an employee’s benefit deductions, you must first rewrite the benefit deduction formulae for the previous months, then overwrite it with the actual cash amounts.

For example, if you want to increase an employee’s health insurance deduction from $100 to $200 beginning in June, go to each previous month’s payroll tab (January through May) and erase the formula in the health insurance column for that employee. Replace $100 with $200 and then edit the employee’s health insurance deduction to $200 in the “Set Up” tab.

Benefits should be treated the same manner as other deductions. Garnishments and other deductions described in the “Set Up” tab appear in the “Other Deductions” column (column S) in the monthly paycheck tabs. The “Total Deductions” (column (column T) represents the total of all taxes withheld, benefit premiums collected, and other deductions.

Net Pay

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

Cells that have “N/A” in them will remain that way until enough employee and/or paycheck information are provided for the computation to be completed.

Employees’ net salary is shown in Column U.

Would you want to learn more about how this Excel payroll template calculates wages and deductions? Check out this video on how to calculate employee compensation.

6. Reward Your Workers

You’re ready to pay your workers after reviewing your payroll taxes, deductions, and wage calculations for the pay period. You may achieve this by printing checks online, depositing funds to employee pay cards, or working with your bank to execute direct payments.

7. Examine year-to-date payroll data

In our payroll Excel template, the final tab, labeled “Year-to-Date Payroll,” provides all of your employee payroll data for the year. Because it’s related to the monthly payroll tabs, it should display all employee payroll expenditures. If you want to produce pay stubs—some states demand it—this page will supply you with the YTD data you’ll need to fill them out.

Conclusion

If you have less than ten workers and reside in a state where labor and payroll laws aren’t tightly regulated, knowing how to make payroll in Excel might be beneficial to your company. You can keep track of the payroll taxes you owe and manage payroll records online in addition to paying workers.

Consider Gusto if you’ve outgrown spreadsheets and want a payroll system that you can quickly customize to match your company’s requirements. Starting at $45, it provides full-service payroll with automatic taxes and paperwork, as well as the ability to execute payroll on autopilot.

More information may be found in our Gusto review.

The “free excel payroll template” is a step by step guide that will teach you how to do your own payroll. The article includes the steps and also provides a free template for you to use.

Related Tags

• excel payroll formulas
• payroll spreadsheet excel template
• how to make a payroll system in microsoft excel with payslip
• excel for payroll course
• payroll spreadsheet template free