Use an Excel PivotTable to get paid on time

PivotTables aren't just for show. Simon Hurst shows how some subtle enhancements in Excel 2007 can help you managed aged debtors better.

I’ve been a fan of PivotTables since I first saw them demonstrated – I think it was at some Microsoft Office release extravaganza at Alexandra Palace. Those with long memories might even recall the Lotus 1-2-3 precursor of Excel’s Pivot Tables, Lotus Improv.

Anyway, when I share my uncontrollable enthusiasm for the wonder of the PivotTable, the reaction is not always 100% positive. Occasionally someone will come up with the very sensible objection that they are obviously very clever, but where’s the practical use? The comprehensive and detailed response is probably to refer them to David Carter’s renowned series of PivotTable tutorials, but here's a short example that also uses a subtle enhancement in Excel 2007.

We're going to be using a PivotTable to compile a list of aged customer balances. We'll start with some dummy data cobbled together from the Microsoft Access database Northwind. We've adapted it to show the age of each unpaid invoice in days:

 
We've then clicked in the table of data and chosen to Insert a PivotTable. (Excel 2003: Data menu, PivotTable and PivotChart report). The customer CompanyName is set as the row label, the Age as the column label and the ExtendedPrice as the value/data:

 
We want to change this presentation to show us the fewest number of customer balances that would bring in £1.5m, sorted by size of outstanding balance.

Further reading
ExcelZone PivotTable tips and queries
Want to learn about Excel pivot tables? Start here
Improve your reporting skills with self-teach tutorials

About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping accountants make effective use of technology. He is a regular contributor to AccountingWEB's ExcelZone and the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website.

Continued...