Sales/Tax by shipping state in Excel – The power of pivot tables, Part 1
by AccountingWEB on
By Chuck Vigeant
This is the first of two articles where I am going to show you several ways of approaching Sales and Tax by Shipping State within Excel – one without a pivot table, and one with a pivot table. Yes, we will use the QODBC driver to import the data, but I will lead you by the hand on that portion.
The main purpose of this exercise is threefold:
- Create a simple sales tax by shipping state report with sales items and tax in separate columns
- Demonstrate several ways of using Excel for this purpose
- Use a pivot table, and demonstrate its power, and ease of creation
Creating a sales/tax by shipping city report in Excel
I would surmise that many of us who have used QuickBooks for years have grown up using the export-to-Excel feature. For reasons of comfort, we probably continue to do so – even though using the QODBC driver might save us time in the process – especially if it is something we repeat every month.
For those who are frightened or overwhelmed by the prospect of learning something technical, I understand why you might shy away from tables, relationships, data, etc. The learning curve can be steep and can take time away from billable hours; believe me I get it.
The Excel Pivot Table feature also takes time to learn and, yes, it can be tricky. But I am sure that more of you have tried to play with the pivot table feature than QODBC. Anybody want to bet? Using a pivot table is much easier than you might think!
When should I use pivot tables?
Many of us confuse pivot tables with cross-tabs or multi-dimensional analysis, but there are two words that better describe pivot tables:
- Summary, in that you can easily show summary data for each group in a list
- Dynamic, in that you can easily change how you want things grouped, e.g. drag and drop
So if you wanted to subtotal a list of all the items in an invoice before taxes, it would very easy to do using a pivot table. But first, let's set the table by showing you how to do this the hard way, while introducing you to some of the available – but not oft used – features in QODBC and Excel 2007.
Using Excel without the pivot table, step by step
From within Excel, invoke the Microsoft Query Wizard:
Choose the QuickBooks Data Source:
You will see a list of QuickBooks data tables:
Scroll down to the Invoice Line table (click the "+" sign to turn it into a "minus"):
Select the following fields then click the Next button:
We are going to filter the dates:
Then filter by Shipping Address State = "CA" and click the Next button:
Skip the next screen, click the Next button:
Return the Data to Excel:
Lastly, decide where to place the data in your worksheet:
The data returns in Excel:
However, if you notice, we have a problem: The Subtotal mechanism is grayed out, and is one major reason why we would use pivot tables for this example. The Subtotal option is not available for a dynamic range, such as created by our QODBC query.
We will however complete the exercise so we can demonstrate the use of the subtotal tool. If you haven't used this before, you will find it pretty handy for a variety of challenges:
First, let's remove the connection to the live data source so we can make use of the subtotal tool (Right click anywhere within the range.)
You will receive the following message; click OK:
You will now see that the Subtotal mechanism is now available on the Data tab. Let's use it.
(You may receive the following message if you did not include the row headers in your range definition. Click Yes to continue.)
Ensure that you engage the following options and click OK:
You will get something like the following (the yellow boxes representing each invoice total):
Now let's click on the Subtotal button again: Notice that we are adding another subtotal (Replace current subtotals is unchecked).
Notice now that we have one column that gives us the sales total (yellow), and one that gives us the tax total (purple):
We also get the grand totals automatically calculated:
This is great, but there are some caveats
We created an example that is realistically missing a few elements:
- We only used invoices (not sales receipts or credit memos)
- It only represents items and customers which were tagged as taxable
- It is accrual based
- It doesn't show the tax rate charged, the sales tax item used, or the customer name
- We don't have a MONTH formula so we can break down the amounts by month
- We have not accounted for the fact that some transactions don't have a shipping address – only a billing address.
We can include the following items in our query to address some of the concerns in (A) thru (E) above. Just add some other items from the InvoiceLine table like this:
To get the MONTH calculation and the Shipping OR Billing Address, we will have to do a little magic.
We will do that in the next article's example when we create the pivot table – and get everything you need to have an accurate and complete Sales Tax by Shipping State report – with the ability to quickly analyze by using drag-and-drop methods.
About the author:
Chuck Vigeant M.Ed., founder of ACCOUNTiGRATE, LP, is considered by many to be the foremost authority on QuickBooks integration, reporting, and Business Intelligence, and is the father of custom QuickBooks reporting using Crystal Reports and the QODBC driver now included in QuickBooks Enterprise Solutions. Vigeant also is a consultant for The Sleeter Group.