Was this page helpful?
Thank you!

Comments or suggestions?



Enter Email Address (optional)
email

Summarize payroll data in Microsoft Excel

QuickBooks includes an Excel workbook containing worksheets designed to collect and organize data required by most state tax agencies to file your payroll tax forms. The workbook also contains worksheets for federal tax forms.

Important: You must have Microsoft Excel 2000, 2002, 2003, 2007, or 2010 installed on your computer to use this feature.

The workbook includes several worksheets, many of which are preset with an Excel PivotTable. You can use each worksheet as designed or modify them to suit your reporting needs.

Go onlineWorksheets in the Microsoft Excel Workbook

Summarize payroll data in Microsoft Excel

To do this task

  1. Go to the Reports menu, choose Employees & Payroll, and then click Summarize Payroll Data in Excel.

    You can also summarize payroll data from the Employee Center. With the Transactions tab displayed in the Center, click the Excel drop-down arrow and then click Summarize Payroll Data in Excel.

  2. If necessary, follow the prompts to enable macros in Excel. The QuickBooks Payroll Reports Workbook window appears after you enable macros.

  3. Enter a date range and uncheck any optional worksheets you want to exclude from the QuickBooks Payroll Reports Workbook.

  4. Clear or select the report checkboxes in the Optional Reports section to exclude or include different reports in the workbook.

    By default, the workbook includes all of the reports defined for the QuickBooks Payroll Reports Workbook.

    You can customize the workbook and get only the payroll data you need to complete your state payroll tax forms. To do so, clear or select the appropriate reports in the Optional Reports area. You can always modify your selections later.

  5. Customize your workbook settings, if necessary.

    1. Click Options/Settings.

    2. Select or clear the options that you want applied to the workbook. By default, the most commonly used settings are selected.

    3. Click OK when complete.

    You can make changes to these settings any time you get or refresh QuickBooks data.

  6. Click Get QuickBooks Data.

    The workbook retrieves payroll data from QuickBooks and displays it in the appropriate places in the various worksheets.

  7. Click the tabs at the bottom of the workbook to display different reports.

Save a QuickBooks Payroll Workbook

You can save a QuickBooks Payroll Workbook so that you don't have to re-customize it every time you want to use it. The links between the workbook and QuickBooks let you load payroll data directly into your memorized workbook, so you can always work with the data you need at the time.

You can customize many of the reports in the workbook, change sort orders, or add and remove columns of data. To change the reports, see Excel's help for pivot tables and pivot reports. Once you make your changes, you can save the workbook and use it later from Excel, refreshing your data in the workbook once it has changed in QuickBooks. To save a workbook:

To do this task

  1. In Excel, go to the File menu and click Save As.

  2. Choose a location on your computer to store the workbook, give it a name, and then click Save.

Re-open your QuickBooks Payroll Workbook and load payroll data

To do this task

  1. Make sure QuickBooks is running and the appropriate company file is open.

  2. Find your workbook in the place you saved it and open it.

  3. If necessary, follow the prompts to enable macros in Excel.

  4. Open the QuickBooks Link window.

  5. Choose your date preferences and click Get QuickBooks Data.
    The workbook retrieves payroll data from QuickBooks and displays it in the appropriate places in the various worksheets.

See also

KB ID# H_RPT_EXCEL_RPTS_SUMMARIZE
12/7/2016 4:08:51 PM
PPRDQSSWS403 9138 Pro 2017 c221f5