QuickBooks Tech Trends

QuickBooks Online Modernizes Excel Exports

Written by David Ringstrom

Until recently, QuickBooks Online users have been relegated to using an out-of-date document format when exporting reports to Excel. An April 2015 update to the product now enables QuickBooks Online users to export reports to the modern XLSX format instead of the obsolete XLS format. The distinction between these formats may appear subtle, but if you’re using Excel 2010 or later, certain key features are disabled when you open spreadsheets saved in the wrong file format. In this article I’ll also explain how to manage the Protected View prompt that you may encounter in Excel 2010 and later.

Exporting Reports From QuickBooks Online

I used the QuickBooks Online Edition sample company as the source for this article, in case you’d like to follow the steps shown in Figure 1:

  1. Choose Reports from the menu along the left-hand side of the screen.
  2. Click the Customize link under Profit and Loss.
  3. Set Sub Items to Collapse.
  4. Change the Columns setting to Months.
  5. Click the Export button at the bottom of the Settings window.
  6. For purposes of this article, choose Excel (XLS) from the drop-down menu. After you finish this article you’ll understand why going forward you’ll always want to use the Excel (XLSX) choice.

Exporting Reports From QuickBooks Online

Figure 1: QuickBooks Online reports can be exported to Excel with a few mouse clicks.

At this point, a file named Report1.xls (or something similar) should download to your computer. Depending upon your browser’s settings, you may have to click the file name to open it in Excel.

Managing Protected View

As shown in Figure 2, Excel 2010 and later displays a Protected View prompt that is intended to protect you from inadvertently opening a malware infected spreadsheet. You can look at, but not edit, files that have been opened in Protected View, which also disables ODBC connections, workbook links, and macros. Reports that you download from QuickBooks Online won’t have any of these features, so you have two choices:

  • Click the Enable Editing button each time you export a report from QuickBooks Online.
  • Disable the Protected View setting in Excel 2010 and later as shown in Figure 2:
    1. Click on the File menu.
    2. Select Info.
    3. Click Protected View Settings.
    4. Uncheck the Enable Protected View for Files Originating From the Internet box. While you’re here you can consider whether to uncheck the Enable Protected View for Outlook Attachments as well.
    5. Click OK to close the Protected View settings.
    6. Click Enable Editing to edit your document.

QuickBooks Online Managing Protected View

Figure 2: You can disable Protected View for all documents if you choose.

Going forward, you’ll be able to open reports downloaded from QuickBooks Online, with the caveat that you’re assuming the risk of being careful about opening files that you may be downloading elsewhere from the Internet. Of course, you can always force a file to open in Protected View in Excel 2010 and later, as shown in Figure 3:

  1. Click once on any file that you wish to open.
  2. Click the arrow on the right-hand side of the Open button.
  3. Choose Open in Protected View.

This allows you to review files of unknown provenance before enabling any automatic links or automation within the workbook.

QuickBooks Online Excel Import

Figure 3: You can manually use Protected View when necessary.

The Risks Presented by XLS Files

Once you’ve opened the Profit and Loss report in Excel, let’s color-code one cell and then save the workbook:

  1. Select cell A7, which contains the words Design Income.
  2. Click the arrow next to the Fill Color command.
  3. Choose any color from the Theme Colors For this exercise do not choose any of the Standard Colors.
  4. Click the Save button on Excel’s Quick Access Toolbar or press Ctrl-S.
  5. A Compatibility Checker window will now appear every time you save this document because you’ve introduced a feature that isn’t fully-compatible with Excel 2003 and earlier. In this case you can uncheck the Check Compatibility When Saving This File box to avoid the prompt entirely. However, do note that if you save your documents in the XLS format, you’ll continue to hit this speedbump, as you must uncheck this box for each individual XLS workbook that you access in the future.
  6. Click Continue to dismiss the Compatibility Checker window.

QuickBooks Online Compatibility Checker

Figure 4: You can disable the Compatibility Checker window to avoid the prompt when you’ve used a feature not fully compatible with Excel 2003 and earlier.

Although the Compatibility Check can be annoying, at least it’s an in-your-face side effect. The XLS file format can pose much more subtle issues for Excel users. Let’s first look at the Sparklines feature available in Excel 2010 and later. For the uninitiated, Sparklines are tiny charts that fit within worksheet cells so that you can get a sense of trends within your data. Let’s say that you’d like to chart your income accounts in this fashion:

  1. Select cells H7:H13.
  2. Click on the Insert tab in Excel’s ribbon interface.
  3. Select Line, Column, or Win/Loss from the Sparklines Oh, wait—you can’t! That’s because Sparklines are only compatible with the XLSX document format.

Figure 5

Figure 5: Certain features, such as Sparklines in Excel 2010 and later, are disabled within XLS workbooks.

Since we’ve been stopped in our tracks, let’s create a pie chart instead:

  1. Select cells A7:A13, and then hold down the Ctrl key while you select cells G7:G13.
  2. Click on the Insert tab in Excel’s ribbon interface.
  3. Click on the Pie Chart icon.
  4. Choose the 2D Pie Chart.
  5. Notice that Discounts Given is a negative amount, which is slightly skewing our pie chart. In Excel 2013 we have a new Chart Filters button that appears when the chart is activated. We’re stymied again by the XLS format, which disables the button that would allow us to remove Discounts Given from our chart.

Sparklines in Excel

Figure 6: You cannot change the chart style or filter charts within XLS workbooks in Excel 2013.

These are just two situations that I’ve encountered when working with XLS workbooks in Excel 2010 and later. Fortunately you can easily head these issues off at the pass:

  • Always choose the Excel (XLSX) format when exporting reports from QuickBooks Online.
  • Convert XLS workbooks that you encounter to the modern XLSX format as shown in Figure 6:
    1. Choose File.
    2. On the Info tab, click the Convert button.
    3. Click OK on the warning prompt about converting workbooks (ideally check the Do Not Ask Me Again About Converting Workbooks box before you do so).
    4. Click Yes when asked if you want to close and reopen to workbook now. If you choose No here, you’ll remain in compatibility mode, which means the features you wish to use will remain disabled until you close and reopen the workbook.

Converting XLS workbooks to the XLSX format

Figure 7: Converting XLS workbooks to the XLSX format will enable all disabled features in Excel.

Note that the Convert command is far superior to using the Save As command to make an XLSX version of an XLS workbook. The Convert command replaces the XLS file with an XLSX file, while Save As will result in you having two copies of the same document, which sets the stage for confusion and potentially updating the wrong version of a document. Once you’ve converted your report you’ll be free to create Sparklines with abandon, as well as filter items from charts if you’re using Excel 2013.

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”

12 Comments

  • Great news!
    The service is systematically growing and becoming more and more useful for users!
    There are many report in XLS that I have to convert.
    What can you advice me to do this work as easy as possible?

    • Leonid,

      In my case I’d create a macro in Excel that would enable me to choose a folder and then convert the files. However, using programming code in Excel is beyond the grasp of many users, so a batch conversion tool such as I found here via a quick Google search should do the trick: http://www.batchwork.com/en/xls2xls/

      Other tools are available, so search on “convert xls to xlsx” if you’d like to see other options.

      David

  • I heard that Quickbooks Online data might soon be available in Power Query for Excel. Have you heard anything about this?

    • You’re right, it’s not there. On that platform you’ll have to do the converson manually with Save As and then delete the XLS version.

  • David – great article. You can use Power Query or any of the MS “Power” tools with QQube by clearify.com. Works great. QQube is a 3rd party data warehouse tool that allows you to generate reports that are beyond the capabilities of QB desktop.
    Yet the MS Power tools will connect with worksheets or with a direct connection like QODBC.
    The difference is that QQube is made to take advantage of these tools, where as QODBC or a report download from QB will not be set up as efficiently as it could. be. And you may run into formatting issues.
    I am very interested in connecting with others that are using (or want to explore using) these Power Tools for Excel and also Power BI. Fran

    • Fran,

      Thank you for adding more ideas on how to connect Excel with QuickBooks. I do plan to cover some of the Power tools in upcoming articles. The scope of this particular article is to help users that export reports directly from QuickBooks avoid feeling hamstrung in Excel. With that said, this approach is definitely the tip of the iceberg when it comes to extracting data from QuickBooks.

      David

  • My biggest drawback to using power tools today with clients is that many of them are still on Excel 2010 or don’t have the correct 2013 pack to take advantage of them. But as the new Office 2016 is on the horizon I hope that it be easier to encourage clients to adopt these tools. I can develop using them but if client doesn’t also have, then they can not refresh the data. The Power Tools or “modern” as Power Pivot Excel expert, Rob Collie calls it , are game changes for those using Excel for analysis.

    • I think Excel 2016 will bring a lot of opportunities. It stuns me to think Excel 2010 has been out for 6 years now, which is forever in computer years. But yes, I agree that the vast majority of Excel users are on aging versions.

    • @Fran – I think the situation that you outline here is exactly why PowerBI.com and Excel Online are going to shine in the next few years. Both of these solutions take the version of excel and operating system out of the equation, not to mention the mobile component which is the fastest growing area of Business Intelligence.