Although the Employees & Payroll reports menu in QuickBooks offers numerous reports that let you slice and dice your payroll data, you may sometimes yearn for more flexibility. When you do, the Summarize Payroll Data in Excel and Tax Form Worksheets in Excel features will transform your payroll data with just a couple of mouse clicks. The Summarize Payroll Data in Excel feature helps you review historical payroll transactions; while the Tax Form Worksheets in Excel lets you peer into the summary numbers that appear on the tax forms that you generate from QuickBooks.
Excel required: Note that you must have Microsoft Excel installed on your computer to use these features. If you don’t already have Microsoft Excel, you can download a free 60-day trial from www.trymicrosoftoffice.com.
Summarizing Payroll Data in Excel
This feature is available in QuickBooks 2004 and later, and enables you to generate numerous payroll reports in Excel with just a couple of mouse clicks. Keep in mind that the mix of reports that you see may vary, based on your version of QuickBooks. In addition, the Excel-based reports take two different formats:
- Pivot table-based: Excel’s pivot tables feature summarizes rows of data into a concise format. In this case the rows of data are in QuickBooks, so the resulting spreadsheet becomes an extension of QuickBooks. In general, pivot tables offer several special benefits:
- You can rearrange the pivot table by dragging and dropping fields
- You can double-click on any number within the pivot table to drill down to the underlying detail
- Certain fields in the pivot table include drop-down lists, from which you can exclude certain items or employees.
- You can set the pivot table to put a page break between each employee or item, which enables you to easily print a separate report to share with each person on your team.
We’ll explore some of these capabilities later in this article.
- Worksheet-based: The worksheet-based reports that QuickBooks generates are static in nature, meaning you can’t double-click on any numbers to view the underlying detail. These reports are similar in nature to reports that are generated when you use the Send to Excel feature to analyze any of QuickBooks built-in reports. You can, however, copy and paste portions of the reports into other workbooks, or modify the reports to meet your needs.
QuickBooks’ Summary Payroll Data Reports
The following payroll data reports appear in the 2007 and 2008 versions QuickBooks Pro or higher – other versions of QuickBooks might not include some of these standard reports:
- Employee Journal – This pivot table-based report lists payroll transactions by employee, including pay date, check number, and deductions.
- YTD Summary – This worksheet-based report summarizes compensation and withholding by employee. This report appears on a worksheet labeled YTD Summary 1.
- Hours – This pivot-table based report summarizes hours by employee, by type – this enables you to see regular, overtime vacation, and sick hours in a concise format.
- Rate & Hours by Job – This report summarizes payroll data on a job by job basis. Make a choice from the Customer:Job drop down at the top of the worksheet to see costs for a particular customer or job.
- State Wage Listing – This report breaks out state-assessed taxes, such as unemployment, so that you can see the wages, excess wages, wage base, and tax amount for the specified report period.
- Quarterly – This report provides summary level wage and tax data sorted by Social Security Number by quarter. If you choose to see payroll for an entire year, this report will include a column for each of the four quarters.
Depending upon your QuickBooks version, you may also be able to generate these reports:
- 8846 Worksheet – This worksheet helps employers calculate the tax credit that can be collected on employer taxes paid on certain types of employee tips.
- Effective Rates By Item – This pivot table-based report appears in a worksheet labeled Calculated %, and shows the percentage used to calculate various taxes, including Social Security, Medicare, and unemployment, as well as other payroll items such as disability and workers’ compensation.
- YTD Recap – Similar in nature to the YTD Summary, this worksheet-based report lists employee activity on a payroll item-basis. This report will appear on a worksheet labeled YTD Summary 2.
- Deferred Compensation – This report displays any compensation and deferred compensation by tax-tracking type, and is sorted by date.
- 943 Worksheet – This worksheet helps employers that are required to file Form 943, Employer’s Annual Tax Return for Agricultural Employees.