Calculating payroll can be a time-consuming endeavor, so automating as much of the process as possible will save you hundreds of hours by the end of the year. Fortunately, Microsoft Excel comes with a free Payroll Calculator workbook, which you can customize for your business.
It includes three worksheets: The first is for your employee information; the second is a payroll calculator; the third is a series of pay stubs for each employee.
You can download the Payroll Calculator template from the Microsoft Office website, templates.office.com, or by opening it from inside Excel. If you download it from the website, double-click it after it appears in your downloads folder and Excel will open it automatically.
Advertisement Article continues below this adTo open the Payroll Calculator template from Excel, click the "File" tab in Excel; click "New" and type "payroll calculator" in the Search field. Select the Payroll Calculator template and click "Create."
Once the template is open, click the "Enable" button at the top of the worksheet so you can make changes to the file. Click the "File" tab, select "Save As" and then rename the file to identify it as the payroll for your current week, such as "Payroll-January-1-7.xlsx."
Click the "Employee Information" tab at the bottom of the workbook to open the worksheet. The template has one sample employee entered. There are columns for each employee's name, ID number, hourly wage and tax deduction percentages.
Advertisement Article continues below this adEnter your employees' information in this worksheet, replacing the sample name with one of your own employees. Employee numbers begin with the number 1. You can replace these numbers with your own numbers if needed, but you will also have to replace the employee numbers on the other two worksheets too. All of the employee information in each worksheet is tied to each individual's employee number.
Open the Payroll Calculator worksheet. The employee names are entered in this worksheet automatically, based on their names and ID numbers from the first worksheet.
If you didn't make any changes to the Employee ID numbers, each employee's name will appear in the second column. If you did change the employee numbers, highlight them in the Employee Information worksheet, and copy them by pressing Ctrl-C. Click cell B4 in the Payroll Calculator worksheet and press Ctrl-V to paste the copied numbers into the column. The employee names corresponding to each ID number will now appear in the second column.
Advertisement Article continues below this adIn this worksheet, you can enter the regular hours worked, vacation hours, sick hours and overtime hours. Enter only the hours worked. If the employee didn't take vacation, for example, leave that cell blank.
Regular pay for the week is based on the hours you entered in the Payroll Calculator worksheet, multiplied by the rate of pay from the Employee Information worksheet.
If the employee worked overtime, you have to enter the overtime rate in column H. If the normal rate is $20 per hour and you pay time-and-a-half for overtime, then this would be $30. The worksheet automatically multiplies the overtime and overtime rate together.
Taxes and other deductions from the Employee Information worksheet are automatically subtracted from the Gross Pay to determine the Net Pay. Note that if you have an additional one-time deduction for the employee, you can enter it in the Other Deductions column. It will also be deducted from the Gross Pay.
Advertisement Article continues below this adThe third worksheet contains individual pay stubs for each employee that you can print out for them. Except for your company name at the top of each stub, all of the information on the stubs is populated from the other two worksheets.
There are only four pay stubs included in the worksheet, but you can add as many as needed for your current number of employees. Each pay stub occupies 12 rows, including a spacer row at the top.
Click the row number "1" at the far right of the worksheet and drag the cursor down to row number "12." Press Ctrl-C to copy the rows.
Advertisement Article continues below this adClick row number "49" to highlight that row and press "Ctrl-V" to paste the copied rows into the worksheet. Click the next blank row number and press "Ctrl-V" again to create a sixth pay stub. Repeat this as often as needed.
Change the Employee ID number in the upper right corner of each pay stub on the new stubs you added to the worksheet. As soon as the number is changed, that employee's information will automatically appear in that pay stub.