UPDATED MARCH 2015
This article is a combination review of a 3rd party utility called “QB2Excel” by InformationActive and a step by step instruction on how to quickly you can produce a seemingly complex pivot table in excel using QuickBooks data in a matter of minutes! AT THE BOTTOM OF THIS ARTICLE IS THE LINK TO A YOUTUBE VIDEO WITH THE ENTIRE PROCESS IN LESS THAN 5 MINUTES
Step 1, Download and Install QB2Excel.
Step 2, Open the desired QuickBooks file and Run QB2Excel, the following screens will open in QuickBooks and you need allow access.
Step 3, Choose the tables you would like to import. In this example, I picked the InvoiceLineItems which allows me to work with the line items within invoices for the Pivot Table that I will create
Once you click on “Convert” you will see a new spreadsheet open in Excel and will contain all the sales data from invoice line by line:
Next step, inside Excel you must go to the Insert Tab, Pivot Table:
And Click OK in the Create Pivot Table window:
At this point, if you are familiar with PivotTables, you may not need to go any further… But, I will still show you the next steps.
In the PivotTable Field List, do the following 4 steps:
- Drag CustomerName field to the Report Filter box
- Drag Date field to the Column Labels box
- Drag ItemName field to the Row Labels box
- Drag ItemQuantity field to the Values box
Change the “Count” Calculation from the Value Field Settings… menu
to “Sum” and then click on OK… which is going to add up all the quantities from all invoices into the report:
Then you can try applying a Customer Filter:
And then, sit there an enjoy the report!
If this does not excite you… you have no soul.
But, seriously this will take less than 15 minutes to learn how to do, and one you master pivot table you will be producing some amazing reports that you just cannot produce in QuickBooks.
About QB2Excel from InformationActive. it has the 3 F’s is fast, functional, and free. I think InformationActive developed this tool as a “gateway drug” into their more sophisticated paid programs that can also help with analysis and other reporting tools. I personally have not use them and when I do, I’ll sure to post a review of it as usual.
The only major limitation this tool has, is that it only work off tables that are already in QuickBooks, so pulling relational data; such as Sales Detail Data combined with Customer Address info from Main Customer Table, is not possible. There is a paid (but worth every penny) tool called QQUBE, that can work in this dimensions if you need more robust reporting requirements, plus for larger datasets is much faster. I wrote an article on: Advanced Excel Reporting using QQUBE, check it out.
Hector Garcia, CPA
Advanced Certified QuickBooks ProAdvisor
Microsoft Excel 2010 & 2013 Certified
hector@garciacpa.com
