Quickbooks Desktop: Can’t “Summarize Payroll Data in Excel”
There has been an incredibly annoying issue between Office 365 and Quickbooks Desktop software – ongoing since January 2019 (this is absurd!) – wherein you cannot successfully export payroll data to Excel and then use the PivotTables to find data necessary for quarterly reports, etc.
This issue, as stated above, has been ongoing for an insanely long amount of time. In January, I researched the issue and found a workaround. I performed the workaround on all PCs in my office and on my client’s PCs. I then put the issue out of mind and got back to work. I posted this information on a Quickbooks support thread at the time. I am STILL getting updates on the thread that people are unable to get this bug sorted out, it is STILL a known issue and Quickbooks support is having people try clean installations – WHICH DOES NOT WORK to fix this issue.
The issue, as far as I can tell, is that Office 365 versions beyond v16.0.11029.20079 break the functionality to “Summarize Payroll Data in Excel.” Following the directions below, you will be able to “Summarize Payroll Data in Excel” successfully by rolling back your Office 365 installation to v16.0.11029.20079.
The following is a snippet from a Microsoft Support page detailing how to roll back Office 365. The important thing to note here is that you want to go back to v16.0.11029.20079 as that seems to be the latest version that works for summarizing the Quickbooks data in Excel.
Disclaimer: I am presenting this for informational purposes only. I have found it to work in restoring this functionality to Quickbooks / Office 365 Excel for my office and my client’s PCs. Proceed knowing I claim no responsibility for issues that may arise, even though I have yet to find any. We are essentially just rolling back to an older version of Office 365. No issues should come up, but we are knowingly going back to an outdated version of Office 365 (in order to make it work!) and that has it’s own security implications.
By default, Click-to-Run installations of Microsoft Office 2016 are configured to automatically update your Office installation when new updates are made publicly available. Occasionally, you may have to revert to an earlier version of an Office 2016 Click-to-Run installation. For example, an automatic update may introduce unexpected behavior by one of your Office applications. In this case, you may want to revert to the previous version until a new version is released that addresses the issue you experienced.
This article provides a list of recommended steps to revert to an earlier version of an Office 2016 Click-to-Run installation.
This section discusses some considerations that you should take into account before you revert to an earlier version of an Office Click-to-Run installation.
Consider the severity of the issue
Before you decide to revert to an earlier version of an Office Click-to-Run installation, you should weigh the severity of the issue that you’re experiencing against the benefits of having the security, performance, and reliability improvements that are included in the latest Click-to-Run update.
Revert to the previous version of Office Click-to-Run
Follow these steps to revert to the previous version of Office Click-to-Run.
Important The Office Click-to-Run installation is streamed from the Internet. Make sure that your connection to the Internet is reliable and persistent.
Step 1: Set a reminder to enable automatic updates at a future date
Before you revert an Office Click-to-Run installation, you should disable automatic updates. To make sure that you don’t miss future updates, set a reminder to manually check for future versions. When a new version becomes available, and if the new version doesn’t exhibit the same issue that you experienced earlier, you should re-enable automatic updates.
Create a reminder to re-enable automatic updates. You can do this in Microsoft Outlook, on Outlook.com, or by using another application that has a calendar.
For more information about Office Click-to-Run updates, see the following article:
Step 2: Install the previous version of Office Click-to-Run
To install the previous version of Office 2016 Click-to-Run, follow these steps:
- Determine and note the previous version number. Use the following Microsoft website to find the update version that is previous to the current version:
- Download and run the self-extracting executable file from the following Download Center link. This file contains the Office Deployment Tool executable (Setup.exe) and a sample configuration file (Configuration.xml):
- Start Notepad and copy the following XML. Then, save the file as Config.xml in the same file location as the Setup.exe file from step 2.<Configuration>
<Updates Enabled=”TRUE” TargetVersion=”16.0.11029.20079″ />
Note In the XML, 16.0.11029.20079 represents the full version number that you noted in step 1.
BCT Accounting note: I have updated the version number in the Config.xml data shown above to ease in your ability to create the file with the correct version needed for this fix.
- Open an elevated Command Prompt window. To do this, click Start, type cmd in the Start Search box, right-click cmd.exe, and then click Run as administrator. Switch to the file location for the Setup.exe and Config.xml files.
- Run the following command:setup.exe /configure config.xml
- Start an Office application (such as Excel), and then select File > Account.
- In the Product Information section, select Update Options > Update Now.
For more information, see the Delay receiving feature updates from Semi-Annual Channel for Office 365 ProPlus.
Note If you are prompted to activate Office again, enter your Microsoft account and password. This step does not add your computer to your account a second time.
Step 3: Disable automatic updates for Office Click-to-Run
This step is very important. The repair process re-enables automatic updates. To prevent the newest version of Office Click-to-Run from being automatically reinstalled, follow these steps:
- Start Outlook. BCT Accounting note: you can open any Office application to do this next step, I always use Excel for this step
- Click File, and then click Office Account.
- In the Product Information column, click Update Options, and then click Disable Updates.
That’s it! You should now be able to successfully “Summarize Payroll Data in Excel” using the Quickbooks Desktop product.