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:
- Choose Reports from the menu along the left-hand side of the screen.
- Click the Customize link under Profit and Loss.
- Set Sub Items to Collapse.
- Change the Columns setting to Months.
- Click the Export button at the bottom of the Settings window.
- 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.
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:
- Click on the File menu.
- Select Info.
- Click Protected View Settings.
- 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.
- Click OK to close the Protected View settings.
- Click Enable Editing to edit your document.
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:
- Click once on any file that you wish to open.
- Click the arrow on the right-hand side of the Open button.
- Choose Open in Protected View.
This allows you to review files of unknown provenance before enabling any automatic links or automation within the workbook.
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:
- Select cell A7, which contains the words Design Income.
- Click the arrow next to the Fill Color command.
- Choose any color from the Theme Colors For this exercise do not choose any of the Standard Colors.
- Click the Save button on Excel’s Quick Access Toolbar or press Ctrl-S.
- 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.
- Click Continue to dismiss the Compatibility Checker window.
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:
- Select cells H7:H13.
- Click on the Insert tab in Excel’s ribbon interface.
- 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: 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:
- Select cells A7:A13, and then hold down the Ctrl key while you select cells G7:G13.
- Click on the Insert tab in Excel’s ribbon interface.
- Click on the Pie Chart icon.
- Choose the 2D Pie Chart.
- 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.
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:
- Choose File.
- On the Info tab, click the Convert button.
- 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).
- 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.
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.