Want to learn about Excel pivot tables? Start here. By David Carter | AccountingWEB

Want to learn about Excel pivot tables? Start here. By David Carter

Pivot tables come free with Microsoft Excel and are a must for anyone who wants to analyse their data. AccountingWEB has been spreading the word for a long time now, and we published our first self-teach tutorial on pivot tables as far back as 1999. There's now a full suite of these tutorials and they have been accessed over 100,000 times.

Why tutorials?
There are dozens of books out there which will explain to you how to create a pivot table. But my own experience is that no matter how many times someone explains something, you will never really understand a piece of software until you've sat down at the keyboard and gone through a worked example yourself.

This is what the tutorials are for. Each comes with sample data and step by step instructions telling you which buttons to press. And each - whether it's budgeting, sales analysis, costing, or whatever - is a real application developed by a user and myself when we were installing an accounts/ERP package.

Working your way through a real case such as the sales analysis tutorial will help you visualise how you might analyse sales in your own company, and how to calculate key numbers such as margin and margin percentage.

Where to start?
How much time do you have? Each tutorial illustrates a dozen or more pivot table features in one continuous session. Realistically, it will take you an hour or 90 minutes. You will need to concentrate, so close the curtains and take the phone off the hook.

If this is too much to digest at one go, or you simply don't have the time to spare, we also have the Five-minute tips series. This uses worked examples, but for just one feature at a time.

Click below to choose the learning schedule you want:

  • 1. Getting started - If you are not an accountant
  • 2. Getting started - If you are an accountant

    1. GETTING STARTED - IF YOU ARE NOT AN ACCOUNTANT

    1. If you want just to get a flavour of what pivot tables are about, start with Five minute pivot table tips and try the first tip.

    2. If you seriously want to learn but don't have an hour or more to spare, work your way through the complete series of Five minute pivot table tips whenever you have a moment. They cover most areas of the Sales Analysis tutorial, plus several others such as charting and the all-important vlookup function.

    The Five minute tips series takes a very simple database of just 27 sales invoice records and shows how to analyse them to show sales by product and by customer, margins, margin percentage, etc. You can work through them in any order, although you should do tips 1 to 3 first to get yourself started.

    3. If you have an hour or 90 minutes to spare for a complete tutorial, spend it working your way through the Sales Analysis with Pivot Tables tutorial. This is similar to the 5-minute tips but uses a much larger (and therefore more realistic) data set of 4200 records.

    In this exercise you import into Excel several thousand sales invoice item records taken from an accounting/ERP package. Then use pivot tables 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.

    2. GETTING STARTED - IF YOU ARE AN ACCOUNTANT

    1. If you want just to get a flavour of what pivot tables are about, start with Five minute pivot table tips and try the first tip.

    2. If you seriously want to learn but don't have an hour or more to spare, work your way through the complete series of Five minute pivot table tips whenever you have a moment. They cover most areas of the Sales Analysis tutorial, plus several others such as charting and the all-important vlookup function.

    The five-minute tips series takes a very simple database of just 27 sales invoice records and shows how to analyse them to show sales by product and by customer, margins, margin percentage, etc. You can work through them in any order, although you should do tips 1 to 3 first to get yourself started.

    3. If you have an hour to spare to work through a complete tutorial the simplest one to start with is Budgeting with Pivot Tables

    Set up monthly budgets for several departments within a company, then use a pivot table 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.

    4.(a) After this you can choose. Either you can complete the budgeting exercise by going to Budgeting with Pivot Tables, part 2.

    This follows on from the first Budgeting tutorial. Add actuals to the budgets each month as they occur, then use pivot tables to produce monthly profitability reports comparing Actual versus Budget, and Variance.

    4.(b) Or, if you want to export balances from an accounts package and create financial reports, you can skip Budgeting Part 2 and go to Turn a Trial Balance into a P&L with a pivot table

    You have exported a Trial Balance report out of your accounts package and into Excel. In this tutorial, you are going to apply pivot tables and Excel's Vlookup function to the Trial Balance data in order to create a Profit and Loss account for each department of your company.

    5. In the next tutorial you export transactions from an accounts package and create financial reports. Use this where the accounts packages doesn't hold balances. For example, every package will hold nominal account balances for the company as a whole, but very few will hold them for each department. In this case you will have to export the raw transactions and calculate the departmental balances yourself. Create financial reports from exported transactions

    Export 2,000 transactions from an accounting package and use pivot tables 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 pivot table to generate the Trial Balance, Profit and Loss report and Balance Sheet.