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

Cody Cromwell
Written by
Last update:

How Doing Payroll in Excel Works

This is a great resource for anyone who needs to calculate payroll, including employers or self-employed individuals. It's designed to take you from creating a simple payroll calculation to creating the entire solution, step-by-step.

Rather than overwhelm you with tables and calculations, this article provides the instructions you need to follow with step-by-step instruction along with plenty of screenshots to help you along the way.

One of the great features of this tutorial is that you can use it as a template. Create a new spreadsheet, name it "Payroll," and fill it out with your own data. Then follow the instructions to create the payroll, changing your data as you need to in order to make the calculations automatically.

Then you can go back through these steps again to do payroll on a recurring basis.

If you're just getting started with Excel or you're looking to do a little number crunching, this article is a great place to start. Read More >>

Are you doing an employee count in Excel but aren't quite sure how to do it? This article will walk you through calculating an employee count for all employees, full-time and part-time workers, at your business.

Review Payroll Excel Template & Edit For Your Business

Many small businesses are started with the same humble aim. To make money from home. Most of those businesses can barely go a week without relying on the income they receive from their customers in some shape or form.

When you break down this idea, you’re essentially providing a service to your clients and you depend on your clients to earn enough money to keep your company running. For those businesses that are self-employed or have incorporated their businesses, this service being provided includes the people that have paid you. If you’re just starting a small business, the amount of money you’re aiming to make from your clients may be next to nothing, but that doesn’t mean you can’t get organized with your business financials.

Financial management is something we all have to deal with … whether we’re self-employed or incorporated. However, it’s not always possible to afford a professional to look after your business financials, especially if you’re just starting your business. Fortunately, there are plenty of free spreadsheet templates and tools that you’re able to use to help you manage your business finances. A business loan can also help you manage this part of your business.

This post can help you get organized with your business finances.

Add or Delete Columns

As you’ve already seen, to add columns to your table, click on the cell “Click here to add new columns to the table.” The keyboard shortcut for this task is Ctrl + Shift + Enter.

Generally, you only have to add a column to the end of your table to force it to grow to fill up all the remaining space. But in some situations, you may want to add a column to the beginning of your table. For instance, you might need to add an “Employee ID” column to your table.

Change Column Names

When doing payroll, you will now and then have to change the names of the columns in the table.

It’s easy to change the column name in your spreadsheet in Excel, you just need to click the square right next to the column header, then choose the name you would like to call it.

You can do this again and again, then you can easily add new columns with the correct names. If you’ve just added your first column, it will be automatically added with the default name … so you can just call it new (or whatever you like).

If you change your mind, the column name can be easily changed again using the same method.

Set Up Payroll Information for Each Employee

The first step is to set up payroll information for each employee in the payroll spreadsheet. Note that in our example, we’re assuming that all employees have the same name, address and email address. However, the payroll spreadsheet can hold multiple payroll information for an employee such as different method of payment, different pay dates, and so on. Nevertheless, the payroll spreadsheet is a reference point for all these different payroll information.

Add Employee Information to Payroll Information

Next, you need to add the employee’s name, address and email address to the payroll spreadsheet. In Microsoft Excel, you can use the Payroll Information heading to help organize the different payroll information for each employee.

Write Down Pay Methods for Each Employee

After adding employee information to the payroll spreadsheet, you need to specify how each employee is paid. In our example, we’re assuming that employees are paid twice a month using direct deposit. This arrangement can be any combination of cash, check or other payment method.

Specify Method of Payment for Each Employee

After adding information on the manner of payment for each employee, you need to mention the specific dates that each employee is paid. This information comes directly from the employees’ pay stubs. Note that this information can be found on each employee’s pay stub.

Employee Name

Payroll, and Taxes.

When setting up payroll in Microsoft Excel, you’ll most likely want to get to know each tab in Excel’s payroll software from whatever viewpoint you’re comfortable with.

Straight-Time Hourly Rate

First up is the straight-time, hourly rate. You should already know how this works. To calculate your straight-time hourly rate, you need first to know your job’s weekly earnings. Once this is done, you simply add up how many hours you worked that week and divide by the whole number of hours in the work week. Let’s say that your weekly earnings total 5,000.

Annual Salary

In Excel: Step-by-Step Guide

Number (#) of Pay Periods in Year

There are 31 calendar days in a month. This is the number of days in a pay period (the pay period may be a month or a quarter).

It is not important whether the pay period is a month or quarter.

The following formula can be used to calculate the number of pay periods in the year.

=D5-D4/4-D5/4

This formula calculates the days in one month and subtracts it by four days to account for the weekly pay allocation.

This formula equals to 31.

D5 is the number of days in the last month and D4 is the number of days in the first month.

D4=14 is the number of pay calendar days for February in a year.

Therefore, =84-31=52.

=52/4=13+13+13+13+13+13+13+13=52.

Therefore, 13 pay periods.

Federal & State Income Tax Rates

American payroll is a headache that most employers would love to avoid. Even after the passage of the Affordable Care Act and the tax reforms of last year, most blue-collar workers are still confused about their payroll requirements.

The DOL has done its part to put an end to this confusion though, having re-issued the following table that points out the current Federal and State tax rates for the lowest income bracket in each of the States.

As you can see, there are a lot of variations of payroll requirements in the States, and there will continue to be, especially since some States are still deliberating higher taxes. Keep up to date by visiting your local DOL office… or plan ahead by reviewing the payroll requirements.

Social Security & Medicare Tax Rates

You can download all the tax tables below to work with at your next tax deadline.

1040A Instructions >>

1040 Tax Tables:

  • Single (12% tax)
  • Married (15% tax)
  • Married Filing Separately (10% tax)
  • Head of household (10% tax)
  • 1040 Instructions >>

1040 Rate Tables:

  • 1040A (10% tax)
  • 1040EZ (6% tax)
  • 1040 (0% tax)
  • 1040 Schedule B Instructions >>
  • 1040 Schedule B Instructions >>
  • 1040 Schedule C Instructions >>
  • 1040 Schedule D Instructions >>
  • 1040 Schedule SE Instructions >>
  • 1040 Schedule SE Instructions >>

Benefits & Other Deductions

Pet care, car care, and professional services are just some of the examples of expenses you have that you can include in the deductions of your paycheck.

Sponsoring a child, an elderly parent, a disabled friend, or a relative, is another way of supporting others. You can deduct certain expenses like your cost of a suit or the money spent while attending a funeral. You can also deduct certain taxes on your expenses, if you were eligible to deduct them (see below).

The deductions you can take vary based on the type of taxes you withheld from the paychecks you received.

Expenses that Can Be Tax Deducted

You can deduct medical and dental expenses that you paid for yourself, your spouse, and your dependents (children, parents and grandparents). Expenses are expected to cover the cost of insurance, medicines, health care, and for some expenses, part of the cost of insurance.

You can deduct certain expenses that you pay for housing. Examples include rent (including pet rent), utilities, water, and taxes, mortgage interest, insurance, and deposits.

Some expenses are eligible for a tax break if they are related to your job. For example, if you take classes or work to improve your skills, you can deduct that cost. If you need a computer to perform your job, you can deduct your computer expenses.

Calculate Paid Time Off (PTO)

Set Up Employer Payroll Tax Information

Once you have setup your information, the next step is to set up payroll taxes. To do that, go to File>Options>Taxes and select the appropriate payroll tax.

Be sure to set up your branch and the state that you file your reports with.

Enter Hours Worked

The number of hours worked is usually automatically entered in the Paycheck by default. This is done by the employee entering their nine-digit employee ID number and the ending time of the workday.

This is done by interacting with a calculator and then clicking the Input button option. The number of hours worked is then automatically located in the bottom-left corner of the screen.

Ms. Forster indicated that it took a few edits to make it all work correctly. Once entered, it looks like this:

To enter a meal break (with or without overtime), the employee should click on the Calculate button first and then input their input.

Once the employee has completed their work day, companies use a payroll program to compute the employee’s pay based on the hours worked. The employee’s tax status can be viewed by clicking the icons which include tax credits, federal withholding and the tax line amounts.

Once the checks have been printed, they may be produced to the appropriate departments for deposit.

Hours Worked & Other Income

In the event of a change in hours worked for the employees, you will need to fill the income tax form (I-TR) to claim for extra expenses you had to pay because of the extra hours. This form has been designed by the government for this purpose.

In the US (and other countries), for each pay period, you need to add pay for each employee (rounded up to next whole dollar).

In addition to the result of a conversion, you will need to use tax hours worked form to calculate how many hours are actually worked in order to calculate working time.

The working time form will allow you to enter a size of the product and then indicate all expenses that are directly related to each product.

If you're employed by companies that have multiple locations and sell products across multiple locations, then you are going to receive a total amount of pay for all these different locations. In this case, the FSD form needs to be filled for each different location.

Review Automatic Payroll Calculations

A payroll system is one of the very important financial functions for any company. It is the continuous process of calculating and creating payroll EFTs every time new employees join the company.

This function is done by Excel payroll modules. These modules make easy the process of creation of payroll calculations automatically and instantly. These modules are coded in such a way that the payroll calculation is done several times, for entire month or quarter or year or any time period.

These modules are compatible with most payroll providers. After you install the payroll modules into the Excel file, these systems can keep you in touch with your accounting department.

2-Hour Excel with VBA Demonstration + Sample Files

After reading this tutorial, you can easily design and configure a payroll setup in your company, with the help of payroll modules.

Useful Data:

Salary per Period

Salary is paid once a month. As it is a fixed amount, it is divided into equal parts and paid out monthly.

Straight-Time Pay

The straight-time payroll system is based on the traditional pay period.

It can be used in both manufacturing and non-manufacturing companies, but it is most commonly found in manufacturing environments. The straight-time payroll system does not have any features for covering part-time workers.

When you use this method, you pay the same rate for all of your employees. This system works when you have a normal number of employees, but you cannot accurately predict how many employees will work a pay period. The only time you want to use this method is when you have a stable number of employees throughout the pay period.

This is the most common system when you do payroll on a daily basis.

When you use the straight-time payroll system, you have more flexibility at the beginning of the pay period when people are observing vacation, and at the end of the pay period when people are observing vacation, and at the end of the pay period.

This is true because you are adding people's pay amounts to the payroll totals for the pay period, and employees usually do not get their check until the end of the pay period. The disadvantages of this system are that you have to double check to make sure that people have enough hours worked to have the correct gross wages at the end of the pay period.

When you want to pay part-time employees, you have two options:

Overtime Pay

When you go to your favorite bar to do some last minute work after your shift is done, you can identify the workaholic sitting in the corner either typing away bringing in some papers and projects, or closing out his/her tab and packing up their stuff. It’s usually an employee that has been here for more than six months. Overtime + Payroll = Stress

You’re kicking yourself, thinking about all that money you spent on vodka cranberries and last calls. If you’re done with the kind of work that requires you to put in late hours, you’re going to have a rough time of it. Also, overtime pay during pay period, mortgage, car, and credit card payments don’t leave much money to go around.

Luckily, there are some automated ways to do overtime pay. Did you all know that apart from being a fun guessing game, it is also an effective way to motivate your employees and keep a record of those who opted for contributions during the quarter? You can plan to do overtime pay by the end of the year, iterate on a quarterly basis and see how much you got. It’s easier when you automate the payroll process.

Gross Pay

It’s simple, easy to use and made for businesses just like you. The fully automated PAYE system tracks information for an employer in a self-service web portal.

A complete and platform-independent solution including D&B e-Billing, salary and pay accounts, payments and reference checks. Supports cash, time card, fixed price and invoice calculations, digital receipts, compliance testing and more.

We are a global tax consultants specialized in e-Tax.

Internet Years has been serving the payroll, tax and treasury industry since 1999, including small businesses, S corporations, multi-state corporations, holdings companies, partnerships, LLCs, limited liability companies, estates and trusts.

FICA Taxes

Most of the time, you don’t need to calculate your FICA taxes. They are included in your payroll deducted. However, you should calculate your FICA taxes when:

  • If you are not paid for the week
  • If you are paid twice during the month
  • If you are paid through payroll deductions

You don’t need to see your payroll to figure out your FICA taxes. You can use your paystub as a reference.

To CALCULATE FICA TAX

  • Take your gross pay for the time or period to calculate W2 tax
  • Add your net pay
  • Subtract the FICA deductions
  • Multiply your gross pay by 0.1 to get your gross salary
  • Multiply your gross salary by 0.2 for the W2 tax
  • Subtract your W2 tax from your gross pay,

PICK up the DIRTY WORK

We put together a template to make this handy little process easy.

Go ahead … make your payroll. Download the template here.

Income Tax Withholdings

Financial payroll services like Paychex handle the majority of your payroll. The companies you work directly with are dedicated to providing you with excellent customer service and superior performance that leaves you with no questions. A payroll service combined with a bookkeeping service, like those provided by Xero, eliminates the need for most of the routine and time-consuming paperwork.

With the income tax withholding calculator, you can learn how these services can help you with your payroll and can save you valuable time you may use in other ways…

Benefits & Other Deductions

Pet care, car care, and professional services are just some of the examples of expenses you have that you can include in the deductions of your paycheck.

Sponsoring a child, an elderly parent, a disabled friend, or a relative, is another way of supporting others. You can deduct certain expenses like your cost of a suit or the money spent while attending a funeral. You can also deduct certain taxes on your expenses, if you were eligible to deduct them (see below).

The deductions you can take vary based on the type of taxes you withheld from the paychecks you received.

Expenses that Can Be Tax Deducted

You can deduct medical and dental expenses that you paid for yourself, your spouse, and your dependents (children, parents and grandparents). Expenses are expected to cover the cost of insurance, medicines, health care, and for some expenses, part of the cost of insurance.

You can deduct certain expenses that you pay for housing. Examples include rent (including pet rent), utilities, water, and taxes, mortgage interest, insurance, and deposits.

Some expenses are eligible for a tax break if they are related to your job. For example, if you take classes or work to improve your skills, you can deduct that cost. If you need a computer to perform your job, you can deduct your computer expenses.

Net Pay

Upon completion of the W2, you will receive a Net Pay statement that you will need to write a check to the government for FICA and Medicare taxes.

You’ll also receive OASDI and Medicare tax forms to file with your follow-up W2.

Net Pay (called Withholding as the business sends the taxes to the government) needs to be done through PayPal. This protects your personal payment information as well as streamslines the process.

Pay Your Employees

Whether you’re an accountant, a business manager or simply a people manager, doing payroll is one important task in your busy schedule. Giving your employees their pay is an important part of being a people manager, and it’s one of the best things you can do for fulfilling your role. It’s important to know the ins and outs of what’s involved in employee pay, and it’s not as difficult as you may think.

Here we’ll teach you a process for doing payroll in Excel. Payroll in Excel gives you an easy way to track the money going from your business into the bank account of your employees. It also allows you to track the details of your employees’ work and time off due to illness or injury.

It’s easy to set up, especially with the steps and templates you can download in an instant .

Payroll in Excel allows automatic setup for pay periods and employees, and it’s able to figure out the tax owed and the direct deposit process. This is handy for having your tax obligation handled for you.

Review Year-to-Date Payroll Information

Keep the information above the Summary box in mind as you build your payroll layout.

The Summary box should list steps in the order that you want to move through them in the Date Worked column.

The Date checked in the Pay rate column tells you the date that an employee worked.

The Pay rate in the Pay Rate column tells you how much you will pay each employee.

The Time worked in the Time worked column tells you the number of hours worked by each employee.

There are two other columns of information included in the payroll layout. The employee name, and division name.

This is your basic payroll format. This layout is ideal for non full-time employees.

This is for top management. Make the sections large enough to fill out each line.

This format is for full-time and part-time employees who have been working at your company for a long while.

Details the employee expenses, earnings, and taxes.

Everything is pretty much the same as the basic format with the exception that each full-time employee also has a 0.00% tax deduction in each section.

Holds overtime information.

Holds FMLA information.

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?

This step is fundamental to managing payroll in Excel. Since 2007, tax withheld and paid are displayed in shape and color in a drop-down list. You must first know how to get this data in Excel.

How can you know how much money has been withheld and paid for taxes in your accounting? To simplify, it remains the most popular and used way to do payroll Excel tasks.

Step One: Get the records from your payroll company

This step is to choose a payroll software for payroll planning. It would be easier to use the software to get the records of the employee from the payroll company. Otherwise pay cash in advance will need to visit the payroll office frequently to get reports.

Step Two: Calculate the taxable amount

When you got your payroll records, it’s time to calculate the taxable amount. The taxable amount is your gross taxable amount.

Step Three: Calculate the number of withholding deductions

The next step is to calculate the number of withholding deductions. You can download the payroll data into your computer to make calculations.

Step Four: Write in the worksheet

There are two ways to write the worksheet. The first way is to do the calculations done in Step Three and by data entry. The second way is to use formulas to calculate the taxable amount with the help of the payroll data.

Step Five: Calculate the net taxable amount

What Excel formulas can I use to calculate payroll taxes?

In general, you can use Excel formulas to perform payroll calculations. However, a handful of payroll calculations require a third-party payroll service. Any calculations that involve employee deductions such as 401K and employer-paid health insurance come with a calculator. Here’s the scenario directly from the US government site:

To determine the employee share of a self-employed SEP-IRA deduction, you must access the SEER/SES online calculator at IRS.gov/payroll.

The IRS does not verify the accuracy of the results that the calculator provides. If you need to report the figures, you can use the information from the calculator, but you must carefully evaluate the results to confirm that the figures are reasonable and not overstated.

While each of these sections has its own specific guidelines, the more general rules are:

None of these sections require the use of a software program where the calculations can’t be done in Excel itself.

If you need to make direct payments to employees, you need to consult with your payroll processor or rely on deductions from your payroll service.

The figures that you obtain from your payroll processor and payroll service won’t be used for tax returns or audits.

How do I create my own Excel payroll template?

Creating additional payroll templates is easy. It is also a good idea to have three types of paydays and other dates (such as the date the employee was hired, etc.). If you want additional information about payroll, make sure you click Get Info on the top-right corner of the cell. Now you will view the cell’s attributes. Here you can set information about your personal preference, color, sorting, etc.

Where can I find other Excel payroll templates?

There are a lot of free payroll templates online, but every template is different. You can get a lot of great info on the internet on how to do payroll in excel, but it's not always the provision of a Word document. How to do payroll in excel is important to learn about because it is the actual action you take when you need to know how to do payroll in excel, as well, as excel payroll templates. But the problem we often have is how do I find different Excel payroll templates when I need to know?

You can find templates on the internet on how to do payroll in excel, but that is with the basic payroll templates. With a bit of free Excel spreadsheet knowledge, you can use the basic pay range and wage amount (and related payroll information) to create a super useful Excel payroll template, or even a spreadsheet that can calculate pay range and wage amount. This is a powerful spreadsheet formula that has been around for many years; originally it was used to find a monthly salary by year, so it is about how to do payroll in excel all the time.

Bottom Line

I really enjoyed creating this tutorial. With it, I wanted to make an Excel document that I hope will help the beginners. We hope that you will not only learn how to do payroll with Excel but also get free bonus Checkbook Features included. We thought that these checkbook features can help you excel while doing financial records.

So, after going through the article, create the Excel payroll template which will help you pay your employees.

This Excel payroll document contains 5 worksheets. The first worksheet is for payroll hours. So, do everything related to time. The second worksheet is for daily pay, vacation and sick days. Then, the third worksheet is for weekly payroll.

Fourth worksheet is for overtime calculation and the fifth sheet is for the benefits calculation.

After all that you just need to complete the payroll and process it.

It Has Worked Examples for Each Employee Payroll

Access the template here:

Click Here to Download

So, keep this software in your computer and use it whenever you have to.

If you find this Excel template useful, then share this post with your friends.