How to Do Payroll in Excel
Microsoft offers a variety of templates for streamlining common tasks in Excel 2013. In the case of payroll management, pre-designed templates offer the formatting you need and allow you to enter payroll data like hourly wages and hours worked. Integrated formulae then automatically calculate gross pay and even print pay stubs for employees. You'll have to do a bit of research into your local and state income taxes, but, once you input the data into your template, Excel provides a valuable tool for staying on top of payroll and producing regular accounting records.
Click on the "File" tab in Excel and then select "New."
Search for a payroll template by typing "payroll" into the search box at the top of the new window (see Resources).
Browse through the available templates to find the one that best suits your needs. Click on a template to see a preview screen with additional information on the template and click on the "Create" button to load a template into Excel for editing.
Edit the title information by clicking on a cell and typing in your company name or any necessary subtitles. Many templates include placeholder text for these items. Just click on the cell and type in your specific information.
Review the template, remove unnecessary columns or rows and add any new categories, such as vacation time accumulated or bonus payments, you might need. Remove cells, columns and rows by selecting them and pressing the "Delete" key. Add cells, columns or rows by right-clicking next to where you want to add the element and selecting "Insert."
Research your state and local income tax information. The Federation of Tax Administrators provides a complete list of such taxes (see Resources). Add these columns to your spreadsheet if necessary or edit the formulae in the pre-designed local tax columns to reflect local tax rates. In Excel, percentages are calculated by multiplying the decimal rate by the cell containing gross pay. If, for instance, gross pay is in column C and local tax rates are 7.5 percent, the appropriate formula would be "=C1* 0.075" in the first row. Highlight the cell and drag the right corner down the column to copy the formula to the entire column with the appropriate row numbers. Be sure that this cell is subtracted from the net pay total at the end of the worksheet.
Enter all of your employee names or identification numbers into the worksheet.
Enter all of the hourly wages for your employees. If your employees work for a fixed salary, remove columns for hourly wage or hours worked. If your employees are paid per unit of production, edit the headings to reflect this fact. Simply input units of production in the hours worked column and per unit pay in the hourly wage column. Leave the formula as is.
Save your work at the end of every pay period. If your template generates pay stubs, click on the "Generate Pay Stubs" button to create and print records for your employees.
Tips & Warnings
- Federal tax rates are already included in most templates. Try to pick a worksheet with these considerations already included to avoid extra work.
- Payroll information can be sensitive. To protect your data, click on the "File" tab and select "Info" to browse options to password-protect your workbook or specify the network users who can access the worksheet.
References & Resources
- University of Chicago: Office 2013, Encrypting Files With Password Protection
- Office Support: Calculate Percentages
- Office Support: Insert or Delete Cells, Rows and Columns
- Learn Free: Using Templates
- Office Templates: Payroll Calculator
- Office Templates: Payroll Calculator With Pay Stubs
- Federation of Tax Administrators: State Income Taxes