Practice Management QuickBooks Small Business Tech Trends

CSV Secrets: QuickBooks Reports to Excel

Written by David Ringstrom

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

To begin, I’ll open one of the sample companies in QuickBooks and export a Profit & Loss report to Excel, as shown in Figure 1:

  • 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.

Exporting a report to Excel

Figure 1: The typical approach for exporting a report to Excel.

Return to QuickBooks, and then carry out the steps shown in Figure 2:

  • 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.

Exporting a report to CSV file

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.

View multiple worksheets at the same time

Figure 3: The Arrange All command allows you to view multiple worksheets simultaneously.

Viewing side-by-side spreadsheets

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.

Number format set to Text

Figure 5: The number format is set to Text.

Change the number format to General

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.

Send Report to Excel window

Figure 7: The Send Report to Excel window.

Automation Opportunities

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.

Text Import Wizard

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.

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!”


  • David,

    Thanks so much for this very helpful article. I have a large monthly report I update and refine each month, and these techniques sound EXTREMELY helpful, especially because for some inexplicable reason the columns in the Excel report are not the same every month, even though the accounts and subaccounts are the same every month. Looking forward to the next article in the series.

    Barbara Sonin
    Certified Quickbooks Proadvisor
    Certified Xero Partner

  • Barbara,

    Yes, the techniques in this article are exactly what you need. You’re going to particularly like one of the upcoming articles in this series that will enable you to create monthly reports from a single worksheet, instead of having to build a tab for each month. Just let me know if you encounter any problems incorporating the CSV approach into your report workbook.


  • I love it! This is a great article, and is an approach I never used before.

    I usually use the Custom ODBC & MS Query to pull data, but I’m definitely going to give this a try, to see if I can simplify some of my reports.


  • Great article, David! I look forward to more. I learned quite a bit from this.

    Of course, if your objective is to make a pretty report to give to a client, rather than doing data analysis, you would still use the Excel export since that holds formatting over from earlier revisions, etc.

    • Thank you, Charlie. I’ll be sharing ways to use this technique to feed presentation quality reports in Excel. I’ve only scratched the surface of possibilities at this point.

  • Hi David,
    Good article. One of the advantages to using the CSV format is that it is MUCH faster on the download to Excel. While one of my favorite techniques is to use the Stored Procedure reports with QODBC to bring data down to Excel so as to control format,content, etc – its not for all. This is a good approach. I find that Excel 2007/later will just open the CSV format without declaring ‘delimiters’. Fran

    • Fran,

      Thank you for the excellent point that in certain instances the CSV format is a much faster export from QuickBooks. For one-time use of a report, users can double-click on a resulting .CSV file to open in it in Excel, or go through the File menu and choose Open. The Data menu approach I presented will help automate Excel reports that need to be refreshed from QuickBooks. I agree that QODBC offers the highest and best connectivity to QuickBooks, and over time I’ll build on this CSV approach by introducing Microsoft Query and SQL techniques will hopefully empower users to try QODBC.


  • Facinating. I am already thinking of the changes I can make and am looking forward with great anticipation to the future articles.

  • Hello,

    I am trying to export a list of all checks issued from our main account into Excel or CSV file. Excel export takes about 45 minutes to complete as so far this year we have issued 53,000 checks and QB format requires 5 lines per check for a total of approximately 265,000 lines.

    CSV export takes only few seconds to export the first 32,768 out of 265,000 and then terminates without giving any error message.

    Would you happen to know why?

    • I’ll guess that you are running into an “out of memory” kind of issue. I’ve not tried to export that amount of info that way. You may have to look into an add-on export tool, or see if you can do the export in smaller chunks.

    • Leonid,

      I program extensively in Excel, and I know that 32,768 is the maximum value for a number classified as an integer. It may be that you’ve stumbled across an inherent limitation of exporting from QuickBooks as the programmers may have labeled the record ID field that holds what you’re going to export as an integer, rather than a double, long, or other number type. I know this gets pretty deep in the weeds, but I wanted to include this in the discussion for others that might get stuck in the same fashion.

      With that said, this only sheds possible light on the why you’re only able to export that many records. As Charlie noted, I’d export the records in smaller chunks to get around the limitation, or else use the ODBC driver in QuickBooks Enterprise, or purchase a third-party tool that adds ODBC support to the other versions of QuickBooks.


      • Thank you very much David,

        I’ve tried to use ODBC from However,I was told by their customer support that “Cleared Status” table was not added by Intuit developers to the SDK. Thus, I cannot use ODBC to export a list of checks with cleared status.

        Do you know a work around this issue by any chance?

        Thank you very much in advance!


        • Leonid, unfortunately, Intuit isn’t very responsive when it comes to filling in the blanks as far as fields that are missing in their desktop programming interface.

          How about exporting a Custom Transaction Detail report filtered for checks, with the “Clr” column?

        • Leo,

          I think as Charlie noted you’re going to have to export your records in batches to reports that include the Cleared status. I will blue-sky another angle, though. You might be able use QODBC to get all of the records out to an Access database, and then create two-column reports in QuickBooks that have the transaction ID and the cleared status, which you then match together via an Access query. The 2 column report might not hang in the fashion that the regular reports do, but I haven’t tested this, and I’m just attempting to brainstorm here for you. I hope it helps! I hate when data gets “stuck” like this.


  • Can you use this same CSV approach when consolidating data form multiple companies. Fir example, what about a corporation that owns 10 separate legal entities that report and are managed independently? I create a report for each one and then create a consolidates Pivot Table, but as you can imagine this process is painfully slow. Can I adapt the CSV approach for consolidation?

    • Jeremy,

      You can clearly link all 10 CSV files to the same Excel workbook. Excel does allow you to create pivot tables based on multiple consolidation ranges. I will say that I haven’t had need to experiment with that functionality, but as an alternative it would be a simple matter to create an Excel macro that would “stack” all 10 sets of records together into one list for pivot table purposes. Let me know if you need additional background.


  • David,

    Thanks for all of the Excel articles. I enjoy reading and learning about the features. I have a question, when I want a new date range to generate in excel, I have to export it from Quickbooks, but do I need to overwrite the existing csv file? At this point, I cannot update the excel sheet unless I overwrite the old csv file with the new date range from Quickbooks. Is this how it is supposed to be?

    Thank you,

    • Orry,

      I’m glad you’re finding my series helpful. Yes, you’re on exactly the right track with regard to saving over the existing CSV file. If you look at figure 8, steps 7 and 8 instruct Excel to automatically pull in the data from the CSV file automatically. Now, if you did want to preserve individual CSV files, then in step 7 of Figure 8 above leave Prompt for File Name turned on, and Excel will ask you to choose a CSV file to import. I find it’s most efficient to just overwrite the CSV file over and over again, but you can have more control over the process if you choose.


  • I have Microsoft Office on the cloud and QuickBooks online and they are telling me there is not a way to export a report in to Excel unless I load Ecxel on my computer. HELP!!!

    • When you click on the Excel button within QuickBooks Online an XLS file gets downloaded to your computer, which you would then have to save to your OneDrive in order to get to it in Excel Online. An alternative would be to install the free Libre Office application, which has a Calc spreadsheet that can open Excel files.

      Let me know if this helps or not. I’m curious to learn more about your situation so that I can help others that get stuck in the same loop. As you’ve found, there are presently some rough edges involved with being entirely cloud-based.

  • I actually need help in the other direction – getting an excel file (saved as .csv) to quickbooks. Do you have any suggestions?

    My story –

    I made the mistake of downloading files from several accounts and saving them as csv because I thought quickbooks could read that. Then when I tried to import them, it only pulled some information and it wasn’t formatted right. Then I saw online that there are ways to convert pdfs to quickbooks, so apparently I didn’t even need to deal with the spreadsheet at all. But I’m stubborn and I don’t want to re-download everything, and then convert that set. So I’m hoping to just be able to change the csv into a qbo file. I’ve found a converter on this site moneythumb but not sure if I want to purchase. They have a free trial so I will give that a go…has anyone heard of this or any other options? I’m so frustrated!

    • Rebecca, what kind of information is this? And, what QuickBooks product do you have?

      Importing is complicated. Some editions/years of QuickBooks have the capability of importing SOME kinds of transactions from Excel (you can convert your CSV to Excel easily) but those options are limited.

      Going to a QBO file is rarely a good option, as many types of transactions can’t be imported. But sometimes it works. Depends on what you are trying to import.

      The best option is to use Transaction Pro Importer, which has a wide range of transaction types it can import. See for details.

      Another option, which is lower cost, is the import tool from Big Red Consulting. I usually avoid using this as I don’t like IIF imports – but Big Red Consulting is reliable.

      You can create your own IIF import, for free, but I strongly recommend against trying that as it is complicated and you can easily corrupt your QB file if you make a mistake. The Big Red tool avoids those mistakes. The Transaction Pro tool is even safer.

      • I’ve had nothing but bad luck with IIF so I’m avoiding that at all costs! The easiest/most accurate option I’ve found so far is the MoneyThumb. I checked them out and did the free trial and it was pretty impressive. Plus the cost wasn’t too bad (although it’s a business expense, so that isn’t hugely important) so I felt like I got a good deal. I like deals even when I don’t really need them 🙂 If I have any problems I will try the other links you suggested, thank you!

        • Rebecca,

          I’m glad you found a tool that works for you. IIF can be tricky, but if you use a tool like the one from Big Red Consulting it has lots of safety checks built in that ensure your file will be properly structured. No matter what tool you use, *always* make a back-up before you import any data into QuickBooks. You can’t have too many back-ups when you’re trying to import any sort of data!


  • We have been exporting reports to Excel for quite sometime without any issues, however one our sister companies with operations abroad have started using QB and they encountered a problem when exporting to a CSV file. Problem arises as a result of them using Excel with numbers formatted the European way, that is for expressing decimals and thousands. For them our 9.5 is expressed 9,5 ; our 1,000.75 is 1.000,75 Can you help us addressing this issue?

    Thank you,


    • This is a Windows setting. Keep in mind that this is a situation where solving one problem might in turn cause another. However, to resolve this you’ll go to Region and Settings in the Windows Control Panel. Click Additional Settings on the Format tab, and then change the Decimal Symbol. This will change the decimal for all programs on the given computer, which may or may not be desirable. However, sometimes this setting gets changed inadvertently, resulting in the results that are being exhibited.

  • Hi,

    I’m trying to get a csv file from quickbooks online and its impossible. When you press the excel button the download starts and it doesn’t let you choose the file format.

    • You’re right, you can’t directly get a CSV file from QuickBooks Online. At the present time the best you’d be able to do is open it in Excel and then resave it as a CSV file. It’s really tough to get much data out of QuickBooks online in a usable format for Excel. I’ll be exploring that topic in detail in an upcoming article.

      If you can tell me what you’re hoping to do with the data from QuickBooks Online I can probably suggest a workaround approach by way of Microsoft Query.

        • OK, Microsoft Query won’t work for that. Your primary option is to manually open the file Excel file that you save from QuickBooks Online and then save it as a CSV file. If you need to rearrange the data to import it into Plan Guru, or if you’re importing frequently into Plan Guru the CSV process can be automated by way of an Excel macro. If you can tell me which report you’re exporting from QuickBooks Online, and whether or not you’re having to modify the columns and rows before you import to Plan Guru, I may be able to provide some additional guidance.

  • Hello,

    Someone mentioned using Custom ODBC and MS Query to create reports. Which is a better way to create reports? Going through ODBC or using VLOOKUP and other functions in Excel?

    • When it comes to getting data out of QuickBooks, better can be a relatively abstract term. You can certainly tap into the database directly via ODBC if you have the technical chops to do so. Many users would say this is the best approach. However, for many QuickBooks users the term ODBC makes them say “Huh?” and they don’t know where to start. I’m probably taking a long-winded approach to say there’s not really a right-or-wrong answer here, the best approach is the one that you can wrap your head around. ODBC is sophisticated but can present challenges for the uninitiated. In my series of articles I’ve tried to democratize getting data out of Excel by writing about approaches that anyone can use. That’s not so say my approaches are best, but rather suitable options to consider. I hope I’ve addressed your question satisfactorily, but let me know if not and I’ll take another crack at it.

  • Thank you. Your answer makes sense. I am not sure I have the technical chops for the ODBC route. I work for a company that uses a 13 period year with each period being 4 weeks each. QB is not very friendly with this type of a year. I am trying to find ways to create better procedures for creating reports and work flow processes. We are not ready to jump to Microsoft Dynamics from an affordability standpoint so we are trying to make QB work for us.

    • You’re right, some of the the built-in reports won’t play well with a 13 period year. This article serves a a foundation to work from for a variety of articles that I’ve written about how to analyze QuickBooks data once you’ve gotten it out of the program, so you have a lot of great resources to start with, and feel free to reach out with any specific questions that my articles haven’t covered.

  • Is there a way to export Balance Sheet in CSV with the balances always in the same column regardless of the level of subaccounts that the reports include? When I save Balance Sheet in CSV it has all the sub accounts in a separate column.

    • Tom,

      Off the cuff, would exporting a Trial Balance work the same for you as exporting a balance sheet? If not, please let me know and I’ll be happy to see if I can recreate what you’re experiencing.


  • We have a report that was exported from QB to excel. Only one of our networked PCs can successfully “update” the report. Other PCs can access, however when they try to “update” the report they get a message that says “This workbook has been modified. Save the workbook and try updating again.”.

    Any suggestions?

    • By update, do you mean that you created a report that stays tied to QuickBooks that you update periodically, instead of exporting directly? I honestly tend to stay away from this feature in QuickBooks as I tend to be distrustful of linked workbooks in general due to bad experiences in the past. I’m on a summer break away from my office the next couple weeks so I can’t try to replicate what you’re experiencing, but if you can provide more background I may be able to help you get to a solution.

      • Correct! I only update this maybe once per week. I have macros and other formulas built into the spreadsheet that let’s me see the data in a more meaningful format. When I was working on a different PC I accessed this report on the network, but was unable to update…instead I got the error message above.

        • Make sure that the workbook is saved to a public folder with exactly the same path on every computer. Short of that, adopting the CSV approach that I discuss in this article is the best recommendation I have. I think Windows may be trying to be helpful and manage the file on the network but in such a way that is blocking QuickBooks.

  • Great information! I would imagine this would work for budgets as well. Do you know if there is a way to export into excel, make changes to the numeric data only in excel, and then import those changes back in to QuickBooks? I am using QuickBooks Accountant 2014

  • Thanks for this article. I want to ask you about the “raw data” feature that’s on your wishlist.
    I work with Excel and people keep sending me reports that I have to turn into the raw rows and columns. I’ve been asking “can you send me unformatted data?” But I just get more reports.

    From your blogpost, it sounds like QuickBooks just doesn’t do data dumps. Is that true?

    • Oz,

      QuickBooks doesn’t offer direct data dumps from the software. You can export lists to tab-delimited files, but these can still require some clean-up. The Enterprise version of QuickBooks does offer an ODBC connection by which you can perform direct data dumps, but that’s beyond the skill set and experience level of most Excel users.

      My complaint is predicated by Sage 50, which in its export screen for reports offers a “Raw Data” format that in effect is a dump, and also offers the formatted version like QuickBooks offers. There’s a couple options for connecting to QuickBooks via ODBC if you’re not using Enterprise, including


  • Hi David,
    Thanks for your informative helpful articles. My problem is that when I import the CSV file into Excel it places the cents amount in a coloum sererate to the rest of the number. This is very frustrationg. Do you have any suggestions for me?

    • Yes, that would be very frustrating. I need to see an example of your file to troubleshoot this further. Sometimes the culprit is within the Regional Settings area of Windows, in other cases there might be a hidden tab character embedded within the row. I’ll email you to request a sample of your data, and I’ll post my findings back here for the benefit of others in the future.

    • Claire,

      Thank you for sending the sample files. As I suspected, it is a Regional Settings issue within your computer. You’re located in South Africa, which I see uses the comma as a decimal mark. This means amounts exported to a CSV file look like this for you:

      ,”31 Dec 15″
      “My Bank”,””,1003,80

      The comma between 1003 and 80 is what is causing your amounts to appear in a second column. This is an unavoidable side effect of the CSV format, which is optimized for using the period as a decimal mark, such as:

      ,”31 Dec 15″
      “My Bank”,””,1003.80

      I could show you some formulas to try to realign the numbers into a debit and credit column (instead of two debit and two credit columns as you have now), but before we go there please read about using Microsoft Query as an alternative:

      Alternatively, to change your regional settings to use a period instead of a comma, carry out these steps:

      1. Access the Control Panel feature in Windows.
      2. Choose Region and Language.
      3. Click Additional Settings on the Formats tab.
      4. Change the Decimal Symbol to a period instead of a comma.

      I doubt you’ll want to do that on your computer, but I have encountered users over the years in the US where their regional setting gets changed inadvertently.

      If Microsoft Query doesn’t work out for you, report back and I’ll show you how to create set-and-forget formulas to realign the numbers into two columns instead of four.


  • David,
    I am looking for help on exporting customer invoices to a csv file. I need the invoice number, shipping method, address, item number, quantity, and memo fields.

    Do you have any suggestions on how to get the information out so we can upload it to our webstore for shipments?

    • My first question would be to ask which webstore you’re using, as many have integration options these days, so we’d want to rule that out first. With that said, many QuickBooks ProAdvisors like using ODBC tools that can extract data directly out of QuickBooks, such as QODBC, into Excel. From there you can save the transactions as a CSV file.

      Another approach that works strictly inside QuickBooks is to choose Reports, Custom Transaction Detail Reports, and then customize the report to show just the fields that you need. From there you can save the report to a CSV file as described above.

      QuickBooks keeps crashing on me when I try to export to CSV on the computer I’m using, but I have confirmed that the fields can be accessed in that fashion.

      My preferred approach for solutions such as this is to build a custom tool using the QuickBooks SDK (software development kit). In such cases an Excel-based tool can house the programming code, and a simple user interface can be developed to allow you to specify the dates you with to extract the data for, and then the CSV file can be generated to the exact specifications your webstore needs. Depending upon the webstore, sometimes the upload of the CSV file can be automated by way of Excel as well.

  • Thank you for this useful information !

    I have a question though, Can you automate QuickBooks to export that CSV file ?? Lets say every night at a certain hour, QuickBooks export the report and save it in folder. So morning employees can have their data up to date every day!


  • Several clients have exported Financial Statements in Excel 2007 as an xls file. When I get the files they look AOK. But when I try to write formulas or create charts – Excel stops… no lines in the chart… cells won’t take the formulas. Pure Frustration — Very time consuming to rewrite all those numbers into a fresh spread sheet, which has been the only way of solving the problem… I will bet you know the answer!

    • Bruce,

      I first have a couple of thoughts that I hope you’ll accept constructively:

      – Excel 2007 has been superseded by three subsequent releases. Old software like Excel 2007 and new software such as QuickBooks 2016 sometimes don’t mix well together.

      – The XLS file format is obsolete, and causes subtle but significant feature disruptions, especially in the very latest versions meaning Excel 2013 and 2016. No one should be using the XLS file format for general use spreadsheets any more.

      With that out of the way, here’s what I’d try in your situation:

      – Ensure that your clients have the latest patches installed for QuickBooks, because it feels to me that the workbooks aren’t being created properly.

      – If your clients’ version of Excel allows it, export to an XLSX spreadsheet.

      – Try repairing the affected workbook. In Excel’s Open dialog box, click once on the spreadsheet to select it, and then click the arrow on the Open button and choose Open and Repair from the submenu.

      – Failing that, follow the instructions in this article to export to the CSV format, which will avoid all problems.

      This is as far as I can go without laying my hands on one of the affected workbooks. It feels likes something environmental, and possibly patch related. I hope this helps!


  • Your articles are extremely informative and appreciated. Question;
    Can I import edited check numbers from a report that I modified in excel. The only edit from the report would be the check numbers. I would like banking transactions that are not an actual check to read differently.

    Thank you
    Olga Nelson

    • Olga,

      Thank you for your kind feedback, I very much appreciate it.

      Regarding your question, when you import into QuickBooks the transactions get duplicated, so that route is not going to work for you. It is possible to edit existing transactions by way of custom tools, such as those that are programmed to use the QuickBooks SDK (software development kit). There are other tools that connect to QuickBooks via ODBC, and my understanding is that they only provide read-only access to data in QuickBooks, meaning you can’t push changes back to the software. So, in my experience having a tool commissioned is the only way to solve this particular problem.


  • Awesome article! It works splendidly as long as new lines are all at the bottom. One can add additional formulas and data in columns to the right of the QB data and keep that intact while the QB part of the data gets updated! However, if an updated QB report has new lines – in between existing lines – the added non-QB data goes out of skew. I wonder if you know a solution for that. In any case this saves me a lot of trouble! Thank you so much!

    • Pieter,

      Thank you for your kind feedback! The data that you add to the right should all be formula-based. If you have static data that you’re adding, I’d try to store it elsewhere in the workbook and then cross-reference it back to the QB report by using VLOOKUP or something similar. As you found the technique doesn’t work with static data.


  • Hi David
    Thank you so much for this article it’s really helped out. I have a question I didn’t see listed here in the comments, maybe you have my answer in another article somewhere.

    is there a way to have these steps automated daily from QuickBooks so i don’t have to go into QBs each day and select today’s date just to get the data dumped into the CSV template? or perhaps all i need to do is go into the template and select refresh??? ultimately I’d love to have this emailed to me each day so its in my inbox i can just open format and i’m done.

  • David,
    This original article changed my life for the better, but now I have Excel 2016 and process has changed. Any chance you could update this using Excel 2016 as a guide?


  • Sue,

    Robin has a point, actually. I’m going to bet that she’s using the Office 365 version of Excel 2016, which is evolving much faster than the perpetual licensed versions of Excel. So we’re in a space where my Office 365-based version of Excel 2016 has different menu commands and functionality in some areas than your perpetually licensed version of Excel 2016 does.

    I’ll plan to write a refresh of this article soon to cover the changes in the Office 365 version of Excel 2016.


    • Thank you. You are correct. I am using Office 365, so it is updating constantly and the new interface presents opportunities, I’m sure, but challenges for now– Chief among them is the lack of ability to convert data in “general” format. I’ve found a way to jailbreak it and go back to the old model, but feels like I’m leaving money on the table.

      • The constant evolution of the Office 365 version of Excel 2016 is definitely presenting both risk and opportunities. Risks of simple tasks suddenly turning into rabbit holes because the techniques are radically different, as well as many of the new features are incompatible with no only older versions of Excel, but the perpetually licensed versions of Excel 2016. There are opportunities as well, though, of newer and easier ways to get things done. As a writer and a webinar trainer, there are many opportunities for me to help others navigate these new tricky waters.

        Contact me through my web site at if you have specific Office 365 questions. In my work it helps me to see where folks are getting stuck or frustrated in Excel. And be on the look-out for an update of this CSV article, which based on the publishing schedule should appear before the end of this year. We’ll be sure to link both this present version of the article and the new version together for easy cross-reference.

        • Hi David

          I’m relatively new to Quickbooks. In my previous job I used a program called Xrl to add rules to data being exported from my financial software (Opera) to Excel. This was very handy for producing a P&L where a number of account codes would be merged into one line in Excel e.g. merge rent, rates, service charges and water rates accounts into one line called “Rent, Rates & Service Charges”. Will QODBC do this for me or do you have another suggestion?

          • Derek,

            QODBC is a vehicle for getting QuickBooks data into Excel, typically in a list format. You’d then craft your own formulas that combine accounts together. One of the approaches you could take is to take a copy of your chart of accounts and put it into the Excel workbook that you use QODBC with. Add a column to the chart of accounts that includes the report description, such as Rent. You can then add a column to the results that QODBC provides an use VLOOKUP to return the report description line. On your report itself SUMIF can add up the multiple instances of Rent for instance.

            I realize I’m writing this at a very summary level, so let me know if you need more specific guidance.


        • David,
          So as I mentioned, I’ve been using a workaround by importing data using the (legacy) feature, and that’s been working ok, until today, when all of a sudden, now when I import data from CSV files, all of the double-quotation marks are are populating in every cell, when before they did not. Any thoughts?

          • OK, that’s some good stuff that you found the legacy Text Import Wizard. It hadn’t occurred to me to look for that yet. For those that are using Office 365 you can access this feature as follows:

            – Right-click on the Quick Access Toolbar that typically appears in the top left-hand corner of the Excel screen.
            – Choose Customize Quick Access Toolbar.
            – Choose Commands Not in the Ribbon
            – Choose From Text (Legacy)
            – Click Add
            – Click OK

            You’ll now have a new icon on your Quick Access Toolbar that will enable you to use the classic Text Import Wizard.

            So Robin, on to your question. It feels to me that it could be one of two issues:

            • Open your CSV file in Notepad. If there are two sets of quotation marks around each field then within the Text Import Wizard the second screen has a checkbox for Treat Consecutive Delimiters as One. Try clicking that checkbox, as it should clear up your problem.

            • The other thing this feels like is your Text Qualifier on that same screen may not be set to ” (a double-quote).

            Let me know if either of these help. Thank you again for bringing that legacy command to my attention. I’ve unearthed those for other commands that vanished, but haven’t for this one yet. It’s so easy to get tunnel vision in Excel.

  • Thanks, David. I logged in this morning and it is working again (ie no quotation marks) but based on your comments, I think I know what happened yesterday. I tried to take a shortcut when pulling the data in from the CSV file and check both the box for Comma delimited and Other delimited, specifying the · symbol to separate the QB account numbers from the text – a step I normally do separately using text to columns. It worked the first time, but every file I tried to do subsequently yesterday did not like it, and worse, wouldn’t let me do it the old way. For whatever reason, today everything is back to normal, and I feel like I’ve had my hand slapped by the Excel gods.

    I do look forward to your tutorial on how to utilize the new bells and whistles in the new Office 365 data connection tool. It looks very powerful and useful, but at first glance, better suited for traditional databases than Quickbooks financial statements. I’m sure there’s a way to use it though.


    • Thanks, Robin, you’re right that you can’t overreach on choosing multiple checkboxes on the second screen of the Text Import Wizard without consequence.

      The good news about the new functionality in Office 365 is that it provides a consistent interface for *anything* you’re bringing into Excel. So it can work for QuickBooks financials, and it actually does streamline some aspects. But it’s a bit disconcerting the first few times you use it.

      A fresh eye does wonders in Excel, so it’s always good to set things down and walk away when you feel like Excel is out to get you. The challenge I’ve observed is Excel can only do what we TELL it do do, which doesn’t always align with what we INTEND for it to do. 🙂

  • Hi David,
    Do you have a method of saving the 1099 files from QuickBooks and being able to convert them to a txt file to upload to States that require them, but do not participate with the IRS efile program.

  • Hi David,
    Thank you for the article and great tips. I exported in CSV the P&L by month divided by columns and it’s displaying as “17-Nov” for November 2017 and “17-Dec” for December 2017. The problem is the underlying date format is shown as “11/17/2018” and “12/17/2018” which is incorrect. Is this a QB settings issue or CSV issue?

    • Here’s what’s going on. The first row of the CSV file when you export it from QuickBooks looks like this:

      ,”Jan 21″,”Feb 21″,”Mar 21″,”Apr 21″,”May 21″,”Jun 21″,”Jul 21″,”Aug 21″,”Sep 21″,”Oct 21″,”Nov 21″,”Dec 21″,”TOTAL”

      As humans we’d recognize that as a month and year. Excel doesn’t, however. It sees this as January 21, February 21, and so on. Further because no explicit year is listed, Excel appends on the current year, which is why your 2017 report suddenly appears as if it’s 2018 data.

      To overcome this, you could import the CSV file into cell A2 of a worksheet. Then in cells B1 through M1 use this formula:


      This would date the month portion (which matches QuickBooks), and the day portion (which is really the year but Excel treats as a day). By inserting /1/ in the middle, we’re recreating a “normal” date in Excel. This would be considered a text string, which might be sufficient, but you could go further and add DATEVALUE if you wanted a numeric date:


      Great question, and I hope this provides a solution for you. Let me know if not. Excel is fraught with nuance, and this is practically a nuance of a nuance. 🙂

  • Haven’t read all the comments but for those that have used newer versions on Excel (2010 & 2013 w/ the power query add-on or 2016 which made power query a core function under the data tab called “get and transform”) there are better ways to create “connections” to other workbooks.

    The problem with the older legacy methods are they keep the source file locked in an open state when you have your file open. This prevents updating the original file. The new connection methods don’t have this limitation. They also scale better for large datasets. They also provide a bunch of “transformation” functions. You create “steps” that alter your data however you need and whenever you “refresh” your workbook/worksheet the new data is automatically ran through those rules, something that isn’t really feasible in the legacy methods.

    It’s one of the most underutilized and best additions to Excel is a long time. Completely replaces the need for the old connections method and vlookup.

    • Thank you for your thoughtful response, Jason. I wrote this article 4 years ago, back before PowerQuery and PowerBI had gotten the traction that they have now. I do agree that those tools are more powerful, but they also are less “Excel-like”, which can mean Excel users feel like a stranger in a strange land. I’m not dissuading anyone from using modern tools, but rather saying as a counterpoint sometimes legacy tools still have a place as well.

  • David.

    I found your article relevant and most helpful, even though it was written a while ago. Do you know how to get rid of the date “subtitle” on the report? When I modify the header information the date/date range stays at the top of the report and is included in my export. It makes sorting the information in excel a little difficult. Any help would be appreciated.

    Thank you

  • Hello,
    This article taught me some new things in Quickbooks and Excel!
    I have an excel spreadsheet we update weekly with our expenses/revenue from the Profit and Loss report in Quickbooks. I am trying to have those totals be automatically pushed into this custom spreadsheet I have built. I am fine with it being pulled from another file if need be, do you have an article that could help with this issue or maybe able to answer my question?

  • Dear David,
    One more time on behalf of all the people above I want to say thank you for your work on so helpful article. It is really appreciated.
    Most of user still spend hours on manual editing reports from QB and their local CRMs. That damages daily workflow heavily.
    Thank you for providing information adapted for wide audience. I’ll do my best to share this article with all my colleagues.