Posts Tagged ‘Pivot Table’

How to track your business in excel part 4

November 18, 2009 Leave a comment

The template for this web cast is available in our Learning Center for $11

This is the final chapter in the 4 part saga (with one bonus in part 2).

Here is the link to watch this web cast (Video):

In part 3 we were not able to get into pivot tables too deeply so I wanted to devote another web cast to that part.

We show you how to use the transaction data to run pivot tables so that you can quickly and concisely report on the data to give you really meaningful information.

If you’ve missed the first 3 parts, be sure and get caught up here:

How to track your business in excel – part 1 –

How to track your business in excel – part 2 –

How to track your business in excel – part 2 – Bonus –

How to track your business in excel – part 3 –

And this one again

How to track your business in excel – part 4 –

The pivot tables are the key to this when it is all said and done because this is how you accomplish what any accounting system should be designed to do – take what you’ve put into it and give you something valuable that you can take out of it. This is what any system’s reporting feature is all about. You won’t be able to do a well formatted P&L and Balance sheet with this, but you can get close by running the pivot table on the transactions and then adding the destination account to the filter, then filter for all Balance sheet accounts. Keep in mind it won’t be perfect. If, for example you have payments to “Accounts Payable” then this will show up on your report, but does not represent what is “Due” in accounts payable. Instead it represents what has been “Paid” on accounts payable. So you may want to exclude that one. You can enter bills in your transaction register and then you would have an accurate payable, because the bills would go in as a negative (credit) to Accounts Payable and the payments on accounts payable would be entered as a positive number. Then you could pivot on accounts payable to get an accurate balance.

Next week we’re back on to QuickBooks so visit us there at


Introduction to Pivot Tables

September 16, 2009 Leave a comment

Pivot tables in excel are not really all that different in concept from when a basketball player pivots on one foot after he (OR SHE) has stopped dribbling (not drooling). The idea is that you stop on one piece of data and then focus on the surroundings. Then you can describe your surroundings with other pieces of data in terms of your columns and rows. In the web cast here we show you how to start using pivot tables using sample data (of course J) based on an export from a QuickBooks file. We show you how you can begin to describe sales data in powerful and meaningful ways using pivot tables.

We begin with a regular table filled with Data:

Then go to your Insert tab and choose Pivot Table:

Next you will select your range (Excel will recognize your table as the range) and you will be presented with some options as well as the opportunity to re-define your range in case Excel was wrong L.

Once you click ‘ok’ excel will insert a new sheet with the layout for the pivot table. Now you’re ready to get started building the pivot table based on your data.

The Video Web Cast will give you a clear introduction on this.

You can watch it here (from the Youtube insert) or for a better quality version, watch the original unfiltered web cast as seen directly from our home website here: Microsoft Excel –Introduction to Pivot tables