OSI Newsletters: "Tax Form Worksheets in Excel" | Account Services & Bookkeeping Services









Download Our Brochure

Tax Form 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.

Figure 5: 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.

Figure 6: QuickBooks can show you the underlying detail for several payroll tax forms.
1. 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 worksheetprovides 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 reportperiod 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:

Figure 7: 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.

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.

Summary

In this article we discussed how integration between QuickBooks and Microsoft Excel can make quick work of analyzing payroll figures. Many of the underlying reports are pivot table-based, which offers drill-down and filtering capability, while others are static, worksheet-based reports. The Excel-based reports give you more options beyond the built-in reports within QuickBooks when you need to analyze or audit your payroll figures.