How To Generate Payroll Summary Reports in Excel

You can easily analyze your payroll data in Excel:

1. In QuickBooks choose Reports, Employees & Payroll, and then Summarize Payroll Data in Excel.
2. Instructions will appear onscreen in Excel if you need to enable macros. Think of macros as custom programming embedded in an Excel workbook. QuickBooks ships with prebuilt Excel workbooks that contain the programming necessary to generate the Excel worksheets, but you must first instruct Excel to enable macros.
3. Once macros are enabled, the dialog box shown in Figure 1 will appear.

This dialog box allows you to determine what reports should be generated in Excel from QuickBooks.

4. Choose a time period, such as this Month, and then choose any of the optional reports.

The export from QuickBooks may take a couple of minutes, depending upon the size of your QuickBooks file – you’ll see onscreen progress indicators. As shown in Figure 2, a workbook with several reports will appear automatically. As discussed previously, you can double-click on any number within a pivot table-based report and view the underlying detail, as shown in Figure 3.

The Summarize Payroll Data in Excel feature creates a variety of reports with just a couple of mouse clicks.

Double-click on any number within a pivot table to view the underlying detail on a new worksheet.

How To Print One Employee Per Page

You can set a pivot table-based report, such as the Employee Journal, to print one employee per page:

1. Right-click on the Grand Total row, and choose Hide Row. Otherwise the grand total will appear on the last employee’s report.
2. In the case of the Employee Journal, right-click on the Transaction Name heading, and then choose Field Settings.
3. In Excel 2003 and earlier, click the Layout button, or in Excel 2007 click the Layout & Print tab. Choose Insert Page Break After Each Item, as shown in Figure 4.

Pivot table-based reports can be printed on a per-item or per-employee basis.

You can apply this technique to any of the pivot table-based reports.

How To Create Payroll Tax Forms Worksheets in Excel

This feature – if present in your version of QuickBooks – makes it easy to view the underlying detail for the tax forms that QuickBooks can generate for you. As you’re probably aware, it’s easy to print payroll tax forms:

1. Choose Employees, Payroll Tax Forms & W-2s, and then Process Payroll Forms.
2. Choose either Federal or State, and then click OK.
3. Choose a form from the resulting list, as shown in Figure 5, and then follow the onscreen prompts.

Most payroll tax forms can be printed directly from QuickBooks.

If you have questions about the numbers that appear on these reports, or you want to audit the figures, the Tax Form Worksheets in Excel provides the underlying detail with just a couple of mouse clicks.

1. Choose Reports, Employees & Payroll, and then Tax Form Worksheets in Excel.
2. After a moment an Excel workbook will appear onscreen. If a Welcome screen appears, follow the onscreen prompts to enable macros in Excel. Once macros are enabled, you’ll see the dialog box shown in Figure 6.

QuickBooks can show you the underlying detail for several payroll tax forms.

3. As you can see in Figure 6, you can generate one tax worksheet at a time:
   • Quarterly 941 – This worksheet summarizes the figures you need to complete your quarterly From 941, which you use to inform the Internal Revenue Service of the total income taxes withheld from employee paychecks, as well as the employee and employer share of Social Security and Medicare taxes.
   • Annual 944 – In certain instances the IRS will notify an employer in writing that Form 944 can be filed annually instead of filing Form 941 on a quarterly basis. Do not file Form 944 unless you receive instructions from the Internal Revenue Service.
   • Annual 940 – This worksheet provides the detail required to file your Federal Unemployment Tax Return.
   • Annual 943 – This worksheet provides the detail needed to compile the Employer’s Annual Federal Tax Return for Agricultural Employees.
   • Annual W2/W3 – This choice gives you the underlying detail for each employee’s W2 form, as well as the summary figures that make up your W3 form.

Download IRS Forms: All IRS forms are available for free download. For instance, you can download Form 941 at www.irs.gov/pub/irs-pdf/f941.pdf. Simply replace 941 with the corresponding form number in the preceding Internet address.

• State SUI Wage Listing – This form provides the details behind your State Umployment Insurance (SUI) form.

Once you choose a tax form, choose a report period from the list, or enter the dates of your choice.

4. The Options/Settings button displays the dialog box shown in Figure 7, which lets you fine-tune the results provided by QuickBooks:

Most users won’t find it necessary to do so, but you can refine how QuickBooks generates the tax forms and payroll summary worksheets.

• By default QuickBooks lists the company name and report dates in the page headers of your Excel worksheet. You won’t typically see these onscreen, but you will when you choose to print the worksheet, or display it in Print Preview mode.
• Hide Detailed Data Returned from QuickBooks – depending upon the tax form you choose, you may see more or less detail onscreen. Typically you’ll want to leave this choice selected.

How To Change Report Options

You don’t have to return to QuickBooks if you decide that you want to generate a different tax form worksheet, or perhaps change the report dates. The steps differ slightly, depending upon your Excel version:

• Excel 2003 or earlier: Choose Get QuickBooks Data or Update Tax Worksheet from the floating QuickBooks Link or QuickBooks Tax Link toolbars, respectively. These toolbars are easy to restore if you inadvertently close them: right-click on any of your Excel toolbars, and then choose QuickBooks link or QuickBooks Tax link.
• Excel 2007 – Click on the Add-Ins tab of the ribbon, choose QB Payroll Summary Reports in the Custom Toolbars section, and then choose either Get QuickBooks Data to update the payroll summary, or Update Tax Worksheet to update a tax form.

Note that if you simply change the dates for the payroll summary or a tax form, your existing worksheet will be overwritten. However, if you choose a different tax form, an additional worksheet will appear within your workbook.