Export reports as Excel workbooks in QuickBooks Desktop

Learn how to export reports to Excel from QuickBooks Desktop.

Want to take your reports out of QuickBooks and analyze them in Microsoft Excel? You can export reports as Excel workbooks (.xlsx), CSV spreadsheets, or PDFs. We’ll show you how.

• Export a report as Excel in QuickBooks 
• Generate Excel-based payroll reports
• Get updated reports while working in Excel
• Enable Macro Settings in Excel
• Troubleshoot export errors

Export a report as Excel in QuickBooks

Any report in QuickBooks can be exported to Excel. 

QuickBooks Desktop for Windows

1. Go to Reports, then Reports Center.
2. Find and double-click the report you want to export.
3. Select the Excel ▼dropdown, then Create New Worksheet or Update Existing Worksheet. 
4. If you see a message that says your report has too many columns, select Advanced, then uncheck the Space between columns checkbox. Select OK.
5. When you’re ready to export, select OK.

QuickBooks Desktop for Mac

You can open the Excel workbooks with Apple Numbers v3.5 or Microsoft Excel 2016 or later, including Mac Office 365.

1. Go to Reports and find the report you want to export.
2. Select Export.
3. When the file opens, select File, then select Save As to save the file where you want it.

QuickBooks opens Excel workbooks with the application you set up. Here’s how to change it.

1. Right-click on the file and select Get Info.
2. From the Open With menu, select Microsoft Excel or Apple Numbers.
3. Select Change All.

You can set your report preferences to change the file type that QuickBooks exports (.sylk or .xlsx).

Generate Excel-based payroll reports

Excel-based payroll reports contain data that is only available in Excel. The following are the Excel-based payroll reports available in QuickBooks Desktop for Windows.

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

Create Payroll Reports in Excel

1. Go to Reports, then Employees & Payroll. Select More Payroll Reports in Excel.
2. Select the type or report you want to generate.
3. Enable Macros if prompted.
4. Select the dates, then Get QuickBooks Data or Create Report.

Create a Summarize Payroll Data report in Excel

The Summarize Payroll Data report has info about:

• Employee Journal by Check
• YTD reports
• Rates & Hours by Job
• State Wage Listing
• Deferred Compensation
• Quarterly Employee Summary
• 8846 Worksheet
• Effective Dates by Item & Employee

1. Go to Reports, then Employees & Payroll. Select Summarize Payroll Data in Excel.
2. Enable Macros if prompted.
3. To customize your workbook settings, select Options/Settings. Select or clear the checkboxes as needed, then OK.
4. Enter a date range, then select Get QuickBooks Data.

Get updated reports while working in Excel

If new transactions are entered or changed in QuickBooks after you've exported to Excel, you can update your report with the latest info.

1. Make sure your QuickBooks company file is open when you do this.
2. In Microsoft Excel, go to the QuickBooks tab. 
3. Select Update Report to get the latest version of the report. 

Enable Macro Settings in Excel

Macros are small programs that run inside Microsoft Excel. They allow you to automate repetitive tasks and perform complex calculations on your data without having to do it manually.

If there is a security warning and an Enable Content button

1. Select Enable Content.

If there isn't an Enable Content button

1. Select File, then Options.
2. Select Trust Center, then Trust Center Settings.
3. Select Macro Settings, then Disable all macros with notification.
4. Select OK twice to save the settings.
5. Close and re-launch the workbook from QuickBooks Desktop.

Troubleshoot export errors

If you get an error message “The Excel report export feature has a limit of 65,536 detail lines due to Microsoft Excel 2003, 2007, 2010, and 2013 limitations”, try these steps:

1. Upgrade your Excel to a newer version
2. Create an SUI file 
3. Save the SUI file as Excel Macro-enabled Workbook (xlsm).
4. Open the saved xlsm file and enable macro settings (anchor), then select the ADD-INs tab. 
5. From the QuickBooks Payroll State SUI E-File ▼dropdown, select Refresh State Data.
6. Select the following to get the data again from QuickBooks:
   • State = State filing for
   • Filing Year = Correct Date
   • Quarter = Correct Quarter
7. Select Get QuickBooks Data. It should fetch all the data this time.