How To Create A Payroll Database In Excel

Download Article

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 Windows and Mac computers.

1
2
Click Download . It's a blue button near the bottom of the window. The template will begin downloading onto your computer.
- Depending on your browser, you may first have to enter a save location and click Save before the file will download.
3

Open the template. Double-click the downloaded Excel template file to open it in Excel.
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.
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.

1

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

Add an employee's name. Type your employee's name into the first blank cell in the "Name" column.
3

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.
4
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:
- Tax Status - A number (usually "1") indicated on the employee's W-2.
- Federal Allowance - A number that determines an employee's tax bracket. Usually found on the W-4.
- State Tax (Percentage) - Your state's income tax percentage.
- Federal Income Tax (Percentage) - The employee's tax bracket's income tax percentage.
- Social Security Tax (Percentage) - The current social security tax percentage.
- Medicare Tax (Percentage) - The current medicare tax percentage.
- Total Taxes Withheld (Percentage) - This field will automatically be calculated once you've filled in the other tax fields.
5
Determine your employee's deductions. This will depend on things like your employee's benefits, investments, and so on:
- Insurance Deduction (Dollars) - The amount in dollars that you withhold for insurance.
- Other Regular Deduction (Dollars) - Any other amount that you withhold.
6

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

1

Click the Payroll calculator tab. It's at the bottom of the page. This will open the calculator page.
2

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

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

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.
5
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.
- The overtime rate is usually 150 percent of the employee's usual rate (hence the phrase "time and a half").
6
Add any last-minute deductions. In the "Other Deduction" column, enter the dollar amount of deductions that fall outside of the normal deductions.
- For example, if your employee took a deduction to pay for equipment, you'd enter it here as a one-time payment.
7
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.
- You can also check the pre-taxes pay in the "Gross Pay" column.
8
Calculate your other employees' payroll. For each employee listed in the "Employee Name" field, fill out the calculator to determine their take-home pay.
- 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.

Add New Question

Question

How can I see a correct print preview in Excel?

Click the office button and select print from the drop down list, then select print preview.
Question

Can I change the template?

Yes, you can. Once you download the payroll template as instructed above, click "enable editing" and you will be able to edit your template.
Question

What does it mean if a cell says #VALUE! in the calculator?

When the value you want to calculate is non-numeric, it shows #VALUE!

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Video

Once you've finished a pay period's payroll, you should save a copy of the payroll spreadsheet before wiping the payroll calculator and creating a new one.

Don't delete any of the pre-formatted cells (e.g., cells that have formulas in them), as doing so will result in errors for the payroll calculator.

About This Article

Article SummaryX

1. Go to https://templates.office.com/en-us/Payroll-calculator-TM06101177
2. Click Download
3. Open the template in Microsoft Excel.
4. Add your employees' information on the Employee information tab.
5. Determine your employees' payroll on the Payroll calculator tab.

Did this summary help you?

Thanks to all authors for creating a page that has been read 530,664 times.