QuickBooks 2012 Excel Integration Improvements
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
Export the report to a new worksheet.
If you click the Advanced button you have a number of options that you can select.
Here is what that report looks like 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.
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.
Locate the existing “template” worksheet and click Export. You will be asked if you want to update that worksheet with new data.
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.
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.
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.
The Excel report will be updated with the most current data.
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
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.
- 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.
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.
Category: QuickBooks Tips/Tricks, Software Updates
About the Author (Author Profile)
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 and a regular blog contributor to the Intuit Inner Circle. Connect with Charlie at GoogleSubscribe
If you enjoyed this article, subscribe to receive more just like it.
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
Geoff, what operating system and what version of Office?
We are having the same issue Windows XP running Office 2007 and quickbooks 2012
Solid article. I’ve used xBooks by Synergration on occasion. Handled a variety of my Excel -> QuickBooks reporting needs rather well. I plan on checking out QQube though.
http://www.synergration.com/xbooks
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
You will need many columns if you have many jobs and produce the PL by Job Report.
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.
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.
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.
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”.
Same here
Thanks so much for this great information. I’m sure my clients will enjoy this new feature.
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).
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)
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…
Ok, will do.
thanks
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.
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.
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????
It still works for me, so there isn’t much that I can say without having hands on a system that doesn’t work…
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.
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?
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.
Not sure what you are referring to as “your program”…
To import transactions from Excel I recommend that you look at the excellent import tool from Baystate Consulting – http://www.baystateconsulting.com
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.
Steven, have you looked at QQube? See our review at http://www.sleeter.com/blog/2011/03/quickbooks-reporting-is-simple-with-qqube/
An absolutely wonderful tool for anyone using Excel and QuickBooks data.
Thank you for this great article. It resolved something that had been very frustrating for me.
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.
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.
Joe, I don’t have a quick answer. I’ve not run into that myself, although I’ve seen several people comment on the issue.
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?
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.
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…
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?
Katherine, what QB product do you have (version, year, revision)? Are you talking about the “ribbon” in Excel itself?