Excel Tip: Pivot Tables

David Carter's series of PivotTable tutorials on our AccountingWEB-UK site have become an Internet legend. The first tutorial, Budgeting with PivotTables, has been accessed more than 9,600 times and was highlighted as a particularly valuable resource by the Internet Scout Report on business and finance.

This page gives you an overview of what is possible with this relatively unsung feature within Microsoft's Excel spreadsheet program. Each tutorial (listed below) comprises an Excel file containing the source data, together with a Word file giving step by step instructions on how to create the PivotTable. If you are new to PivotTables, David suggests starting with either the Modelling or the first Budgeting tutorial.

Budgeting with PivotTables
Shows how to set up monthly budgets for several departments within a company, then use a PivotTable to calculate the overall company total budget. "Drill down" on the monthly figures to check doubtful balances. Learn how to revise individual departmental budgets, then use the Refresh Data command to recalculate the overall company totals.

Budgeting with PivotTables - Part Two
Follows on from the first Budgeting tutorial. Shows how to add the actuals to the budgets each month as they occur, then use PivotTables to produce monthly profitability reports comparing Actual versus Budget, and Variance.

Modelling and "What If?" Analysis with PivotTables
In this exercise you are a magazine publisher who is forecasting future income streams dependent upon variables such as the number of new subscribers anticipated per month, number of pages per issue, estimated advertising rate per page, etc etc. It shows how, by using a PivotTable for the underlying structure, you can build a model that is reliable, that is properly documented and whose structure can easily be changed at a later date.

Analyze Sales Data with PivotTables
In this exercise you import into Excel five thousand sales invoice item records taken from an accounting/ERP package. Then use PivotTables to analyse sales and gross profit any way you want - sales by customer by month, sales by product, sales by sales executive, gross profit by sales exec, rank your top selling products by value, etc etc.

Audit and Financial Reporting with PivotTables
Takes two thousand transactions from an accounting package and shows how to use PivotTables to check for any errors - eg transactions analysed to the wrong nominal account, why Debtors Control doesn't agree with the sum of customer balances, etc. After proving that the data has been correctly analysed, use a PivotTable to generate the Trial Balance, Profit and Loss report and Balance Sheet.

Sage Line 50 and PivotTables
Many users will wish to use PivotTables to analyze data in their own accounts or ERP package. But the data files in such packages are designed for transaction processing rather than analysis. Based around the popular Sage Line 50 accounts package, this tutorial shows you what's involved in taking raw data from an accounts package, and modifying it preparatory to analysis in a PivotTable.

SagePivot - Extract "Pivot-ready" Data from your Accounts Package
For Sage Line 50 users ONLY. Shows how a specialist data extraction program can assemble the data from multiple data files into the one large "flat" file that is necessary for data analysis via PivotTables.

IT consultant David Carter has been installing accounting and stock control systems for 10 years. He discovered PivotTables in Excel 95 and has helped a range of clients exploit them for a range of applications over the past five years. An expert on mid-range enterprise resource planning systems, he trained with IBM and has written on business systems for Financial Director magazine and the Financial Times. He can be reached by email at david.carter@moose.co.uk.

View more helpful tips!

You may like these other stories...

Earlier this week I presented the Chart Edition of AccountingWEB’s High Impact Excel webinar series. One of the many topics I covered was the Sparklines feature, which was first introduced in Excel 2010. Several...
On January 30, I led a free, one-hour webinar, High Impact Excel: Pivot Table Edition. If you missed the presentation, it’s too late to get CPE credit, but you can watch an on-demand recording. After the webinar, I...
By David Ringstrom, CPA In Part 1 of this series I showed how to use a custom number format to conditionally display decimal places. Although the technique is simple, the downside is it may not work in every situation....

Already a member? log in here.

Upcoming CPE Webinars

Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
Sep 10
Transfer your knowledge and experience to prepare your team for the challenges and opportunities of an accounting career.
Sep 11
This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.
Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.