How to Prepare Payroll in Excel

This wikiHow teaches you how to calculate payroll for your employees in Microsoft Excel. Creating a payroll calculator from scratch is an incredibly tedious process, but Microsoft has a free payroll calculator template for Excel on both...

Part 1 of 3:

Creating the Payroll Calculator

How to Prepare Payroll in Excel Picture 1
Open the Payroll Calculator website. Go to https://templates.office.com/en-us/Payroll-calculator-TM06101177 in your computer's web browser.
1. This calculator is a free Excel template from Microsoft.
How to Prepare Payroll in Excel Picture 2
Click Download. It's a blue button near the bottom of the window. The template will begin downloading onto your computer.
1. Depending on your browser, you may first have to enter a save location and click Save before the file will download.
How to Prepare Payroll in Excel Picture 3

Open the template. Double-click the downloaded Excel template file to open it in Excel.
How to Prepare Payroll in Excel Picture 4

Click Enable Editing. This button is in the yellow bar at the top of the Excel window. Doing so will unlock the Excel file for editing.
How to Prepare Payroll in Excel Picture 5

Save your document. Before you edit the template any further, press Ctrl+S (Windows) or ⌘ Command+S (Mac), enter your file's name (e.g., "Payroll 5.12.2018"), and click Save. This ensures that your payroll sheet will automatically be saved as a separate file. At this point, you can begin calculating payroll.

Part 2 of 3:

Entering Employee Information

How to Prepare Payroll in Excel Picture 6

Click Employee information. It's in the bottom-left corner of the Excel window. This will ensure that you're on the Employee Information sheet.
How to Prepare Payroll in Excel Picture 7

Add an employee's name. Type your employee's name into the first blank cell in the "Name" column.
How to Prepare Payroll in Excel Picture 8

Enter the employee's hourly wage. Type the dollar amount that your employee makes per hour into the first blank cell in the "Hourly Wage" column.
How to Prepare Payroll in Excel Picture 9
Enter the employee's tax information. Make sure that you have your employee's tax information on-hand, then fill out the cells below the following headings:
1. Tax Status - A number (usually "1") indicated on the employee's W-2.
2. Federal Allowance - A number that determines an employee's tax bracket. Usually found on the W-4.
3. State Tax (Percentage) - Your state's income tax percentage.
4. Federal Income Tax (Percentage) - The employee's tax bracket's income tax percentage.
5. Social Security Tax (Percentage) - The current social security tax percentage.
6. Medicare Tax (Percentage) - The current medicare tax percentage.
7. Total Taxes Withheld (Percentage) - This field will automatically be calculated once you've filled in the other tax fields.
How to Prepare Payroll in Excel Picture 10
Determine your employee's deductions. This will depend on things like your employee's benefits, investments, and so on:
1. Insurance Deduction (Dollars) - The amount in dollars that you withhold for insurance.
2. Other Regular Deduction (Dollars) - Any other amount that you withhold.
How to Prepare Payroll in Excel Picture 11

Add your other employees' information. Once you've added a row of information for every employee, you can proceed to calculating payroll.

Part 3 of 3:

Calculating Payroll

How to Prepare Payroll in Excel Picture 12

Click the Payroll calculator tab. It's at the bottom of the page. This will open the calculator page.
How to Prepare Payroll in Excel Picture 13

Find an employee. Locate the first employee whose information you entered on the Employee Information page. Their name should be at the top of this page.
How to Prepare Payroll in Excel Picture 14

Enter the number of hours worked. In the "Regular Hours Worked" column, type in the number of hours that the employee worked (e.g., 40) in the given pay period.
How to Prepare Payroll in Excel Picture 15

Add vacation and sick hours if necessary. If your employee used any vacation time or sick time, note the number of hours in the "Vacation Hours" or "Sick Hours" column, respectively.
How to Prepare Payroll in Excel Picture 16
Enter overtime hours and rate. If your employee worked any overtime (e.g., time over 40 hours in a week), enter the number of hours worked in the "Overtime Hours" column, then enter the overtime rate (in dollars) in the "Overtime Rate" column.
1. The overtime rate is usually 150 percent of the employee's usual rate (hence the phrase "time and a half").
How to Prepare Payroll in Excel Picture 17
Add any last-minute deductions. In the "Other Deduction" column, enter the dollar amount of deductions that fall outside of the normal deductions.
1. For example, if your employee took a deduction to pay for equipment, you'd enter it here as a one-time payment.
How to Prepare Payroll in Excel Picture 18
Review the employee's net pay. In the "Net Pay" column, you'll be able to see how much your employee's take-home is; if this number looks correct, you're done calculating payroll for that employee.
1. You can also check the pre-taxes pay in the "Gross Pay" column.
How to Prepare Payroll in Excel Picture 19
Calculate your other employees' payroll. For each employee listed in the "Employee Name" field, fill out the calculator to determine their take-home pay.
1. You can check your employees' pay stubs in the PAYROLL PAYSTUBS or Individual Paystubs tab at the bottom of the page once you're done.

employee tax employee's payroll

David Pac

Update 05 March 2020

You should read it

Maybe you are interested