Skip to main content
K
Kurt_qc9
February 11, 2023
Solved

Workaround for Excel 65,000 row Export limit?

  • February 11, 2023
  • 2 replies
  • 0 views

I have found that annual payroll forms data fails due to both the payroll size and the number of data rows in excess of 70,000. ...What is the work around to the 65,000 row limit that causes "Summarize payroll data in Excel" to fail?

Is there a way to specify the Microsoft365 macro Enabled workbook template instead of the default?

Best answer by Archie_B

Good day,Kurt_qc9. I'm here to share some information about exporting payroll data and help you get back on track.

 

The Excel report export feature has a limit of 65,536 detail lines due to Microsoft Excel 2003, 2007, 2010, and 2013 limitations.

 

If you exceed this limit, you may need to upgrade to Excel 2015 or 2016, which supports more than 65,536 detail lines. Once you upgrade, you can follow the steps below as a workaround.

 

Like this:

 

  1. In your QuickBooks Desktop company file, run a report such as the Create State SUI EFile.
  2. Change the settings using the Create State SUI EFile before getting the QuickBooks Desktop data in Excel.
    a. State = State filing for
    b. Filing year = Future date
    c. Quarter = Quarter filing for
  3. Go to the generated excel and Save As. Choose the type as Excel Macro-enabled Workbook (xlsm).
  4. Open the saved xlsm file and enable the macro contents by clicking on Enable Content.
  5. Go to ADD-IN menu, then select QuickBooks Payroll State SUI E-File drop down or the Excel Report you are trying to run.
  6. Click on Refresh State Data.
  7. We will again get the option of getting the QB data. Choose the following settings:
    a. State = State filing for
    b. Filing Year = Correct Date
    c. Quarter = Correct Quarter

 

After that, you should be able to fetch all the payroll data. For more information about Excel based reports available in QuickBooks, you can visit this link: Excel based payroll reports.

 

I'm also adding this article to learn how you can customize payroll reports to get more information about your payroll and employee expenses: Customize payroll and employee reports.

 

You're always welcome to post here in the Community or leave a comment in this thread if you have other payroll or reporting concerns. I'll be here to assist. Have a wonderful day!

2 replies

A
Archie_BAnswer
February 11, 2023

Good day,Kurt_qc9. I'm here to share some information about exporting payroll data and help you get back on track.

 

The Excel report export feature has a limit of 65,536 detail lines due to Microsoft Excel 2003, 2007, 2010, and 2013 limitations.

 

If you exceed this limit, you may need to upgrade to Excel 2015 or 2016, which supports more than 65,536 detail lines. Once you upgrade, you can follow the steps below as a workaround.

 

Like this:

 

  1. In your QuickBooks Desktop company file, run a report such as the Create State SUI EFile.
  2. Change the settings using the Create State SUI EFile before getting the QuickBooks Desktop data in Excel.
    a. State = State filing for
    b. Filing year = Future date
    c. Quarter = Quarter filing for
  3. Go to the generated excel and Save As. Choose the type as Excel Macro-enabled Workbook (xlsm).
  4. Open the saved xlsm file and enable the macro contents by clicking on Enable Content.
  5. Go to ADD-IN menu, then select QuickBooks Payroll State SUI E-File drop down or the Excel Report you are trying to run.
  6. Click on Refresh State Data.
  7. We will again get the option of getting the QB data. Choose the following settings:
    a. State = State filing for
    b. Filing Year = Correct Date
    c. Quarter = Correct Quarter

 

After that, you should be able to fetch all the payroll data. For more information about Excel based reports available in QuickBooks, you can visit this link: Excel based payroll reports.

 

I'm also adding this article to learn how you can customize payroll reports to get more information about your payroll and employee expenses: Customize payroll and employee reports.

 

You're always welcome to post here in the Community or leave a comment in this thread if you have other payroll or reporting concerns. I'll be here to assist. Have a wonderful day!

    K
    Kurt_qc9Author
    February 15, 2023

    Thank you Archie.  The procedure worked for me as described.  When I returned to the workbook after saving it as a macro-enabled microsoft365 Excel workbook it accepted the additional rows.

      KlentB
      KlentB
      February 15, 2023

      On behalf of my colleague, Archie_B, you're welcome, Kurt_qc9.

       

      I'm glad that you're now able to fetch all the payroll data. The QuickBooks Community will always be around to lend you a hand any time you need guidance.

       

      Let me know if there is anything else I can help you with. I hope you have a great day!

      BigRedConsulting
      BigRedConsulting
      February 13, 2023

      @Kurt_qc9  RE: Is there a way to specify the Microsoft365 macro Enabled workbook template instead of the default?

       

      Not on launch, but if after launch you save the workbook as an *.XLSM file, and then get data from QuickBooks again, you will no longer have the 65k limitation because the worksheet sizes will be extended to that format's limit, 1M rows.

      Terms & ConditionsAccessibility statement