Quick Pivot Tables in Excel with QuickBooks Data – Export Excel to QuickBooks

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.

qb2xl-qbpermission

qb2xl-qbpermission-1

 

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

Screen Shot 2014-04-01 at 10.36.20 PM

 

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:

Screen Shot 2014-04-01 at 10.37.16 PM

 

Next step, inside Excel you must go to the Insert Tab, Pivot Table:

Screen Shot 2014-04-01 at 10.44.13 PM

 

And Click OK in the Create Pivot Table window:

Screen Shot 2014-04-01 at 10.45.33 PM

 

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:

  1. Drag CustomerName field to the Report Filter box
  2. Drag Date field to the Column Labels box
  3. Drag ItemName field to the Row Labels box
  4. Drag ItemQuantity field to the Values box

Screen Shot 2014-04-01 at 10.49.17 PM

 

Change the “Count” Calculation from the Value Field Settings… menu

Untitled 2

to “Sum” and then click on OK… which is going to add up all the quantities from all invoices into the report:

Screen Shot 2014-04-01 at 10.56.20 PM

 

Then you can try applying a Customer Filter:

Screen Shot 2014-04-01 at 10.58.45 PM

 

And then, sit there an enjoy the report!

Screen Shot 2014-04-01 at 11.00.11 PM

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

 

 

 

Hector Garcia

Hector Garcia

Use our link to get 30% off for a year, valid through 07/31/2022

Leave a Reply

Your email address will not be published. Required fields are marked *



Popular Courses

Private QuickBooks Training onsite

Customized Training

Accounting & Bookkeeping Services

Video-Based Virtual QuickBooks Course

Share

30% Off QuickBooks

Use our link to get 30% off for a year, valid through 07/31/2022