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.

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.

Add comment
Log in or register to post comments
This blog

Popular posts from Glen Feechan's Not Just Numbers blog - The blog for those who know it's not just about the numbers. Typical content included is primarily Excel tips and other comment relevant to those responsible for finance in their business. Glen develops spreadsheets for clients all over the world via needaspreadsheet.com and helps accountancy practices to make better use of Excel through his Excellent Accountancy business.

Get The 5 Excel features that you need to know free, by subscribing to Not Just Numbers here.