Along with paying employees for services rendered and withholding applicable taxes, employers must also keep track of employees' time. Regardless of the company's size, the employer must establish a timekeeping system, according to the U.S. Department of Labor; the type of system is up to the employer and is permissible as long as it is accurate and complete. If your payroll is relatively small, using Microsoft Excel is one of the best ways to maintain payroll data for yourself and your workers.
- Skill level:
- Moderately Challenging
Open a new spreadsheet in Microsoft Excel. Ensure that you design the workbook to reflect payroll data for the entire year. At the start of each year, create a new spreadsheet.
Create headings for your columns. The most important columns for payroll record-keeping are employee name, pay period end date, regular hours, overtime hours, statutory deductions such as taxes and child support, voluntary deductions such as health benefits and 401k contributions, gross pay and net pay.
Enter the payroll information under each heading. You should be finished with payroll processing at least two days prior to each pay date. After payroll processing, enter the payroll data from either the payroll reports or the actual paychecks, under the appropriate spreadsheet headings.
Create formulas for each column. Ensure each column has a total, which should be reflected once you have entered the formula and the pay period information. Enter the formula for the column in the formula bar. For instance, if the "net pay" heading falls under the P column and includes rows 2 to 15, your formula bar should reflect =SUM(P2:P15).
Devise a spreadsheet for personnel reasons. To save you from having to access hard copy personnel records or from having to log in to the payroll system, create a workbook for this purpose only. The spreadsheet headings may include employee name, ID number and department (if applicable), birthday, Social Security number, address and telephone number.
Tips and warnings
- You can have the system calculate the total taxes withheld if you are using Microsoft Excel 2007. Enter "Payroll" in the "Search Microsoft Office Online for a Template" text box to access the Payroll Calculator template. Enter relevant data such as employee name, hourly pay, tax status and tax percentages, and insurance deductions. Based on the information you enter, the system will calculate the total taxes withheld.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for