cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Intuit

Excel based payroll reports

Below are information about several Excel reports available in QuickBooks and how to generate them:

  • Summarize Payroll Data in Excel
  • Payroll Summary by Tax Tracking Type
  • Employee Time & Costs
  • Employee Sick & Vacation History
  • Employee Direct Deposit Listing
  • Tax Form Worksheets
  • Certified Payroll Report
  • Payroll Liability Accruals & Payments
  • Deferred Compensation Report for 401(k) (& more)
  • Local Tax Summary
  • Payroll by Department/Class

Summarize Payroll Data in Excel

If you need information from the State Quarterly Wage List prior to receiving the report in the Payroll Tax Center, you can access the information through your QuickBooks software if you use QuickBooks Pro or above. From QuickBooks, generate the Summarize Payroll Data in Excel report. The State Wage Listing section of the report contains the same information that Assisted Payroll sends to you.

  1. Under Reports from the QuickBooks menu, choose Employees & Payroll, then select Summarize Payroll Data in Excel.
  2. If prompted by the Excel security warning, follow the directions to enable macros in Excel.
  3. From the QuickBooks Payroll Workbook - Welcome screen, select OK.
  4. Enter a date range, then select Get QuickBooks Data.
  5. Select the State Wage Listing tab at the bottom.

In addition to the availability of the State Wage Listing, several new data fields are available to be added to Pivot Tables in Excel. These include:

  • Pay Type for earnings items, such as Regular and Overtime, Sick and Vacation
  • Rate Multiplier for overtime items, this is the multiplier you see when setting up the item. You can now calculate the base & overtime portions of overtime pay when using these excel reports with customization
  • Pay Period Dates: For each paycheck the Pay Period Begin and Pay Period End dates are now included in the detailed data. They can be added to any Pivot-Table based report.

Payroll Summary by Tax Tracking Type

This report groups payroll data by the tax tracking type first and then also shows the payroll item detail within the type. This report can be useful to see a more summarized version of the payroll data or to trouble-shoot payroll item setup.

  1. Select Reports at the top menu bar > Employees & Payroll > More Payroll Reports in Excel > Payroll Summary by Tax Tracking Type
  2. If prompted by the Excel security warning, follow the directions to enable macros in Excel.
  3. Select the dates, then click Get QuickBooks Data.

Employee Sick & Vacation History

This report gathers the accrued and used sick and vacation values for each paycheck for each employee over a period and creates a table of this history. Additionally, an estimate of the historic sick and vacation balance is calculated, working back from present balances because QuickBooks doesn't keep historic balances. The estimate is accurate where no manual adjustments have been made.

  1. Select Reports at the top menu bar > Employees & Payroll > More Payroll Reports in Excel > Employee Sick & Vacation History.
  2. If prompted by the Excel security warning, saying macros are not enabled in Excel, follow the directions to enable macros for your version of Excel.
  3. Select the dates, click Get QuickBooks Data.

Each employee with activity in the period is given a separate page break in the report.

Employee Direct Deposit Listing

This report displays the Direct Deposit setup by employee. It shows which employees are setup and the bank information for each employee.

  1. Select Reports at the top menu bar > Employees & Payroll > More Payroll Reports in Excel > Employee Direct Deposit Listing.
  2. If prompted by the Excel security warning, saying macros are not enabled in Excel, follow the directions to enable macros for your version of Excel.
  3. Click Get QuickBooks Data.

Tax Form Worksheets

You can QuickZoom on the numbers on each tax form, which will produce a new worksheet/report with all the detailed transactions that make up that number.

  1. Select Reports at the top menu bar > Employees & Payroll > More Payroll Reports in Excel > Tax Form Worksheets.
  2. If prompted by the Excel security warning, saying macros are not enabled in Excel, follow the directions to enable macros for your version of Excel.
  3. Choose the type of tax worksheet you want to create and the dates, click Create Report.

Certified Payroll Report

See Certified Payroll for information about how to create Certified Payroll Reports in QuickBooks Desktop.

Due to Microsoft Excel 2003, 2007, 2010 and 2013 limits, this Excel report export feature has a limit of 65,536 detail lines.

If you exceed this limit you would have to upgrade to Excel 2015 or 2016 and try the following workaround

  1. You must have Excel 2015 or 2016 for this to work because they support more than 65,536 detail lines.
  2. In your QuickBooks Desktop file, run a report such as the Create State SUI EFile.
  3. Before getting the QuickBooks Desktop data in Excel, change the settings using the Create State SUI EFile.
    1. State = State filing for
    2. Filing year = Future date
    3. Quarter = Quarter filing for
  4. Go to the generated excel and Save As. Choose the type as Excel Macro-enabled Workbook (xlsm).
  5. Open the saved xlsm file and enable the macro contents by clicking on the Enable Content.
  6. Go to ADD-IN menu, then select QuickBooks Payroll State SUI E-File drop down or the Excel Report you are trying to run.
  7. Choose Refresh State Data.
  8. We will again get the option of getting the QB data. Choose the following settings:
    1. State = State filing for
    2. Filing Year = Correct Date
    3. Quarter = Correct Quarter
  9. This time it should fetch all the data.

Was this helpful?

You must sign in to vote, reply, or post