While most users export QuickBooks reports as Microsoft Excel workbooks, I often use the comma-separated value (CSV) format instead. Doing so strips extraneous formatting from the report so that I can immediately filter a report or analyze it with a pivot table. In this article, I’ll show you how to use the CSV format to create Excel workbooks that automatically update themselves, along with any helper formulas you may wish to add alongside your QuickBooks data. In future articles, I’ll share techniques you can use to capitalize on this functionality.
Two Ways to Export QuickBooks Reports to Excel
- In your QuickBooks Company, choose Reports, Company and Financial, and then Profit & Loss Standard.
- Click Excel, Create New Worksheet, and then click Export.
After a moment, the report should appear within Microsoft Excel.
Figure 1: The typical approach for exporting a report to Excel.
- Click Excel, then Create New Worksheet.
- Click Create a comma separated values (.csv) file from the Send Report to Excel window.
- Click Export, specify a file name for your file, and then click Save.
Figure 2: Exporting to a CSV file provides several unexpected benefits.
CSV files don’t open automatically in Excel, but the bit of extra effort will be well worth the trouble. There are actually two different ways to open this file in Excel – the second of which enables hidden automation features – but first I’ll open the file by hand:
- In Excel, choose File, then click Open. (Excel 2007 users will click the round Office button instead.)
- Change the File Type to Text Files (*.txt, *.prn, *.csv) and then browse to the folder where you saved the CSV file you created.
- Double-click the file name to skip clicking on the Open button.
At this point, the CSV file should be open in Excel, along with the initial Profit & Loss report you exported directly.
Comparing the Approaches
Before we compare the output of these two approaches, first close any other open spreadsheets. When only the two versions of the Profit & Loss report are open, carry out the steps shown in Figure 3:
- Choose the Arrange command:
- Excel 2007 and later: Choose View, Arrange All.
- Excel 2003 and earlier: Choose Window, Arrange.
- Double-click on Vertical; doing so allows you to skip clicking the OK button.
Figure 3: The Arrange All command allows you to view multiple worksheets simultaneously.
Figure 4: Viewing two spreadsheets side by side allows you to compare their differences.
As shown in Figure 4, some startling differences stand out between the two formats:
- In the Excel workbook version of our Profit & Loss report, the numbers appear in column G. Your column may vary based on the level of subaccounts that your reports include. Conversely, in the CSV version of the report, the numbers appear in column B. The latter format is far more analysis friendly in Excel. In future articles, I’ll discuss using worksheet functions, such as VLOOKUP, SUMIF, MATCH/INDEX, etc., to extract data from QuickBooks reports. Such analysis is daunting at best with the Excel format shown on the left – our formula criteria are strewn across two or more columns. Conversely, all report headings appear in column A in the CSV format shown on the right.
- Exporting to CSV provides another unexpected benefit with regard to formulas we may wish to add to our exported data. As shown in Figure 5, click in cell D4 in the Excel workbook version of your Profit & Loss report and then press Ctrl-1 to display the Format Cells window. You’ll notice that the number format is set to Text, which means that worksheets cells will display their contents rather than values. To test this, enter the formula =D3 in cell D4. Your formula will remain visible in the cell instead of the word Income. Most text-based columns within QuickBooks reports exported to Excel have the Text format applied by default. It becomes a Sisyphean task to repeatedly remove the Text format, although you do have a number of approaches available:
- Press Ctrl-Spacebar to select the current column in Excel and then press Ctrl-` (the same key that contains the ~ symbol) to reset the column to General format.
- Select the current column (or a range of cells) with your mouse, and then type the letter G in the field shown in Figure 6 to select the General format in Excel 2007 and later.
- Select a range of cells, press Ctrl-1 to display the Format Cells window, and then choose an alternate number format.
Figure 5: The number format is set to Text.
Figure 6: Change the number format to General.
None of these text format elimination steps are required with the CSV-based report – by its very nature it’s bereft of formatting. My wish list item for future versions of QuickBooks is that we would have a “raw data” option that would empower us to send data to Excel in list form, sans any formatting. It could easily be a new option within the Send Report to Excel window, shown in Figure 7. But until then, the Create a comma separated values (.csv) file option is as close as we can get within QuickBooks.
Figure 7: The Send Report to Excel window.
As we’ve seen thus far, the CSV format gives us a much more analysis-friendly version of our QuickBooks data. This alone is helpful, but just the tip of the iceberg with regard to the possibilities of using CSV files. For instance, we can use this format to create self-updating Excel reports:
- Create a blank Excel workbook.
- Click on cell A1 and then create a connection to the CSV file that you created:
- Excel 2007 and later: Choose Data and then From Text. Select your CSV file and then click Import.
- Excel 2003 and earlier: Choose Data, Get External Data, and then From Text. Select your CSV file and then click Import.
- When the Text Import Wizard window appears, as shown in Figure 8, click in the File Origin field and press W to change this to Windows ANSI. Excel inexplicably assumes CSV files from QuickBooks are of Japanese provenance. It’s not the end of the world if you miss this step, but the bullet symbol that appears between Account Number and Account Name in certain reports will be presented as a torch-like symbol instead. This is cosmetic and doesn’t affect your data in any way.
- Click Next to proceed to Step 2 of the wizard. Uncheck Tab, check Comma, and then click Finish. The third tab of the wizard does offer some formatting benefits that I’ll describe in a future article, but they’re beyond the scope of this discussion.
- When the Import Data window appears, click the Properties button and then:
- Clear the checkbox for Prompt for file name on refresh.
- Click Refresh data when opening the file.
- Click Fill down formulas in columns adjacent to data.
- Click OK twice.
- Save and close your workbook in the usual fashion.
- In QuickBooks, choose a different date range for your Profit & Loss report and then export it to a CSV file again.
- When you reopen the Excel workbook, the latest version of your Profit & Loss report should appear automatically within the spreadsheet.
Figure 8: The Text Import Wizard allows you to import data from a text file.
This technique to export QuickBooks reports to Excel will serve as the cornerstone for several analytical techniques that I’ll be sharing with you in the coming months. I’ll also explore nuances of this approach, with the overarching goal of empowering you to take control over your QuickBooks data. I’ll close today with a phrase that I coined several years ago: “Either you work Excel, or it works you!” This is true for QuickBooks as well.