Search Not Just Numbers

Monday, 16 November 2009

Excel Pivot Table Uses: Cash Book Analysis

Regular readers will know that I am a huge fan of Pivot Tables in Excel and I thought it might be useful to start an occasional series highlighting some of their uses. I thought I would start with an accountant's staple - Cash Book Analysis.

If you are not yet familiar with Pivot Tables, it will almost certainly help to view our free video on the subject before you read the rest of this article. Alternatively, if you have already seen this and want to learn pivot tables in greater depth, have a look at our Introduction to Pivot Tables video training course.

Typically, accountants use Excel to automate a traditional cash book by mimicking its manual equivalent with the added benefit of the totals being automatically calculated. A slightly different approach, using the power of Pivot Tables, reduces the data input and significantly increases the reporting flexibility.

Instead of replicating the net balance of each cash transaction in the column associated with its category (as in a manual cash book), simply have a column that contains the name of that category (this can be a drop-down list using data validation) and include the net value once.

By dragging this field into the column area of the pivot table, with the net amount as data, you replicate the traditional approach - but can choose to just show totals rather than the detailed transactions (if you wish). Using the "=month()" formula, you can have a column alongside the data that strips the month number from the date. If you use this as a the Page field in the Pivot Table you can provide monthly summaries.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.


  1. Pivot table is a wonderful tool. Appreciate the efforts in proliferating the knowledge in the area.

  2. Many, many years in the past, My partner and i stayed at as of this motel inside Seattle. My partner and i wandered directly into my own area a single night time, and also there was clearly any louis vuitton outlet take note around the your bed having said that, "Look within the your bed. inches This is uncommon; My partner and i has been inquisitive. Thus, I obtained on to the floor and also seemed beneath the your bed. There was clearly one more remember that examine: "Yes, My partner and i clear beneath the following, also. Nice Desires, Kathy. "I’ve stayed at with dozens and dozens regarding accommodations, yet any particular one My partner and i omega replica. From the the space, and also From the Kathy inside housekeeping services. No-one shared with her to accomplish this; the lady got that after their self to produce an effect. The lady serviced in which area want it has been her own. The lady didn’t very own the particular motel, yet the lady held the girl efficiency. The lady held the girl frame of mind concerning the girl perform. The lady has been pushed to produce perfect effect – to be able to "leave the girl mark". Quite a few folks question by themselves, "Why can i move the particular louis vuitton handbags sale distance and discover what’s achievable? inches You ought to move the excess distance as the disappointment to take action will be surrendering to be able to mediocrity. Declining to be able to contact other folks, failing to be able to maximize the particular skill you might have, and also rolex replica sale inside the mild regarding opportunity—this will be almost all dying simply by mediocrity. A great U . s . poet input it in this way, "Every person drops dead – Don't assume all person actually lifestyles. inches Don’t reconcile at under the full living simply by rolex replica sale establishing the particular pub lower and also offering the very least energy.