Print This Post

QuickBooks 2012 Excel Integration Improvements

September 26, 2011 | By | 59 Replies More

QuickBooks has your data, you want to get it into reports that are formatted the way that YOU want. You can customize or modify QuickBooks reports, but the options are limited. How do you get things to look right? Many QuickBooks users will export to Microsoft Excel and then reformat the reports. If you are one of those users, you will enjoy the Excel integration improvements in QuickBooks 2012.

Intuit spend a lot of time studying how people use QuickBooks and reporting tools. They knew that many of the complaints about QuickBooks revolve around hassles with generating reports. Sure, there are a lot of great reporting tools available that integrate with QuickBooks at an extra cost (my favorite is QQube), but Intuit wanted to improve the built in features. If you have QuickBooks Enterprise you have the ODBC driver, which you can use with Excel (see this tutorial), but ODBC is complicated (and an extra charge if you have Premier or Pro). Intuit’s research shows that most users are comfortable with Excel, but that they want to get in and create their reports quickly. So, they’ve worked on improving the Excel integration with QuickBooks.

If you export a QuickBooks report to Excel you will probably be changing the appearance, adding columns, adding formulas and more. Unfortunately, once you create your modified report you can’t easily import new data from QuickBooks. You have to reformat everything again. With QuickBooks 2012, though, you can create a template report and retain many formatting elements, such as the following (more options should be available in some upcoming revisions):

  • Change the fonts in row and column headers.
  • Create new formulas.
  • Rename column and row headers.
  • Change report titles.
  • Resize columns.
  • Insert columns and rows.
  • Insert text (when entered as a formula).

Lets take a look at how it works.

Select a report, such as the Inventory Stock Status by Item report shown here. Click the Excel button and select Create New Worksheet

Inventory Stock Status by Item in QuickBooks

Export the report to a new worksheet.

Excel Report Options

If you click the Advanced button you have a number of options that you can select.

Advanced Excel Report options

Here is what that report looks like in Excel.

Report in Excel

I’ll make a number of changes. I changed the font for the column headings, changed the text for some column headings, resized column widths, inserted the “Net Purch” column and added a formula (Calc Avail + Incoming), added some formatted text under one column heading. Then I save the report.

Modified report

So far nothing is new.

Later, after some sales and receipts have changed the values in that report. Note, for example, that Available has changed. I select Excel and then Update Existing Worksheet.

Updated QuickBooks report

Locate the existing “template” worksheet and click Export. You will be asked if you want to update that worksheet with new data.

Update existing worksheet

Here is the Excel worksheet. Note that my formatting changes carried over (except the color formatting of that added text cell). I don’t have to redo all of the reformatting work, including my calculated column. This is very useful.

Updated Excel report

Excel Reports Without Opening QuickBooks

Another aspect to this new feature is the ability to update your Excel report (once created) with new data, without having QuickBooks open. I’m not sure how much time this will save, and you still have to have QuickBooks installed on your computer, but let’s take a look. In the 2012 R1 release I ran into a small glitch, although it is easy to get around.

Open your QuickBooks company file (as the Admin user, please) and create your Excel report as shown above. In Excel, find the QuickBooks tab (I’m showing Office 2010 here), and click on the Update Report QuickBooks icon.

Excel Plugin

This opens the Update Report window. Make sure that your company file is selected (it should show the right one by default) and select your date range for the report. Click Update.

Update Report

The Excel report will be updated with the most current data.

Updated report

That should be all you have to do. From this point on you should be able to click the Update Report icon in Excel to update the data in the report even if QuickBooks is not running.

Let’s test this – I close QuickBooks, start Excel, click the icon, click the Update button, and…

Oops

QuickBooks Excel Error

I’m not sure if this was an issue with just my own system, or if it is a general problem. If it is a widespread issue, I am sure that they’ll correct this in the next release. If you run into this it is an easy fix – here is what you need to do.

  • Open QuickBooks as the admin user, select File then Preferences. Find the Integrated Applications preference and click the Company Preferences tab.
  • In the list of applications locate the INTUIT EXCEL ADDIN. It should be checked as shown below. Make sure that the Don’t allow any applications box is NOT checked (normally it isn’t). Click on the INTUIT EXCEL ADDIN item and click the Properties button.

Integrated Applications Preference

  • Under Access Rights put a check mark in the two boxes shown. In my test installations with QuickBooks 2012 R1 the Allow this application to login automatically box was NOT checked, and that is the problem.

Access rights

Once you have this setting corrected you should be able to update your reports from Excel without having QuickBooks open.

NOTE that if you are using multiple company files you have to go through this process with each company file as the settings are specific to each file.

 

I’m glad to see that Intuit has put a lot of thought into this feature, and I’m sure that they are going to develop this further in the future. If you are starting with the basic QuickBooks reports and making formatting and minor calculation changes, this will be very helpful. And this is available at no additional charge.

If you want to have greater control over your reports, and perhaps create reports that are NOT found in QuickBooks already, I highly recommend that you consider one of the excellent reporting tools that are available. If you are comfortable with Excel, I highly recommend the QQube reporting tool from Clearify. It is the best way to get your QuickBooks data into Excel.

Tags: , , ,

Category: Excel, QuickBooks Tips/Tricks, Software Updates

About the Author ()

Charlie Russell is the founder of CCRSoftware. He's been involved with the small business software industry since the mid 70's, and remembers releasing his first commercial accounting software product when you had a one-floppy disk drive system, loading the program from one floppy and then replacing that with the other floppy to hold the data. He has a special interest in inventory and manufacturing software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor with additional certifications for QuickBooks Online and QuickBooks Enterprise. He also is a Xero Certified Partner. Visit his CCRSoftware web site for information about his QuickBooks add-on products. Charlie can be reached at charlie.russell@sleeter.com He is also the author of the California Wildflower Hikes blog Connect with Charlie at Google

Comments (59)

Trackback URL | Comments RSS Feed

  1. Jace Greenman says:

    Sadly, this new capability can still be rendered useless if you have reports with over 256 columns. Although Excel has supported over 16,000 columns since 2007, five years later QuickBooks still only exports 256. The “workaround” is to export to CSV. But of course then you lose all the formatting and connectivity of exporting to Excel.

    • I am wondering if anyone is having trouble with new Excel features? I have a client who is unable to “Update” the data in Excel. She tried to “Update” from QB and tried to “update” from Excel and neither worked. When updating from QB it said the format had changed in Excel (which it hadn’t) so it could not update, when trying from Excel it would seem to be updating but the sheets had no cells and were all gray????

      In addition it seems as though you need to have the Excel sheet open in order to update from QB whereas previously it worked better when Excel was not open.

      We have been using the same Excel workbook for a long time and have had no problems until she updated to 2012.

      Any thoughts?

      Thanks,

      Geoff

  2. William Murphy says:

    Charlie – another well written, informative article on a new much needed functionality, that Intuit engineers are continuing to work on and expand because they know that this is one of the most needed improvements of all time. Now a lot of ‘report’ 3rd party providers are probably NOT happy to see this change, but there will always be a need for other reporting tools that can do more, or streamline the process of getting more into a user friendly format with ease, so they need not be worried in my opinion.

    As for Jace – I can’t even imagine a spreadsheet with more than 256 columns, despite Excel’s limits, but to each his own….that is what having more and more ‘reporting options’ is really all about…..just like at burger king ‘having it your way.’.

    Murph

  3. Thanks, guys. Jace, I have to agree, I can’t imagine extracting a report from QuickBooks via Excel that would have that many columns. I suspect that you are using this to extract some data rather than just run a standard report? In that case I normally use tools like the Transaction Pro Exporter from Baystate Consulting, OR the QQube tool from Clearify. Both are better suited to extracting information. But then, of course, there is an added expense for those tools.

    Murph, I don’t think that the third party tool makers are really worried about this, as this would be just a small portion of what kinds of things that they can do.

  4. Karen says:

    Thank you Charlie – I see this as a very positive step. Especially the update feature. I have many small business clients that will absolutely love this feature.

  5. Cliff says:

    I am finding QB fails to update some excel worksheets, and generates the following error.

    [img]http://img502.imageshack.us/img502/7926/errorot.jpg[/img]

    I sometimes get the error when I click on Update Existing Worksheet > select a workbook and sheet, and export.

    I get this error on some memorized reports, not all of them. There must be a bug where QB cannot figure out how to handle some memorized reports.

    • Cliff says:

      As follow-up to my previous post.

      I am seeing these error messages in my QBWin.log.

      ExcelHelper.cpp (470) : MESSAGE: 6048: Sun Oct 09 02:48:13 LVL_ERROR–ExcelHelper::WriteExcelVariable Com Error#: 800a03ec
      qbExcelCustomProperties.cpp (1307) : MESSAGE: 6048: Sun Oct 09 02:48:13 LVL_ERROR–ExcelHelper::SetCustomPropertyString – Cannot add variable to excel : QBSUBSTORAGE
      ExcelHelper.cpp (470) : MESSAGE: 6048: Sun Oct 09 02:48:13 LVL_ERROR–ExcelHelper::WriteExcelVariable Com Error#: 800a03ec
      qbExcelCustomProperties.cpp (1307) : MESSAGE: 6048: Sun Oct 09 02:48:13 LVL_ERROR–ExcelHelper::SetCustomPropertyString – Cannot add variable to excel : QBSTARTDATE
      ExcelHelper.cpp (470) : MESSAGE: 6048: Sun Oct 09 02:48:13 LVL_ERROR–ExcelHelper::WriteExcelVariable Com Error#: 800a03ec
      qbExcelCustomProperties.cpp (1307) : MESSAGE: 6048: Sun Oct 09 02:48:13 LVL_ERROR–ExcelHelper::SetCustomPropertyString – Cannot add variable to excel : QBENDDATE

    • The new feature doesn’t work for all reports – the help file lists where it works, I believe. You didn’t mention which specific report you tried, so not much more that I can say here.

      • It seems that if the updating is not working for some, the best course of action at this time is to simply export to an old Excel workbook that has never been exported to by QB 2012, “save as” and continue to export to this “untainted” workbook going forward. My client can not “update” a workbook we have been using for a long, long time. We export 5 reports weekly and generally write over the previous week’s reports.
        I am wondering if there is a way to not have the “original” report exported to Excel in addition to the standard report? We now get ten tabs rather than 5 every time she exports, we get the report and the “original”.

  6. Thanks so much for this great information. I’m sure my clients will enjoy this new feature.

  7. John says:

    My question with this integration is if I download the balance sheet in Excel, format it the way I want, will it update automatically by changing the dates in excel? For example, if it was set up as October and now I want to change the date for November, will it automatically update with November figures?

    • John, as the article shows, you can either select the report (with the dates you want) in QB and “update” the Excel file, or you can use the “update report” icon that shows in the QuickBooks tab in Excel and select the date. Either way should work.

      Note that this is in the US version of QuickBooks 2012. Your website indicates that you are in Canada, and I don’t believe that the Canadian version has these features (at least not at this time).

  8. Tara says:

    Since we have updated to Quickbooks 2012, we are no longer able to export to Excel properly. It works the first time. The second time it says, “Excel Update Failed”. Quickbooks has said it is an excel problem, but it is happening from QB’s side and excel hasn’t changed. We are using Excel 2007.

    The first time we export, it copies over the worksheet in Excel from the previous week, as it’s supposed to. The second time we get the error and it won’t work. Quickbooks support does not seem to have the level of expertise required to troubleshoot this. I have noticed a few other users have written of this issue in the past week, but no one seems to know what to do.
    Can anyone here help?

    Thanks so much for your time!!

    • Tara, based on your email address, I’m assuming that you are usingthe Canadian version? That differs from the US version, and I’m not sure that you have the same new features that are listed in this article (which relates to the US version). I’ve not had the problem you specify, so I can’t say much myself (but I’m using Office 2010)

      • Tara says:

        Thanks for your quick respones!

        You are correct, I’m based in Canada. Plus, we are using Office 2007. We will upgrade and see if that makes the difference.

        • Before you upgrade to Office 2010 please check with the Intuit Canada folks on that. I know that there have been some issues with Office 2010 and QuickBooks in the US, particularly with the 2011 releases of QB. And, typically, the Canadian versions tend to be a year behind the US versions. I don’t have the current Canadian release so I can’t say if Office 2010 works well with that or not. Please do some checking before you jump in on that…

  9. Suzanne says:

    Hello,

    We are having the same problem that Tara above is describing. Has anyone found a solution to the problem or found a way around it?

    We can no longer update our excel sheets every since upgrading to Quickbooks 2012! We would downgrade back to 2011, but since the data has been converted to the 2012 format already we can’t.

    • amitech says:

      Same here! Very frustrating! We have 3 users and one machine is windows XP with office 2007 there are two others running office 2010 without issues.

  10. Jill says:

    I’m so frustrated! I was so excited to have this new feature with Excel. But as stated by others, it worked the first time and now it no longer works. I’ve tried updating an existing sheet, updating to a new sheet and I get the same error. I’ve also tried updating from the Excel sheet by using the QB tab, but that didn’t work either.

    Is there any help on this????

  11. amitech says:

    FIXED: Excel printing and export issue!!

    After a week of frustration there was a net framework update that had failed and after fixing THAT it is now working.
    We also uninstalled a plugin for office online.

    • Doug Sleeter says:

      Hi Amitech,

      Is your environment still working? You said you had some with Excel 2007 and some with 2010. Which are working/broken?

      Also, which QB 2012 flavor/release are you using?

      Also, which QB reports are you using? Are they standard (built-in) reports, or customized reports that you memorized?

  12. Torri says:

    We need to Import Bill Payments and other reports from Excel into QB 2012 and need to know if your program will do this Easily. Or if you have other suggestions.

  13. Steven Starace says:

    I’ve been using Quickbooks Enterprise since version 2.0. When they brought out 12.0, they destroyed some of the functionality of the Export to Excel routine. I need to have financial statements showing both budget and actual numbers current month and year to date. The only way to do that is to bring out my reports to an Excel Template that has 10 sheets which reference and link to give me the reports I need every month. In 12.0, you cannot replace a sheet in an Excel.xlsx file any longer, you receive an error message “The specified File could not be found or is not a valid Excel file. Correct the pathname to the file or select a different file.” Save the file in older Excel.xls format and it now works. However, for some insane reason, they decided to instead of replacing the sheet like the function says, they rename the old sheet as ‘XXXX Original” and keep it in the file. No idea why, we would choose create a new sheet if we wanted that. Anyway, you have to delete the Original sheet, an extra step. I would love to find a program that will allow me to create a report with both budget and actual numbers, current month and year to date, this year and last and just refresh the report with any changes instead of having to re-export the individual reports to a template.

  14. Brendan says:

    Thank you for this great article. It resolved something that had been very frustrating for me.

  15. Armando says:

    Where can I find the Intuit Excel Addin? I do not see it under my company integrated applications. Thanks.

    • Armando, it should be placed there the first time that you use a supported version of Excel from a supported version of QuickBooks. What version (year, nationality) of QuickBooks are you using? Older versions, and some non-US versions, don’t have this feature. What version of Excel are you using? The student versions, for instance won’t work with QuickBooks.

  16. joe says:

    For some reason the add-in isn’t listed in the list of integrated applications. Does anyone know why? I am using Windows 7, Excel 2010, QB Premier Contractor Edition 2012.

  17. Don Loughman says:

    Exported QB reports with columns displayed by class are missing data!

    I am using Windows 7 Professional, Quickbooks Premier 2012 and Office Home & Business 2010.

    In any QB report that I attempt to export to Excel that has columns displayed by any of the drop down options: day, week, month, quarter, year or class, the last column of data (that should be the totals like the original QB report)is actually a repeat of the second column of data! The row that displays the column headings (day, month, quarter, class etc.)is messed up to where the headings continue off the page where there is no data. Totaly useless report. I ccan say that exporting to csv file works. Is there a compatibility issue or is using the Display Columns By… just asking too much?

    • Robin says:

      Don, did you ever get your QB report to Excel issue resolved? I just installed QB Premier 2012 and Office Home & Business 2010 on a new computer with Windows 7 and I can’t get QB to export a report to Excel. QB will export a letter document to Word but won’t export a report to Excel. Any help would be greatly appreciated.

      • Robin, which did you install first?

        Can you do a “repair” of your Office installation, through the Windows Control Panel?

        • Robin says:

          I uninstalled and reinstalled both Office and Quickbooks in the order that was recommended by Quickbooks. I have also run repairs on both Office and Quickbooks since reinstalling both programs since the reinstalling didn’t work. Thank you so much for responding. I have chatted with Microsoft IT and they blame it on Quickbooks. I have talked to QB IT and they want to charge me to fix it.

        • Robin says:

          Do you have any other suggestions? Thank you

          • At this point, without hands on the computer, there isn’t a lot that I can offer. These kinds of things can be hard to work out, there are so many variables.

  18. Jim Walsh says:

    After downloading my Quickbook invoice list into Excel, I turned it into an Excel table. Do you know if I can use this feature to update the table from Quickbooks? Not having much luck with this.

    • Jim Walsh says:

      When I try to update my Excel table, Excel closes and does not open up again. It would normally open back up with the updated information.

    • Jim, this particular feature is a read-only “reporting” kind of tool. If you want to update info in QuickBooks you have to use some other approach. The Transaction Pro Importer from Baystate Consulting is a good option…

  19. Katherine says:

    When using excel to export reports from quick books i used to have the command update from quickbooks in excel. I ran the updates needed for quickbooks yesterday, and now this morning i no longer have that command, even if i export the report to a new excel worksheet? Can I add this button to the command ribbion?

  20. James Rensen says:

    I tried automating this feature to update several different tabs in a workbook with a macro. The add-in, when invoked from the ribbon causes Excel to crash. I am looking for a direct programmatic / API-based invocation for the add-in to prevent crashing.

    • James, I’m not surprised that you can’t use macros to automate this feature (although I haven’t tried).

      Rather than deal with that, you might look at QQube (www.clearify.com), which I’ve reviewed in this blog some time ago. It is the absolutely best way to extract data from QuickBooks into Excel

  21. Geri says:

    Do you work with Mac users?
    I want to put QB Mac 2012 on my macbook pro (the only version that will fit with this older laptop) as I just upgraded to Snow Leopard OSX 10.6.3, and it really messed up my QB files!
    Thank you,
    Geri

  22. Keenan R says:

    I know this is an old post but, which exact Intuit Excel add-in are you using, can you provide the most recent source for this add-in? As well we are able to export and import to excel, just the update button in excel says it does not have permission to access the company data file. Do we maybe have the add-in already it is just not enabled? I do not see it in the list of integrated applications in QuickBooks. We are using Quickbooks enterprise solutions: contractors edition.

    Thanks in advance,
    Keenan

  23. Liz says:

    I have a problem once I’ve exported the report to excel, all totals columns reflect 0.00. I am not willing to change all the formulas by hand. What can the problem be? I’m using Office 2010. Management needs to see the formulas so exporting to .csv is not an option.
    Thank you
    Liz

  24. Erik Levy says:

    Hey Charlie,
    I got here through a Google search when Intuit’s site sent me in circles, as usual. I spent more time looking through your hiking site than stumbling through my Quickbooks issues. Fwiw, it helped quell the grumpy middle-aged man in me. Anyway, I have been a Quickbooks user since, well, QB 1.0 c1990, which I still have on floppy disks. I used many Windows versions over the years – actually decades. I have since graduated to QB for Mac 2011. I am running Snow Leopard 10.6.8. For some unknown reason, I cannot export to Excel or Numbers. I click on the export button, and nothing happens. I checked the download folder to see if QB just flung there, but found nothing. Any ideas would be appreciated. Btw, my site is up, but broken. I have well over 100 hundred hikes that I have to get re-posted.

    Regards,

    Erik

Leave a Reply