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